Re: Question on Buckets and Batches in explain plan

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(dot)klaver(at)aklaver(dot)com