Clarification on interactions between query parameters and partial indexes

From: PG Doc comments form <noreply(at)postgresql(dot)org>
To: pgsql-docs(at)lists(dot)postgresql(dot)org
Cc: samuel(dot)hodge(at)live(dot)com
Subject: Clarification on interactions between query parameters and partial indexes
Date: 2020-02-13 23:00:55
Message-ID: 158163485510.6759.4276544280525471906@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-docs

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

Responses

Browse pgsql-docs by date

  From Date Subject
Next Message Dave Cramer 2020-02-14 16:05:25 documenting divergence from the SQL spec
Previous Message Tom Lane 2020-02-13 20:37:27 PDF doc build is broken on recent Fedora