Executing with the job_id shown in the stats of the empty table below (didn’t change after bunches of executions). The job_entry table has very ephemeral data in general.tapesystem=# EXPLAIN ANALYZE EXECUTE foo('cc54ca5d-0dca-4b35-acd9-e0fe69c6b247');
QUERY PLAN
------------------------------------------------------------ ------------------------------ --------------------------
Hash Join (cost=9582.63..21191.13 rows=300019 width=77) (actual time=22.679..22.679 rows=0 loops=1)
Hash Cond: (job_entry.blob_id = blob.id)
-> Seq Scan on job_entry (cost=0.00..7483.24 rows=300019 width=16) (actual time=22.677..22.677 rows=0 loops=1)
Filter: (job_id = 'cc54ca5d-0dca-4b35-acd9-e0fe69c6b247'::uuid)
-> Hash (cost=5832.28..5832.28 rows=300028 width=77) (never executed)
-> Seq Scan on blob (cost=0.00..5832.28 rows=300028 width=77) (never executed)
Execution time: 22.723 ms[...]
job_entry | r | 300021 | 3733
job_entry_blob_id_idx | i | 300022 | 1509
job_entry_chunk_id_idx | i | 300022 | 1405
job_entry_job_id_blob_id_key| i | 300022 | 2392
job_entry_job_id_idx | i | 300022 | 1424
job_entry_order_index_chunk_id_key | i | 300022 | 1971
job_entry_pkey | i | 300022 | 1528[...]
job_id | f | 1 | cc54ca5d-0dca-4b35-acd9-e0fe69c6b247
IIUC, the system believes your job_entry table has 300k records ALL of them having the UUID value ending in "*b247" - so it is unsurprising that it chooses to sequentially scan job_entry when its given that ID to search for. And if its given a different ID is realizes it can accurately confirm the absence of the supplied value in the table by using the index.
I would suspect that vacuuming these tables would solve your problem. Whether there is an issue beyond a lack of vacuuming, or related to auto-vacuum, I am unsure. Though at this point it may take a vacuum full to recover back to a sane state. Though ANALYZE by itself should clear up the statistical discrepancy.
But, I may be missing something, my experience and skill here is somewhat limited.
David J.
