Re: Clarification on interactions between query parameters and partial indexes

PG Doc comments form <noreply(at)postgresql(dot)org> writes:
> In section "11.8 Partial Indexes" it states the following:

> "Matching takes place at query planning time, not at run time. As a result,
> parameterized query clauses do not work with a partial index. For example a
> prepared query with a parameter might specify “x < ?” which will never imply
> “x < 2” for all possible values of the parameter."

> We decided to run some tests to verify this statement, as we use both
> partial indexes and parameterized queries on some very large tables (100mil+
> rows). However, we are not able to replicate the stated behavior. It seems
> like the query planner is able to make use of the partial index for both
> parameterized and manually interpolated values.

> Have we misunderstood what the documentation is trying to say or has this
> limitation been fixed?

The statement is true as far as it goes: "x < $1" will never be considered
to imply "x < 2". However, there's a lot of context that's going unstated
there. In some code paths, higher-level code such as the plan cache may
try substituting the concrete value of a parameter as a constant, to see
if it can get a better (but less general) plan that way. I think that's
probably what happened in your experiment, but you didn't provide enough
details to be sure.

regards, tom lane