Thread: Clarification on interactions between query parameters and partial indexes

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/12/indexes-partial.html
Description:

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?

PostgreSQL 10.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313
(Red Hat 4.4.7-18), 64-bit
PHP 7.2
PDO::prepare

PG Doc comments form <noreply@postgresql.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

On Fri, Feb 14, 2020 at 11:42:34AM -0500, Tom Lane wrote:
> PG Doc comments form <noreply@postgresql.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@momjian.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 +