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.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services