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

Hi,

On [1] I suggested an idea to make improvements to the planner around the
Equivalence Class code. Later in [2] Tom raised concerns with this adding
too many planning cycles for a perhaps not common enough situation. I
don't want to discuss that particular patch here, I want to discuss more
generally about the dilemma about adding more smarts to the planner to
allow it to generate a more optimal plan in order to save on execution time.

In the case of the Equivalence Class Filters code, I quoted an example
where pushing these filters down into the joined relation caused a
significant performance improvement to a query. Now, I understand Tom's
concerns with slowing down the planner, as in cases where the query is
short running, or the optimisations don't apply, then we could cause the
query to overall (including planning time) perform worse. Nobody wants
that, but on the other hand, if spending 5-10 extra microseconds during
planning equates to 6 hours shaved off execution time, then nobody would
think to grudge that extra 5-10 microseconds during planning.

What I'd like to discuss here is what was touched on on that other thread
on ways to get around this problem:

A number of ideas were suggested on the other thread about how we might go
about solving this problem. In [3] Simon talked about perhaps enabling
extra optimisations when the planner sees that the plan will cost more than
some given threshold. That's perhaps an option, but may not work well for
optimisations which must take place very early in planning, for example [4].
Another idea which came up was from Evgeniy [5], which was more of a
request not to do it this way, but never-the-less, the idea was basically
to add lots of GUCs to enable/disable each extra planner feature.

Another option which I've thought about previously was a planner_strength
GUC, at which various additional optimisations are enabled at various
predefined strength levels, so that databases which tend to spend a great
deal more execution time compared to planning time can turn this up a bit
to see if that helps change that ratio a bit. This idea is far from
perfect though, as who's to say that planner feature X should "kick in"
before planner feature Y? I've also often thought that it might be nice to
have it so the planner does not modify the Parse object, so that the
planner has the option to throw away what it's done so far and start
planning all over again with the "planner_strength" knob turned up to the
maximum, if the cost happened to indicate that the query was going to take
a long time to execute.

In reality we already have some planner features which are possible
candidates for non essential optimisations. For example join removals
likely don't apply in all that many cases, but when they do, this feature
is a great win. So by having some sort of ability to enable/disable planner
features we also stand to actually speed the planner up for fast simple
queries.

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.

I wrote:
> I believe that with parallel query on the horizon for 9.6 that we're now
> aiming to support bigger OLAP type database than ever before. So if we
> ignore patches like this one then it appears that we have some conflicting
> goals in the community as it seems that we're willing to add the brawn,
but
> we're not willing to add the brain. If this is the case then it's a shame,
> as I think we can have both. So I very much agree on the fact that we must
> find a way to maintain support and high performance of small OLTP
databases
> too.

So here I'd very much like to kick off discussion on an acceptable way to
solve this problem, in a realistic way which we're all happy with.

Comments are of course welcome.

[1]
http://www.postgresql.org/message-id/CAKJS1f9FK_X_5HKcPcSeimy16Owe3EmPmmGsGWLcKkj_rW9s6A@mail.gmail.com
[2] http://www.postgresql.org/message-id/30810.1449335261@sss.pgh.pa.us
[3]
http://www.postgresql.org/message-id/CANP8+jLRpRN4ynMsRkOqhYi-Dw5JrODMOt05qejhrAyrsExVmg@mail.gmail.com
[4]
http://www.postgresql.org/message-id/CAKJS1f_UZ_MXtpot6EPXsgHSujoUCrKuXYHLH06h072rDXsCzw@mail.gmail.com
[5]
http://www.postgresql.org/message-id/2F30BA8B-DAB9-4907-9E4E-102D242566E3@gmail.com

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services