Re: PostgreSQL performance enhancement when query

Csaba Nagy wrote:

> On Tue, 2006-08-08 at 12:36, Constantin Teodorescu wrote:
>> We have tried PGStatement#setPrepareThreshold with 1 as the threshold
>> but it's not a good solution.
>> Actually is worst. Considering that you have 5 different query plans,
>> you are selecting approx. random one of them, not taking into account
>> the statistics.
>
> Wrong, you'll select _the same_ plan, that's what matters. If it's not
> the plan you wanted, you have to rewrite the query, and try again, but
> once you got the plan you wanted, it's pretty much you'll get always the
> same plan. So you only need to test as long as you get the right query
> to trigger the right plan... but of course this requires that your
> queries are so constructed to always be OK with that plan, regardless
> the parameter values. Usually this means a suboptimal plan, but stable
> execution times.

Well it should usually be possible to find a query that gives a stable
query plan. However in some cases stable query plan means varying
performance which is also not ideal. So you have to actually find a
query that will give you stable performance (which often means finding a
query that is a good compromise and that producses a stable plan).

But if you have changing data, very different selectivity for values etc
this can become very hard, maybe even impossible. For these kinds of
queries it might just be easier to put in the effort to specify (parts
of) the query plan explicitly. Especially as an interim solution until a
new stable release comes around that fixes the underlying planner issue
(which will usually be atleast 6-12 months).

regards,
Lukas