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...
