Re: Query hitting empty tables taking 48 minutes

On Thu, Jun 7, 2018 at 3:02 PM, Robert Creager <robert(at)logicalchaos(dot)org>
wrote:

> 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.