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.
