Question on Buckets and Batches in explain plan

From: Charlin Barak <charlinbarak(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org >> PG-General Mailing List" <pgsql-general(at)postgresql(dot)org>
Subject: Question on Buckets and Batches in explain plan
Date: 2018-06-15 00:47:28
Message-ID: CAPrg5cbPxSqrgAPKtSjt3J8ajJB6M1G=o297YLK1oNzUNWJcWw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2018-06-15 02:55:05 Re: Question on Buckets and Batches in explain plan
Previous Message Sam Saffron 2018-06-15 00:29:02 Faster way of estimating database size