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.
