Stefan Kaltenbrunner <stefan@kaltenbrunner.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.
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.
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.
> 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?
regards, tom lane