Thread

Re: Discussion on missing optimizations

Adam Brusselback <adambrusselback@gmail.com> — 2017-10-13T16:56:49Z

So from following this discussion and others focused on making the
planner "smarter", there is always an argument to be had over wasting
planner cycles, and it's always a hard fought battle to get any changes made.

Now, i'm speaking without any knowledge of the Postgres internals, so
please bear with me.  It seems like the underlying "feature" which would
allow more edge case optimizations into the planner would be some
framework for knowing when to consider using those optimizations, and
when to not waste time planning and skip expensive planning optimizations.

It seems with a system like that, maybe Postgres could still maintain
(or even improve) it's hard fought quick planning time for OLTP queries,
while being able to spend more time planning for OLAP queries where
spending an extra 500ms planning may save minutes of execution time.

I know in my database, I have analytical queries which I wouldn't mind spending
multiple seconds planning if necessary to produce an optimal plan,
because execution
time dominates everything.

Now what that system looks like is something I have no real opinion
or authority on.  It seems like there are three options though, automatic,
manual, and automatic with manual override.  Two of those, manual / automatic
with manual override seem to be a little too close to query hints for them to be
considered from all previous discussions I've heard (correct me if i'm wrong).
So that leaves automatic as the only option I can see being considered viable.

Now comes the question of if it's possible to automatically classify queries in
such a way that we can cheaply know what optimizations we should attempt,
and what should not even be considered because the planning time would
dominate.


I'll leave my thoughts at that, and await comments from the smarter people
in the room who know exactly why this idea wouldn't work.  The general thought
behind it is to make it easier for more and more query optimizations
to make it into
the planner, while not hurting queries which shouldn't spend their time on it.

If there are better ways of going about it, great!

Thanks for listening to me ramble,
-Adam