Re: -HEAD planner issue wrt hash_joins on dbt3 ?

Tom Lane wrote:
> Stefan Kaltenbrunner <stefan(at)kaltenbrunner(dot)cc> writes:
>> some additional numbers(first one is with default settings, second is
>> with enable_nestloop = 'off', third one is with enable_nestloop = 'off'
>> and enable_hashjoin='off'):
>
>> http://www.kaltenbrunner.cc/files/analyze_q7.txt
>
> I'm inclined to think you still have effective_cache_size set too high;
> or at least that the planner is being too optimistic about how much
> cache space is actually available to each indexscan.

I have long term external monitoring on that server and it indeed shows
that that there was never less then about 5.8G of buffercache used (or
more then 2.2GB used by other means). So 6G might still be a bit on the
optimistic side but it is not actually that far of from reality.
I will redo with lower settings - do you have any suggestions for that ?

>
> With the code as it currently stands, effective_cache_size has some of
> the same properties as work_mem: the planner effectively assumes that
> that much space is available to *each* indexscan, and so you'd need to
> de-rate the setting based on the complexity of queries and the number of
> concurrent sessions.

concurrency is 1 here - there is never more than a single query running
in parallel in those tests.

>
> 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 :-(

>
>
>> http://www.kaltenbrunner.cc/files/analyze_q20.txt
>> here we have a 180x(!) speedup with both disabled planner options ...
>
> There's something awfully bogus about that one --- how is it that the
> aggregate subplan, with the exact same plan and same number of
> executions in all three cases, has an actual runtime 200x more in the
> first case?

hmm - good question. I will redo those in a bit ...

Stefan