Re: Re: OLAP/reporting queries fall into nested loops over seq scans or other horrible planner choices

On Fri, Nov 3, 2017 at 5:28 AM, Thomas Kellerer <spam_eater(at)gmx(dot)net> wrote:
> I do like Oracle's approach with SQL profiles, where you can force the
> optimizer to try harder to find a good execution plan. I _think_ it even
> runs the statement with multiple plans and compares the expected outcome
> with the actual values. Once a better plan is found that plan can be
> attached to that query and the planner will use that plan with subsequent
> executions.

I also think that this is a really cool approach. For those specific
problem queries, pretty much tell the optimizer "do your best to make
this as efficient as possible".

To make that more useful though, you'd probably need a shared query
cache that would be persisted through restarts. I'd assume if you
have a problem query, this very heavy "planning / optimization"
operation would not be something you wanted every connection to have
to do every time they connect.

I wish I was more knowledgeable about the internals so I could more
clearly see how a system like that could come together, and what other
groundwork would be needed building up to it.