Re: Bind message - Mailing list pgsql-jdbc

Hi, Oliver,

Oliver Jowett wrote:

>> Ah. But usually, the other parameter values for the same prepared
>> statement produce similar query plans, because they're the same use case
>> on similar data.
>
> For the "wrong" values though the plan might be pathologically bad.

Yes, I know, and that's why I'd like to retain the possibilitiy of
preparing a new statement with a different first parameter set for
different use cases, and introduce the option of switching back to the
current behaviour.

>> Maybe this behaviour should be configurable via some session
>> configuration variable?
>
> Isn't this just a very roundabout way of overriding the planner? i.e.
> what you are really doing is saying "ignore your statistics, I know more
> about the parameter values I'm going to give you than I'm actually
> telling you"..

No, it's telling the planner "You can assume that all those queries for
my statement are similar enough that the same query plan will fit them,
so you can safe the overhead of both parsing and planning".

Don't forget that, in the current implementation, the query planner has
no choice but planning the query without any actual parameter values,
which is likely to give equal or worse results in most cases.

The use case I have in mind are geometric data lookup of mostly static
data (PostGIS objects) that are nicely distributed, and the queries all
fetch a small bounding box hitting a fraction of at most 1/1000th, more
likely 1/1000000 of the data. For them, the GIST index scan is the best
query plan.


Another option would be that the planner uses some self-learning
algorithm that analyzes the parameter sets for a given prepared
statement, and sees whether it's better to replan always, keep the same
plan, or even efficiently switch between a small set of fixed plans. But
that's out of scope for now, I'm afraid.

Thanks,
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org