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@xxxxxxxxxxx
