From: | Andres Freund <andres(at)anarazel(dot)de> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Plan time Improvement - 64bit bitmapset |
Date: | 2009-06-03 15:48:02 |
Message-ID: | 4A269B32.5060600@anarazel.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi,
While analyzing some complex query and switching away from using the
materialized views to their underlying ones I got interested in the long
plan times (minutes and up) and did some profiling work.
The queries are high dimensional star-schema-alike queries
(unfortunately quite private (health) data and a schema I may not make
public).
Using oprofile and
"valgrind --tool=callgrind --dump-instr=yes --collect-jumps=yes
--simulate-cache=yes --simulate-hwpref=yes" I found that one of the
bitmapset functions are near the top of the profile.
When switching bitmapword and companions in bitmap.h to u64 and s64
respectively I get an improvement up to 15% in queries with 16+ joins.
The more joins the bigger the win.
In the very simple (structurally) query with 16 joins the improvement is
around 1-2%.
With the most complex query I tested (the nr. of participating relations
is hard to count because of many views) I get an improvement up to 15%.
I did not test with bigger/more complex queries because it got too slow
to get sufficiently thorough results.
When playing around with join_collapse_limit, from_collapse_limit, geqo,
geqo_threshold I found that unless the settings are set to really low
values I can find performance improvements for most combinations.
I could not find any regression in the queries we use - and I can't see
where there would be a significant overhead.
Unfortunately the more interesting trace seems to be the valgrind one -
which with these options currently only "kcachegrind" can read. I could
not get a usable text export out of the latter.
Linked are two overview pictures before (32bit.png) and after
(64bit.png) the switch to using 64bit bitmapsets from the backend
evaluating a complex query once:
http://anarazel.de/pg/32bit_bitmapsets.png
http://anarazel.de/pg/64bit_bitmapsets.png
That seems like an easy change - is there a reason not to do this if the
arch is a 64bit one?
Can anybody else with complex queries test my results? (I can provide a
patch if wanted).
Andres
PS: If kcachegrind users want to see the trace, speak up...
From | Date | Subject | |
---|---|---|---|
Next Message | Aidan Van Dyk | 2009-06-03 15:52:49 | Re: PostgreSQL Developer meeting minutes up |
Previous Message | Tom Lane | 2009-06-03 15:40:02 | Re: Question about STRICT |