Re: Macro customizable hashtable / bitmapscan & aggregation perf

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