Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 8FE0E9FB2DE; Thu, 12 Oct 2006 19:15:49 -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 66276-03-2; Thu, 12 Oct 2006 22:15:23 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- X-Greylist: from auto-whitelisted by SQLgrey- Received: from servata.com (ip-216-152-249-241.servata.com [216.152.249.241]) by postgresql.org (Postfix) with ESMTP id 852169FB33F; Thu, 12 Oct 2006 19:15:23 -0300 (ADT) Received: from [209.162.219.253] (helo=dogma.v10.wvs) by servata.com with esmtpa (Exim 4.50) id 1GY8pS-00024N-CT; Thu, 12 Oct 2006 15:15:02 -0700 Subject: Re: Hints proposal From: Jeff Davis To: pgsql-hackers@postgreSQL.org Cc: Arjen van der Meijden , Csaba Nagy , "Jim C. Nasby" , postgres performance list In-Reply-To: <19693.1160688528@sss.pgh.pa.us> References: <20061012151439.GT28647@nasby.net> <1160671375.31966.84.camel@dogma.v10.wvs> <1160673340.28751.83.camel@coppola.muc.ecircle.de> <1160680031.31966.128.camel@dogma.v10.wvs> <452EAE80.5030501@tweakers.net> <19693.1160688528@sss.pgh.pa.us> Content-Type: text/plain Date: Thu, 12 Oct 2006 15:15:03 -0700 Message-Id: <1160691303.31966.139.camel@dogma.v10.wvs> Mime-Version: 1.0 X-Mailer: Evolution 2.0.4 (2.0.4-7) Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.134 tagged_above=0 required=5 tests=FORGED_RCVD_HELO, SPF_HELO_PASS X-Spam-Level: X-Archive-Number: 200610/266 X-Sequence-Number: 21395 On Thu, 2006-10-12 at 17:28 -0400, Tom Lane wrote: > [ trying once again to push this thread over to -hackers where it belongs ] > > Arjen van der Meijden writes: > > On 12-10-2006 21:07 Jeff Davis wrote: > >> On Thu, 2006-10-12 at 19:15 +0200, Csaba Nagy wrote: > >> To formalize the proposal a litte, you could have syntax like: > >> CREATE HINT [FOR USER username] MATCHES regex APPLY HINT some_hint; > >> > >> Where "some_hint" would be a hinting language perhaps like Jim's, except > >> not guaranteed to be compatible between versions of PostgreSQL. The > >> developers could change the hinting language at every release and people > >> can just re-write the hints without changing their application. > > Do you have any idea how much push-back there would be to that? In > practice we'd be bound by backwards-compatibility concerns for the hints > too. > No, I don't have any idea, except that it would be less push-back than changing a language that's embedded in client code. Also, I see no reason to think that a hint would not be obsolete upon a new release anyway. > The problems that you are seeing all come from the insistence that a > hint should be textually associated with a query. Using a regex is a > little better than putting it right into the query, but the only thing "Little better" is all I was going for. I was just making the observation that we can separate two concepts: (1) Embedding code in the client's queries, which I see as very undesirable and unnecessary (2) Providing very specific hints which at least gives us a place to talk about the debate more reasonably. > that really fixes is not having the hints directly embedded into > client-side code. It's still wrong at the conceptual level. > I won't disagree with that. I will just say it's no more wrong than applying the same concept in addition to embedding the hints in client queries. > The right way to think about it is to ask why is the planner not picking > the right plan to start with --- is it missing a statistical > correlation, or are its cost parameters wrong for a specific case, or > is it perhaps unable to generate the desired plan at all? (If the > latter, no amount of hinting is going to help.) If it's a statistics or > costing problem, I think the right thing is to try to fix it with hints > at that level. You're much more likely to fix the behavior across a > class of queries than you will be with a hint textually matched to a > specific query. > Agreed. Regards, Jeff Davis