Question on Buckets and Batches in explain plan

[Charlin Barak <charlinbarak@xxxxxxxxx>]

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.