Date: Tue, 17 Mar 2020 20:52:29 -0400
From: Bruce Momjian <bruce@momjian.us>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: samuel.hodge@live.com, pgsql-docs@lists.postgresql.org
Subject: Re: Clarification on interactions between query parameters and
 partial indexes
Message-ID: <20200318005229.GE17915@momjian.us>
References: <158163485510.6759.4276544280525471906@wrigleys.postgresql.org>
 <12809.1581698554@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <12809.1581698554@sss.pgh.pa.us>
User-Agent: Mutt/1.10.1 (2018-07-13)
Precedence: bulk

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 +