Question on Buckets and Batches in explain plan

Поиск
Список
Период
Сортировка
От Charlin Barak
Тема Question on Buckets and Batches in explain plan
Дата
Msg-id CAPrg5cbPxSqrgAPKtSjt3J8ajJB6M1G=o297YLK1oNzUNWJcWw@mail.gmail.com
обсуждение исходный текст
Ответы Re: Question on Buckets and Batches in explain plan  (Adrian Klaver <adrian.klaver@aklaver.com>)
Список pgsql-general
Hi,
I ran a query on two supposedly similarly configured 9.6.8 databases but got two different timings. One ran three times faster than the other.

The explain plans on both systems look the same except for the Buckets and Batches. Can someone explain what that means and what configuration settings I should compare between the systems? The faster plan seems to be fetching data in bigger batches (2048) thus requiring fewer buckets ( 65536)?

Faster plan.
->  Hash  (cost=1805846.88..1805846.88 rows=76895088 width=49) (actual time=143919.988..143919.988 rows=83895440 loops=1)
                                       Buckets: 65536  Batches: 2048  Memory Usage: 3513kB
                                       Buffers: shared hit=2 read=1036894, temp written=652371


Slower plan
->  Hash  (cost=1805862.40..1805862.40 rows=76895440 width=49) (actual time=530978.279..530978.279 rows=83895440 loops=1)
                                        Buckets: 2097152  Batches: 64  Memory Usage: 112069kB
                                        Buffers: shared hit=1 read=1036907, temp written=643448


Thanks.

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

Предыдущее
От: Sam Saffron
Дата:
Сообщение: Faster way of estimating database size
Следующее
От: Adrian Klaver
Дата:
Сообщение: Re: Question on Buckets and Batches in explain plan