pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Greg Stark <gsstark@mit.edu>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Greg Stark <gsstark@mit.edu>
Cc: Mr Pink <mr_pink_is_the_only_pro@yahoo.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Why does a simple query not use an obvious index?
Date: 31 Aug 2004 02:00:22 -0400
Message-ID: <87isazhmo9.fsf@stark.xeocode.com> (raw)
In-Reply-To: <11438.1093899600@sss.pgh.pa.us>
References: <20040830073841.30957.qmail@web41108.mail.yahoo.com>
	<87ekloidls.fsf@stark.xeocode.com>
	<11438.1093899600@sss.pgh.pa.us>


Tom Lane <tgl@sss.pgh.pa.us> writes:

> Greg Stark <gsstark@mit.edu> 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




view thread (28+ messages)  latest in thread

Message-ID: <87isazhmo9.fsf@stark.xeocode.com>
Permalink:  ../87isazhmo9.fsf@stark.xeocode.com/
Also on:    postgresql.org/message-id/87isazhmo9.fsf@stark.xeocode.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: gsstark@mit.edu, tgl@sss.pgh.pa.us, mr_pink_is_the_only_pro@yahoo.com
  Subject: Re: Why does a simple query not use an obvious index?
  In-Reply-To: <87isazhmo9.fsf@stark.xeocode.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox