Question on Buckets and Batches in explain plan

On 06/14/2018 05:47 PM, Charlin Barak wrote:

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.

I think the first thing to do would be to verify the configurations in
their respective postgresql.conf files.

Other questions that come to mind:

1) Are they running on different machines?

If so:
a) What OS is involved in each case?

b) What is the hardware specifications for each machine?

2) Do they have same data, type and amount?

3) What is the actual query being run below?

4) What is the full EXPLAIN ANALYZE for each case?

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.

--
Adrian Klaver
adrian.klaver@aklaver.com