Mysteriously varying index scan costs

Поиск
Список
Период
Сортировка
От Laurenz Albe
Тема Mysteriously varying index scan costs
Дата
Msg-id 0979b9133184be5bdec8a99324854bf95656942f.camel@cybertec.at
обсуждение исходный текст
Ответы Re: Mysteriously varying index scan costs  (Pavan Deolasee <pavan.deolasee@gmail.com>)
Список pgsql-general
Last week I encountered the following at a customer site on PostgreSQL 9.6,
and I cannot explain it.

The first run gave me this:

 Index Scan using device_outbound_messages_status on device_outbound_messages  (cost=0.43..20.46 rows=97 width=128)
(actualtime=34.021..35.545 rows=133 loops=1)
 
   Index Cond: ((status)::text = ANY ('{WAITING_FOR_TX,WAITING_FOR_IMMEDIATE_TX}'::text[]))
   Buffers: shared hit=74917 dirtied=707

Subsequent runs of the same query gave me:

 Index Scan using device_outbound_messages_status on device_outbound_messages  (cost=0.43..20.46 rows=97 width=128)
(actualtime=2.129..3.907 rows=133 loops=1)
 
   Index Cond: ((status)::text = ANY ('{WAITING_FOR_TX,WAITING_FOR_IMMEDIATE_TX}'::text[]))
   Buffers: shared hit=1185

There were no concurrent changes to the data (test environment).
This was part of a bigger statement.

I understand that some pages can be dirtied because table fetches that
cause hint bits to be set.

But how can it be that the first run has to touch 74917 blocks,
while whe second run only needs to touch 1185?

Thanks for any ideas!

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com



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

Предыдущее
От: Durgamahesh Manne
Дата:
Сообщение: Re: *Regarding brin_index on required column of the table
Следующее
От: Pavan Deolasee
Дата:
Сообщение: Re: Mysteriously varying index scan costs