Re: More thorough planning for OLAP queries (was: [PATCH] Equivalence Class Filters)

On 12/30/2015 08:16 AM, David Rowley wrote:

I do strongly believe that we need to come up with something to
solve this problem. I already summarised my thoughts on the other
thread.

One approach that I don't see mentioned on any of the threads is plan caching, which allows reusing the plan across many query executions, hopefully amortizing the planning costs.

I'm sure implementing such caching is non-trivial and there are cases where it may not help, but perhaps it's not entirely futile (AFAIK it's used by some databases exactly to address the higher planning costs).

I imagine a single GUC enabling or disabling this (possibly not just globally but per session, user or database).

We already have some form of plan caching, although only for prepared statements within a single session - maybe that could be a good starting point? For example what if we only enabled those "expensive" optimizations for prepared statements, which are assumed to be executed multiple times? Of course, this may not be entirely true (say, PL/pgSQL uses prepared statements all the time).

Of course, the annoying consequence of this would be that the planning may get somewhat unpredictable - the plan will depend on whether the query was planned directly or as a prepared statement, or whether plan caching is enabled. However, the same mostly applies to solutions proposed in the other threads so far.