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
In-reply-to: <158163485510.6759.4276544280525471906@wrigleys.postgresql.org>
References: <158163485510.6759.4276544280525471906@wrigleys.postgresql.org>
Comments: In-reply-to PG Doc comments form <noreply@postgresql.org>
	message dated "Thu, 13 Feb 2020 23:00:55 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <12808.1581698554.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Fri, 14 Feb 2020 11:42:34 -0500
Message-ID: <12809.1581698554@sss.pgh.pa.us>
Precedence: bulk

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 resu=
lt,
> parameterized query clauses do not work with a partial index. For exampl=
e a
> prepared query with a parameter might specify =E2=80=9Cx < ?=E2=80=9D wh=
ich will never imply
> =E2=80=9Cx < 2=E2=80=9D 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 (100=
mil+
> rows). However, we are not able to replicate the stated behavior. It see=
ms
> 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 thi=
s
> 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