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

2017-11-03 Thread Laurenz Albe
Gunther wrote: > > Bad choices are almost always caused by bad estimates. > > Granted, there is no way that estimates can ever be perfect. > > ... > > Looking deeper, I would say that wrongly chosen nested loop joins > > often come from an underestimate that is close to zero. > > PostgreSQL already

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

2017-11-03 Thread Thomas Kellerer
Laurenz Albe schrieb am 02.11.2017 um 09:30: > Finally, even though the official line of PostgreSQL is to *not* have > query hints, and for a number of good reasons, this is far from being > an unanimous decision. The scales may tip at some point, though I > personally hope that this point is not

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

2017-11-03 Thread Adam Brusselback
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

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

2017-11-03 Thread Gunther
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

2017-11-03 Thread Gunther
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

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

2017-11-03 Thread legrand legrand
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_tuple_cost cpu_index_tuple_cost cpu_ope

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

2017-11-03 Thread Gunther
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

2017-11-03 Thread Dave Nicponski
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