Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 5CC549FB292 for ; Mon, 9 Oct 2006 19:30:44 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 33612-03 for ; Mon, 9 Oct 2006 22:30:35 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id EA94C9FB28F for ; Mon, 9 Oct 2006 19:30:33 -0300 (ADT) Received: by noel.decibel.org (Postfix, from userid 1001) id 823C956431; Mon, 9 Oct 2006 17:30:32 -0500 (CDT) Received: (hashcash-sendmail, from uid 1001); Mon, 9 Oct 2006 17:30:31 -0500 Date: Mon, 9 Oct 2006 17:30:31 -0500 From: "Jim C. Nasby" To: Tobias Brox Cc: Chris Browne , pgsql-performance@postgresql.org Subject: Re: Simple join optimized badly? Message-ID: <20061009223030.GW72517@nasby.net> References: <20061009213303.GA19144@oppetid.no> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20061009213303.GA19144@oppetid.no> X-Operating-System: FreeBSD 6.0-RELEASE-p4 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Hashcash: 1:20:061009:tobias@nordicbet.com::pX+QmdleGaeylnfk:00000000000000000 0000000000000000000000003NKq X-Hashcash: 1:20:061009:cbbrowne@acm.org::SI617MxbEWLZupZ0:0Ac3u X-Hashcash: 1:20:061009:pgsql-performance@postgresql.org::M5OXQCfDup38ox82:00000 0000000000000000000000000WJT X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200610/172 X-Sequence-Number: 21301 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)