Re: Query hitting empty tables taking 48 minutes

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: Query hitting empty tables taking 48 minutes
Дата
Msg-id CAKFQuwb_euhMMMxBzV2b6Dt2zeJ_xK3b3Vu7+HeKHxcaztLZ6Q@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Query hitting empty tables taking 48 minutes  (Robert Creager <robert@logicalchaos.org>)
Ответы Re: Query hitting empty tables taking 48 minutes  (Robert Creager <robert@logicalchaos.org>)
Re: Query hitting empty tables taking 48 minutes  (Robert Creager <robertc@spectralogic.com>)
Список pgsql-general
On Thu, Jun 7, 2018 at 3:02 PM, Robert Creager <robert@logicalchaos.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.

В списке pgsql-general по дате отправления:

Предыдущее
От: Robert Creager
Дата:
Сообщение: Re: Query hitting empty tables taking 48 minutes
Следующее
От: Robert Creager
Дата:
Сообщение: Re: Query hitting empty tables taking 48 minutes