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

Thank you Gunther for bringing this up. It's been bothering me quite a bit over time as well. Forgive the naive question, but does the query planner's cost estimator only track a single estimate of cost that gets accumulated and compared across plan variants? Or is it keeping a range or probabil

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

On 11/3/2017 10:55, legrand legrand wrote: To limit NL usage, wouldn't a modified set of Planner Cost Constants https://www.postgresql.org/docs/current/static/runtime-config-query.html seq_page_cost random_page_cost cpu_t

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

Just throwing out some more innovative ideas. Materialized join tables, I have read somewhere. OK, difficult to keep consistent with transactions. Forget that. But, why not collect statistics on every join that is processed, even if the query is interrupted. Then as more and more plans are ru

Re: [PERFORM] 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 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.

Re: [PERFORM] 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 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 va