X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 982095E46F9 for ; Tue, 31 Aug 2004 03:00:55 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 71234-09 for ; Tue, 31 Aug 2004 06:00:44 +0000 (GMT) Received: from stark.xeocode.com (gsstark.mtl.istop.com [66.11.160.162]) by svr1.postgresql.org (Postfix) with ESMTP id 31A7D5E46E9 for ; Tue, 31 Aug 2004 03:00:41 -0300 (ADT) Received: from localhost ([127.0.0.1] helo=stark.xeocode.com) by stark.xeocode.com with smtp (Exim 3.36 #1 (Debian)) id 1C21gs-0004xA-00; Tue, 31 Aug 2004 02:00:22 -0400 To: Tom Lane Cc: Greg Stark , Mr Pink , pgsql-performance@postgresql.org Subject: Re: Why does a simple query not use an obvious index? References: <20040830073841.30957.qmail@web41108.mail.yahoo.com> <87ekloidls.fsf@stark.xeocode.com> <11438.1093899600@sss.pgh.pa.us> In-Reply-To: <11438.1093899600@sss.pgh.pa.us> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 31 Aug 2004 02:00:22 -0400 Message-ID: <87isazhmo9.fsf@stark.xeocode.com> Lines: 45 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/462 X-Sequence-Number: 8098 Tom Lane writes: > Greg Stark writes: > > However I have the complementary reaction. I find peeking at the first > > bind parameter to be scary as hell. Functions seem slightly less scary. > > FWIW, we only do it in the context of unnamed parameterized queries. I knew that. That's why I hadn't been jumping up and down screaming. I was watching though to insist on an option to disable it if it became more widespread. > As the protocol docs say, those are optimized on the assumption that > they will be executed only once. It seems entirely legitimate to me > to use the parameter values in such a case. Sure. It's a great feature to have; it means people can be more aggressive about using placeholders for other reasons without worrying about performance impacts. > We might in future get braver about using sample parameter values, > but 8.0 is conservative about it. If they're used for named parameters I would strongly recommend guc variable to control the default on a server-wide basis. It could be a variable that individual sessions could override since there's no security or resource implications. It's purely a protocol interface issue. For that matter, would it be possible for the default selectivity estimates to be a guc variable? It's something that the DBA -- or even programmer on a per-session basis -- might be able to provide a better value for his applications than any hard coded default. Or perhaps it would be one valid use of hints to provide selectivity estimates for blind placeholders. It would be nice to be able to say for example: select * from foo where col > $0 /*+ 5% */ AND col2 > $1 /*+ 10% */ Would there be any hope of convincing you that this is a justifiable use of hints; providing information that the optimizer has absolutely no possibility of ever being able to calculate on its own? -- greg