Re: Why does a simple query not use an obvious index?

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Greg Stark <gsstark(at)mit(dot)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