pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedClarification on interactions between query parameters and partial indexes
3+ messages / 3 participants
[nested] [flat]
* Clarification on interactions between query parameters and partial indexes
@ 2020-02-13 23:00 PG Doc comments form <noreply@postgresql.org>
2020-02-14 16:42 ` Re: Clarification on interactions between query parameters and partial indexes Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: PG Doc comments form @ 2020-02-13 23:00 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org; +Cc: samuel.hodge@live.com
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Clarification on interactions between query parameters and partial indexes
2020-02-13 23:00 Clarification on interactions between query parameters and partial indexes PG Doc comments form <noreply@postgresql.org>
@ 2020-02-14 16:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-03-18 00:52 ` Re: Clarification on interactions between query parameters and partial indexes Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2020-02-14 16:42 UTC (permalink / raw)
To: samuel.hodge@live.com; +Cc: pgsql-docs@lists.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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Clarification on interactions between query parameters and partial indexes
2020-02-13 23:00 Clarification on interactions between query parameters and partial indexes PG Doc comments form <noreply@postgresql.org>
2020-02-14 16:42 ` Re: Clarification on interactions between query parameters and partial indexes Tom Lane <tgl@sss.pgh.pa.us>
@ 2020-03-18 00:52 ` Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 3+ messages in thread
From: Bruce Momjian @ 2020-03-18 00:52 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: samuel.hodge@live.com; pgsql-docs@lists.postgresql.org
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 +
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2020-03-18 00:52 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-02-13 23:00 Clarification on interactions between query parameters and partial indexes PG Doc comments form <noreply@postgresql.org>
2020-02-14 16:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-03-18 00:52 ` Bruce Momjian <bruce@momjian.us>
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