Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 92CF39FB2D3; Thu, 12 Oct 2006 19:03:05 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 17699-05; Thu, 12 Oct 2006 19:02:30 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- X-Greylist: from auto-whitelisted by SQLgrey- Received: from lists.commandprompt.com (host-130.commandprompt.net [207.173.203.130]) by postgresql.org (Postfix) with ESMTP id 690919FB2BE; Thu, 12 Oct 2006 19:02:29 -0300 (ADT) Received: from alvh.no-ip.org (200-126-100-219.bk7-dsl.surnet.cl [200.126.100.219]) (authenticated bits=0) by lists.commandprompt.com (8.13.7/8.13.6) with ESMTP id k9CM2CTa029324; Thu, 12 Oct 2006 15:02:17 -0700 Received: by alvh.no-ip.org (Postfix, from userid 1000) id D0C21C2DC70; Thu, 12 Oct 2006 18:02:07 -0400 (CLT) Date: Thu, 12 Oct 2006 18:02:07 -0400 From: Alvaro Herrera To: Bucky Jordan Cc: josh@agliodbs.com, "Jim C. Nasby" , pgsql-hackers@postgresql.org, pgsql-performance@postgresql.org Subject: Re: [HACKERS] Hints proposal Message-ID: <20061012220207.GI18764@alvh.no-ip.org> Mail-Followup-To: Bucky Jordan , josh@agliodbs.com, "Jim C. Nasby" , pgsql-hackers@postgresql.org, pgsql-performance@postgresql.org References: <200610121358.22739.josh@agliodbs.com> <78ED28FACE63744386D68D8A9D1CF5D4209CA5@MAIL.corp.lumeta.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <78ED28FACE63744386D68D8A9D1CF5D4209CA5@MAIL.corp.lumeta.com> User-Agent: Mutt/1.5.13 (2006-08-11) X-Virus-Scanned: ClamAV version 0.88.4, clamav-milter version 0.88.4 on projects.commandprompt.com X-Virus-Status: Clean X-Greylist: Sender succeded SMTP AUTH authentication, not delayed by milter-greylist-1.6 (lists.commandprompt.com [192.168.2.159]); Thu, 12 Oct 2006 15:02:22 -0700 (PDT) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.782 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200610/264 X-Sequence-Number: 21393 Bucky Jordan wrote: > What about using regular expressions, plus, if you have a function > (views, or any other statement that is stored), you can assign a rule to > that particular function. So you get matching, plus explicit selection. > This way it's easy to find all your hints, turn them off, manage them, > etc. (Not to mention dynamically generated SQL is ugly enough without > having to put hints in there). The regular expression idea that's being floated around makes my brain feel like somebody is screeching a blackboard nearby. I don't think it's a sane idea. I think you could achieve something similar by using stored plan representations, like we do for rewrite rules. So you'd look for, say, a matching join combination in a catalog, and get a selectivity from a function that would get the selectivities of the conditions on the base tables. Or something like that anyway. That gets ugly pretty fast when you have to extract selectivities for all the possible join paths in any given query. But please don't talk about regular expressions. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support