Thread
-
Re: Simple join optimized badly?
Tobias Brox <tobias@nordicbet.com> — 2006-10-09T21:33:03Z
[Jim C. Nasby - Mon at 04:18:27PM -0500] > I can agree to that, but we'll never get any progress so long as every > time hints are brought up the response is that they're evil and should > never be in the database. I'll also say that a very simple hinting > language (ie: allowing you to specify access method for a table, and > join methods) would go a huge way towards enabling app developers to get > stuff done now while waiting for all these magical optimizer > improvements that have been talked about for years. Just a comment from the side line; can't the rough "set enable_seqscan=off" be considered as sort of a hint anyway? There have been situations where we've actually had to resort to such crud. Beeing able to i.e. force a particular index is something I really wouldn't put into the application except for as a very last resort, _but_ beeing able to force i.e. the use of a particular index in an interactive 'explain analyze'-query would often be ... if not outright useful, then at least very interessting.
-
Re: Simple join optimized badly?
Jim Nasby <jim@nasby.net> — 2006-10-09T22:30:31Z
On Mon, Oct 09, 2006 at 11:33:03PM +0200, Tobias Brox wrote: > [Jim C. Nasby - Mon at 04:18:27PM -0500] > > I can agree to that, but we'll never get any progress so long as every > > time hints are brought up the response is that they're evil and should > > never be in the database. I'll also say that a very simple hinting > > language (ie: allowing you to specify access method for a table, and > > join methods) would go a huge way towards enabling app developers to get > > stuff done now while waiting for all these magical optimizer > > improvements that have been talked about for years. > > Just a comment from the side line; can't the rough "set > enable_seqscan=off" be considered as sort of a hint anyway? There have > been situations where we've actually had to resort to such crud. > > Beeing able to i.e. force a particular index is something I really > wouldn't put into the application except for as a very last resort, > _but_ beeing able to force i.e. the use of a particular index in an > interactive 'explain analyze'-query would often be ... if not outright > useful, then at least very interessting. One of the big problems with doing set enable_...=off is that there's no way to embed that into something like a view, so you're almost forced into putting into the application code itself, which makes matters even worse. If you could hint this within a query (maybe even on a per-table level), you could at least encapsulate that into a view. -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
-
Re: Simple join optimized badly?
Joshua D. Drake <jd@commandprompt.com> — 2006-10-09T22:41:09Z
> > One of the big problems with doing set enable_...=off is that there's no > way to embed that into something like a view, so you're almost forced > into putting into the application code itself, which makes matters even > worse. If you could hint this within a query (maybe even on a per-table > level), you could at least encapsulate that into a view. You can easily pass multiple statements within a single exec() or push it into an SPF. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ -
Re: Simple join optimized badly?
Tom Lane <tgl@sss.pgh.pa.us> — 2006-10-09T22:45:16Z
"Jim C. Nasby" <jim@nasby.net> writes: > One of the big problems with doing set enable_...=off is that there's no > way to embed that into something like a view, so you're almost forced > into putting into the application code itself, which makes matters even > worse. If you could hint this within a query (maybe even on a per-table > level), you could at least encapsulate that into a view. You've almost reinvented one of the points that was made in the last go-round on the subject of hints, which is that keeping them out of the application code is an important factor in making them manageable by a DBA. Hints stored in a system catalog (and probably having the form of "make this statistical assumption" rather than specifically "use that plan") would avoid many of the negatives. regards, tom lane
-
Re: Simple join optimized badly?
Jim Nasby <jim@nasby.net> — 2006-10-10T14:00:50Z
On Mon, Oct 09, 2006 at 06:45:16PM -0400, Tom Lane wrote: > "Jim C. Nasby" <jim@nasby.net> writes: > > One of the big problems with doing set enable_...=off is that there's no > > way to embed that into something like a view, so you're almost forced > > into putting into the application code itself, which makes matters even > > worse. If you could hint this within a query (maybe even on a per-table > > level), you could at least encapsulate that into a view. > > You've almost reinvented one of the points that was made in the last > go-round on the subject of hints, which is that keeping them out of the > application code is an important factor in making them manageable by a > DBA. Hints stored in a system catalog (and probably having the form of > "make this statistical assumption" rather than specifically "use that > plan") would avoid many of the negatives. Sure, but IIRC no one's figured out what that would actually look like, while it's not hard to come up with a syntax that allows you to tell the optimizer "scan index XYZ to access this table". (And if there's real interest in adding that I'll come up with a proposal.) I'd rather have the ugly solution sooner rather than the elegant one later (if ever). -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
-
Re: Simple join optimized badly?
Jim Nasby <jim@nasby.net> — 2006-10-10T14:02:22Z
On Mon, Oct 09, 2006 at 03:41:09PM -0700, Joshua D. Drake wrote: > > > > One of the big problems with doing set enable_...=off is that there's no > > way to embed that into something like a view, so you're almost forced > > into putting into the application code itself, which makes matters even > > worse. If you could hint this within a query (maybe even on a per-table > > level), you could at least encapsulate that into a view. > > You can easily pass multiple statements within a single exec() or push > it into an SPF. Unless I'm missing something, putting multiple statements in a single exec means you're messing with the application code. And you can't update a SRF (also means messing with the application code). Though, I suppose you could update a view that pulled from an SRF... -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
-
Re: Simple join optimized badly?
Tom Lane <tgl@sss.pgh.pa.us> — 2006-10-10T14:14:48Z
"Jim C. Nasby" <jim@nasby.net> writes: > I'd rather have the ugly solution sooner rather than the elegant one > later (if ever). The trouble with that is that we couldn't ever get rid of it, and we'd be stuck with backward-compatibility concerns with the first (over simplified) design. It's important to get it right the first time, at least for stuff that you know perfectly well is going to end up embedded in application code. regards, tom lane
-
Re: Simple join optimized badly?
Jim Nasby <jim@nasby.net> — 2006-10-10T14:21:02Z
On Tue, Oct 10, 2006 at 10:14:48AM -0400, Tom Lane wrote: > "Jim C. Nasby" <jim@nasby.net> writes: > > I'd rather have the ugly solution sooner rather than the elegant one > > later (if ever). > > The trouble with that is that we couldn't ever get rid of it, and we'd > be stuck with backward-compatibility concerns with the first (over > simplified) design. It's important to get it right the first time, > at least for stuff that you know perfectly well is going to end up > embedded in application code. We've depricated things before, I'm sure we'll do it again. Yes, it's a pain, but it's better than not having anything release after release. And having a formal hint language would at least allow us to eventually clean up some of these oddball cases, like the OFFSET 0 hack. I'm also not convinced that even supplimental statistics will be enough to ensure the planner always does the right thing, so query-level hints may have to stay (though it'd be great if that wasn't the case). -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
-
Re: Simple join optimized badly?
Joshua D. Drake <jd@commandprompt.com> — 2006-10-10T14:23:30Z
Jim C. Nasby wrote: > On Mon, Oct 09, 2006 at 03:41:09PM -0700, Joshua D. Drake wrote: >>> One of the big problems with doing set enable_...=off is that there's no >>> way to embed that into something like a view, so you're almost forced >>> into putting into the application code itself, which makes matters even >>> worse. If you could hint this within a query (maybe even on a per-table >>> level), you could at least encapsulate that into a view. >> You can easily pass multiple statements within a single exec() or push >> it into an SPF. > > Unless I'm missing something, putting multiple statements in a single > exec means you're messing with the application code. And you can't > update a SRF (also means messing with the application code). Though, I > suppose you could update a view that pulled from an SRF... I always think of application code as outside the db. I was thinking more in layers. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ -
Re: Simple join optimized badly?
Bruno Wolff III <bruno@wolff.to> — 2006-10-10T16:45:34Z
On Mon, Oct 09, 2006 at 23:33:03 +0200, Tobias Brox <tobias@nordicbet.com> wrote: > > Just a comment from the side line; can't the rough "set > enable_seqscan=off" be considered as sort of a hint anyway? There have > been situations where we've actually had to resort to such crud. That only works for simple queries. To be generally useful, you want to be able to hint how to handle each join being done in the query. The current controlls affect all joins.
-
Re: Simple join optimized badly?
Josh Berkus <josh@agliodbs.com> — 2006-10-10T17:28:29Z
Jim, > We've depricated things before, I'm sure we'll do it again. Yes, it's a > pain, but it's better than not having anything release after release. > And having a formal hint language would at least allow us to eventually > clean up some of these oddball cases, like the OFFSET 0 hack. > > I'm also not convinced that even supplimental statistics will be enough > to ensure the planner always does the right thing, so query-level hints > may have to stay (though it'd be great if that wasn't the case). "stay"? I don't think that the general developers of PostgreSQL are going to *accept* anything that stands a significant chance of breaking in one release. You have you challange for the EDB development team: come up with a hinting language which is flexible enough not to do more harm than good (hint: it's not Oracle's hints). -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco
-
Re: Simple join optimized badly?
Jim Nasby <jim@nasby.net> — 2006-10-10T19:26:23Z
On Tue, Oct 10, 2006 at 10:28:29AM -0700, Josh Berkus wrote: > Jim, > > > We've depricated things before, I'm sure we'll do it again. Yes, it's a > > pain, but it's better than not having anything release after release. > > And having a formal hint language would at least allow us to eventually > > clean up some of these oddball cases, like the OFFSET 0 hack. > > > > I'm also not convinced that even supplimental statistics will be enough > > to ensure the planner always does the right thing, so query-level hints > > may have to stay (though it'd be great if that wasn't the case). > > "stay"? I don't think that the general developers of PostgreSQL are going > to *accept* anything that stands a significant chance of breaking in one > release. You have you challange for the EDB development team: come up > with a hinting language which is flexible enough not to do more harm than > good (hint: it's not Oracle's hints). My point was that I think we'll always have a need for fine-grained (ie: table and join level) hints, even if we do get the ability for users to over-ride the statistics system. It's just not possible to come up with automation that will handle every possible query that can be thrown at a system. I don't see how that means breaking anything in a given release. Worst-case, the optimizer might be able to do a better job of something than hints written for an older version of the database, but that's going to be true of any planner override we come up with. BTW, I'm not speaking for EnterpriseDB or it's developers here... query hints are something I feel we've needed for a long time. -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)