Re: Clarification on interactions between query parameters and partial indexes

On Fri, Feb 14, 2020 at 11:42:34AM -0500, Tom Lane wrote:
> 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.

Also. the PREPARE docs might explain some of your test results:

https://www.postgresql.org/docs/12/sql-prepare.html

A prepared statement can be executed with either a generic plan or
a custom plan. A generic plan is the same across all executions,
while a custom plan is generated for a specific execution using
the parameter values given in that call. Use of a generic plan
avoids planning overhead, but in some situations a custom plan
will be much more efficient to execute because the planner can
make use of knowledge of the parameter values. (Of course, if
the prepared statement has no parameters, then this is moot and
a generic plan is always used.)

By default (that is, when plan_cache_mode is set to auto), the
server will automatically choose whether to use a generic or
custom plan for a prepared statement that has parameters. The
current rule for this is that the first five executions are done
with custom plans and the average estimated cost of those plans
is calculated. Then a generic plan is created and its estimated
cost is compared to the average custom-plan cost. Subsequent
executions use the generic plan if its cost is not so much higher
than the average custom-plan cost as to make repeated replanning
seem preferable.

--
Bruce Momjian <bruce(at)momjian(dot)us> https://momjian.us
EnterpriseDB https://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +