Re: [PERFORM] Hints (was Poor performance using CTE)

2012-12-03 Thread Gavin Flower
On 28/11/12 15:17, Craig Ringer wrote: On 27/11/2012 3:42 PM, Scott Marlowe wrote: Here here! PostgreSQL is well known for its extensibility and this is the perfect place for hints. I agree with the sentiment and your concerns. However, this doesn't solve the CTE problem. Some people are re

Re: [PERFORM] Hints (was Poor performance using CTE)

2012-11-27 Thread Scott Marlowe
On Tue, Nov 27, 2012 at 7:17 PM, Craig Ringer wrote: > On 27/11/2012 3:42 PM, Scott Marlowe wrote: > >> Here here! PostgreSQL is well known for its extensibility and this is >> the perfect place for hints. > > I agree with the sentiment and your concerns. However, this doesn't solve > the CTE pro

Re: [PERFORM] Hints (was Poor performance using CTE)

2012-11-27 Thread Craig Ringer
On 27/11/2012 3:42 PM, Scott Marlowe wrote: Here here! PostgreSQL is well known for its extensibility and this is the perfect place for hints. I agree with the sentiment and your concerns. However, this doesn't solve the CTE problem. Some people are relying on the planner's inability to push

Re: [PERFORM] Hints (was Poor performance using CTE)

2012-11-27 Thread Scott Marlowe
On Fri, Nov 23, 2012 at 3:05 AM, Cédric Villemain wrote: > Le mercredi 21 novembre 2012 17:34:02, Craig James a écrit : >> On Wed, Nov 21, 2012 at 5:42 AM, Kevin Grittner wrote: >> > It's a tough problem. Disguising and not documenting the available >> > optimizer hints leads to more reports on w

Re: [PERFORM] Hints (was Poor performance using CTE)

2012-11-23 Thread Gavin Flower
On 22/11/12 06:28, Craig James wrote: On Wed, Nov 21, 2012 at 9:25 AM, Joe Conway > wrote: On 11/21/2012 08:05 AM, Heikki Linnakangas wrote: > Rather than telling the planner what to do or not to do, I'd much rather > have hints that give the planner

Re: [PERFORM] Hints (was Poor performance using CTE)

2012-11-23 Thread Cédric Villemain
Le mercredi 21 novembre 2012 17:34:02, Craig James a écrit : > On Wed, Nov 21, 2012 at 5:42 AM, Kevin Grittner wrote: > > It's a tough problem. Disguising and not documenting the available > > optimizer hints leads to more reports on where the optimizer should > > be smarter, and has spurred optim

Re: [PERFORM] Hints (was Poor performance using CTE)

2012-11-22 Thread Heikki Linnakangas
On 22.11.2012 02:53, Jeff Janes wrote: That gives the planner the information it needs to choose the right plan on its own. That kind of hints would be much less implementation specific and much more likely to still be useful, or at least not outright counter-productive, in a future version with

Re: [PERFORM] Hints (was Poor performance using CTE)

2012-11-21 Thread Jeff Janes
On Wed, Nov 21, 2012 at 8:05 AM, Heikki Linnakangas wrote: > On 21.11.2012 15:42, Kevin Grittner wrote: >> >> Better, IMV, would be to identify what sorts of hints people actually >> find useful, and use that as the basis for TODO items for optimizer >> improvement as well as inventing clear ways

Re: [PERFORM] Hints (was Poor performance using CTE)

2012-11-21 Thread Andreas Kretschmer
Craig James wrote: > On 11/21/2012 08:05 AM, Heikki Linnakangas wrote: > > Rather than telling the planner what to do or not to do, I'd much rather > > have hints that give the planner more information about the tables and > > quals involved in the query. A typical source of bad p

Re: [PERFORM] Hints (was Poor performance using CTE)

2012-11-21 Thread Tom Lane
Craig James writes: > On Wed, Nov 21, 2012 at 9:25 AM, Joe Conway wrote: >> I like this idea, but also think that if we have a syntax to allow >> hints, it would be nice to have a simple way to ignore all hints (yes, I >> suppose I'm suggesting yet another GUC). That way after sprinkling your >>

Re: [PERFORM] Hints (was Poor performance using CTE)

2012-11-21 Thread Joe Conway
On 11/21/2012 09:28 AM, Craig James wrote: > > > On Wed, Nov 21, 2012 at 9:25 AM, Joe Conway > wrote: > > On 11/21/2012 08:05 AM, Heikki Linnakangas wrote: > > Rather than telling the planner what to do or not to do, I'd much > rather > > have hints th

Re: [PERFORM] Hints (was Poor performance using CTE)

2012-11-21 Thread Craig James
On Wed, Nov 21, 2012 at 9:25 AM, Joe Conway wrote: > On 11/21/2012 08:05 AM, Heikki Linnakangas wrote: > > Rather than telling the planner what to do or not to do, I'd much rather > > have hints that give the planner more information about the tables and > > quals involved in the query. A typical

Re: [PERFORM] Hints (was Poor performance using CTE)

2012-11-21 Thread Joe Conway
On 11/21/2012 08:05 AM, Heikki Linnakangas wrote: > Rather than telling the planner what to do or not to do, I'd much rather > have hints that give the planner more information about the tables and > quals involved in the query. A typical source of bad plans is when the > planner gets its cost esti

Re: [PERFORM] Hints (was Poor performance using CTE)

2012-11-21 Thread Craig James
On Wed, Nov 21, 2012 at 5:42 AM, Kevin Grittner wrote: > > It's a tough problem. Disguising and not documenting the available > optimizer hints leads to more reports on where the optimizer should > be smarter, and has spurred optimizer improvements. ... > Regarding the above-mentioned benefits we

Re: [PERFORM] Hints (was Poor performance using CTE)

2012-11-21 Thread Heikki Linnakangas
On 21.11.2012 15:42, Kevin Grittner wrote: Better, IMV, would be to identify what sorts of hints people actually find useful, and use that as the basis for TODO items for optimizer improvement as well as inventing clear ways to specify the desired coercion. I liked the suggestion that a CTE which

Re: [PERFORM] Hints (was Poor performance using CTE)

2012-11-21 Thread Kevin Grittner
Craig Ringer wrote: > On 11/21/2012 09:35 AM, Craig James wrote: >> Why not make an explicit hint syntax and document it? I've still >> don't understand why "hint" is a dirty word in Postgres. There are >> a half-dozen or so ways in common use to circumvent or correct >> sub-optimal plans. > > The

Re: [PERFORM] Hints (was Poor performance using CTE)

2012-11-21 Thread Shaun Thomas
On 11/20/2012 08:15 PM, Craig Ringer wrote: I think it's time to admit that and get the syntax in place for CTEs so there's room to optimize them later, rather than cementing CTEs-as-fences in forever as a Pg quirk. I know I'm just some schmo, but I'd vote for this. I'm certainly guilty of us

Re: [PERFORM] Hints (was Poor performance using CTE)

2012-11-20 Thread Craig Ringer
On 11/21/2012 09:35 AM, Craig James wrote: > Why not make an explicit hint syntax and document it? I've still don't > understand why "hint" is a dirty word in Postgres. There are a > half-dozen or so ways in common use to circumvent or correct > sub-optimal plans. > The reason usually given is th

[PERFORM] Hints (was Poor performance using CTE)

2012-11-20 Thread Craig James
On Tue, Nov 20, 2012 at 3:53 PM, Tom Lane wrote: > Craig Ringer writes: > > On 11/21/2012 12:06 AM, Claudio Freire wrote: > >> I meant for postgres to do automatically. Rewriting as a join wouldn't > >> work as an optimization fence the way we're used to, but pushing > >> constraints upwards can