From: Tom Lane <tgl@sss.pgh.pa.us>
To: samuel.hodge@live.com
Cc: pgsql-docs@lists.postgresql.org
Subject: Re: Clarification on interactions between query parameters and partial indexes
Date: Fri, 14 Feb 2020 11:42:34 -0500
Message-ID: <12809.1581698554@sss.pgh.pa.us> (raw)
In-Reply-To: <158163485510.6759.4276544280525471906@wrigleys.postgresql.org>
References: <158163485510.6759.4276544280525471906@wrigleys.postgresql.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: tgl@sss.pgh.pa.us, samuel.hodge@live.com, pgsql-docs@lists.postgresql.org
Subject: Re: Clarification on interactions between query parameters and partial indexes
In-Reply-To: <12809.1581698554@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox