Plan time Improvement - 64bit bitmapset
От | Andres Freund |
---|---|
Тема | Plan time Improvement - 64bit bitmapset |
Дата | |
Msg-id | 4A269B32.5060600@anarazel.de обсуждение исходный текст |
Ответы |
Re: Plan time Improvement - 64bit bitmapset
Re: Plan time Improvement - 64bit bitmapset |
Список | 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...
В списке pgsql-hackers по дате отправления: