From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Andres Freund <andres@anarazel.de>
Cc: pgsql-hackers@postgresql.org
Subject: Re: Macro customizable hashtable / bitmapscan & aggregation perf
Date: Tue, 11 Oct 2016 04:29:31 +0200
Message-ID: <4c78c42c-8259-4d15-1a4f-df17878e8e1b@2ndquadrant.com> (raw)
In-Reply-To: <20161011020751.o2ozug266i5vx7pt@alap3.anarazel.de>
References: <20160727004333.r3e2k2y6fvk2ntup@alap3.anarazel.de>
<20161001004454.4wkqbmu67spvc3ak@alap3.anarazel.de>
<41a178d4-a599-60e6-1657-e2489508858a@2ndquadrant.com>
<20161001195956.u657cbvya7thzkr6@alap3.anarazel.de>
<0b71d15b-89ef-bd4f-d653-0a118b550601@2ndquadrant.com>
<20161002001758.GA14611@awork2>
<ec2f8c1d-329b-e7b6-1406-f74af789c1f0@2ndquadrant.com>
<20161009233803.6zjackpbz2v2s4sw@alap3.anarazel.de>
<64bb428d-8e05-9130-5627-fac7e85a68aa@2ndquadrant.com>
<20161011004622.5kkvlwghm4yvg4id@alap3.anarazel.de>
<20161011020751.o2ozug266i5vx7pt@alap3.anarazel.de>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
On 10/11/2016 04:07 AM, Andres Freund wrote:
> On 2016-10-10 17:46:22 -0700, Andres Freund wrote:
>>> TPC-DS (tpcds.ods)
>>> ------------------
>>>
>>> In this case, I'd say the results are less convincing. There are quite a few
>>> queries that got slower by ~10%, which is well above - for example queries
>>> 22 and 67. There are of course queries that got ~10% faster, and in total
>>> the patched version executed more queries (so overall the result is slightly
>>> positive, but not significantly).
>>
>> That's interesting. I wonder whether that's plan changes just due to the
>> changing memory estimates, or what's causing that. I'll look into it.
>
> Hm. Based on an initial look those queries aren't planned with any of
> the affected codepaths. Could this primarily be a question of
> randomness? Would it perhaps make sense to run the tests in a comparable
> order? Looking at tpcds.py and the output files, it seems that the query
> order differes between the runs, that can easily explain bigger
> difference than the above. For me a scale=1 run creates a database of
> approximately 4.5GB, thus with shared_buffers=1GB execution order is
> likely to have a significant performance impact.
>
Yes, I see similar plans (no bitmap index scans or hash aggregates). But
the difference is there, even when running the query alone (so it's not
merely due to the randomized ordering).
I wonder whether this is again due to compiler moving stuff around.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: tomas.vondra@2ndquadrant.com, andres@anarazel.de
Subject: Re: Macro customizable hashtable / bitmapscan & aggregation perf
In-Reply-To: <4c78c42c-8259-4d15-1a4f-df17878e8e1b@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox