On Thu, Nov 13, 2014 at 5:47 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
David G Johnston <david.g.johnston@gmail.com> writes: > Tom Lane-2 wrote >> In the meantime, I assume that your real data contains a small percentage >> of values other than these two? If so, maybe cranking up the statistics >> target would help. If the planner knows that there are more than two >> values in the column, I think it would be less optimistic about assuming >> that the comparison value is one of the big two.
> Is there any value (or can value be added) in creating a partial index of > the form:
> archetype IN ('banner','some other rare value')
> such that the planner will see that such a value is possible but infrequent > and will, in the presence of a plan using a value contained in the partial > index, refuse to use a generic plan knowing that it will be unable to use > the very specific index that the user created?
The existence of such an index wouldn't alter the planner's statistics. In theory we could make it do so, but I seriously doubt the cost-benefit ratio is attractive, either as to implementation effort or the added planning cost.
[adding -general back in...]
While "planner hints" comes to mind...on the SQL side can we extend the "PREPARE" command with two additional keywords?
PREPARE
name [ ( data_type [, ...] ) ] [
[NO] GENERIC
]
AS statement
I was originally thinking this could attach to EXECUTE and maybe it could there as well. If EXECUTE is bare whatever the PREPARE used would be in effect (a bare PREPARE exhibiting the current dynamic behavior). If EXECUTE and PREPARE disagree execute wins and the current call is (re-)prepared as requested.
We have introduced intelligence to PREPARE/EXECUTE that is not always favorable but provide little way to override it if the user has superior knowledge. The dual role of prepared statements to both prevent SQL-injection as well as create cache-able generic plans further complicates things. In effect by supplying NO GENERIC on the PREPARE the caller is saying they only wish to make use of the SQL-injection aspect of prepared statements. Adding the EXECUTE piece allows for the same plan to be used in injection-prevention mode if the caller knows that the user-supplied value does not play well with the generic plan.
