Tom Lane wrote: > Stefan Kaltenbrunner <[EMAIL PROTECTED]> writes: >> Tom Lane wrote: >>> I'm not sure what we could do about the concurrent-sessions issue, but >>> we could make some sort of attack on the query complexity issue by >>> pro-rating the effective_cache_size among all the tables used by a >>> query. > >> hmm not sure i understand what you mean here :-( > > Per the comment for index_pages_fetched: > > * We assume that effective_cache_size is the total number of buffer pages > * available for both table and index, and pro-rate that space between the > * table and index. (Ideally other_pages should include all the other > * tables and indexes used by the query too; but we don't have a good way > * to get that number here.) > > A first-order approximation to this would be to add up the total sizes > of all the other tables used in the query. I am thinking of leaving out > other indexes, mainly because we can't tell at this level which other > indexes are actually gonna get used. This would tend to underestimate > by leaving out indexes, but not by a lot if you assume indexes are much > smaller than their tables. It would also be an overestimate because > tables that are not indexscanned concurrently with the one under > consideration probably shouldn't be counted anyway. So one might hope > these effects would more or less cancel out. Anyway it seems to be a > better idea than what we have now.
aah - I think I understand that logic now - thanks for the reference to the source :-) > >> I will redo with lower settings - do you have any suggestions for that ? > > Try reducing effective_cache_size to maybe a fourth of what it is now. > If that helps the thing pick better plans for these multi-table queries, > then we should try changing the other_pages calculation as above. ok - the planner switches to a different plan at about 2.5GB of effective_cache_size resulting in the following plan: http://www.kaltenbrunner.cc/files/analyze_q7_1GB.txt (3 consecutive runs - starting with cold caches) with 6GB I get: http://www.kaltenbrunner.cc/files/analyze_q7_6GB.txt (single run - immediatly after the above ones) Stefan ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
