Re: -HEAD planner issue wrt hash_joins on dbt3 ?

Tom Lane wrote:
> Stefan Kaltenbrunner <stefan(at)kaltenbrunner(dot)cc> 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