Search Postgresql Archives

Re: Question on Buckets and Batches in explain plan

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux