/v2/contracts/{chainId} endpoint hangs beyond certain matchId

[marcocastignoli]

A user in our public chat reported an issue where the /v2/contracts/{chainId} endpoint hangs after reaching a certain matchId.

I investigated and found that running the query below takes a long time, but if you set LIMIT 1, it returns quickly. This slowdown happens because there’s a large gap of non-137 chain contracts before matchId 4403388, causing a delay before returning results.

SELECT
      sourcify_matches.id, sourcify_matches.creation_match, sourcify_matches.runtime_match, nullif(concat('0x', encode(contract_deployments.address, 'hex')), '0x') as address, to_char(sourcify_matches.created_at, 'YYYY-MM-DD"T"HH24:MI:SS"Z"') as verified_at
    FROM public.sourcify_matches
    JOIN public.verified_contracts ON verified_contracts.id = sourcify_matches.verified_contract_id
    JOIN public.contract_deployments ON 
        contract_deployments.id = verified_contracts.deployment_id
    WHERE sourcify_matches.id < 4403388 and contract_deployments.chain_id = 137
    ORDER BY sourcify_matches.id DESC
    LIMIT 2;

The user later identified the exact gap:

seems like the issue is fixed now. 
After '1152336', it jumped to '4403507' (quite a big jump), but its working now.⁦