Re: Bind variables

Mladen Gogala <mgogala(at)vmsinfo(dot)com> writes:
> What does optimizer do when it encounters a bind variable? Does it have
> a method of "peeking", like some other databases or it has some
> predefined values and percentages?

There are three cases:

1. No information available, as when you PREPARE a parameterized
statement. It'll just fall back to some generic selectivity estimates
for clauses that contain parameters.

2. Parameter values available but not considered trustworthy (IIRC
this is invoked by protocol-level Parse/Bind on the unnamed statement).
The values will be used as-is for selectivity estimation purposes,
but the planner won't make any decisions that absolutely depend on
these values being the ones used at runtime.

3. Plan is being generated for the specific values and will be discarded
after use. The parameter values are used just as if they were literal
constants. This allows for example LIKE index optimization and
partition optimizations that depend on the parameter values.

I don't have time at the moment to look up exactly which cases from the
user's viewpoint translate into each of these behaviors (and it'll be
changing in 9.1 anyway ...)

regards, tom lane