Clarification on interactions between query parameters and partial indexes

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Clarification on interactions between query parameters and partial indexes

apt.postgresql.org Repository Update
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
Reply | Threaded
Open this post in threaded view
|

Re: Clarification on interactions between query parameters and partial indexes

Tom Lane-2
PG Doc comments form <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: Clarification on interactions between query parameters and partial indexes

Bruce Momjian
On Fri, Feb 14, 2020 at 11:42:34AM -0500, Tom Lane wrote:

> PG Doc comments form <[hidden email]> 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  <[hidden email]>        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 +