Re: Query times change by orders of magnitude as DB ages

Hello,

> * Is there any way I can nail the query planner to a particular query plan,
> rather than have it keep changing its mind?

All these setting leads to choosing different plans. If you have small
number of complex sensitive queires, you can run explain on them with
correct settings, then re-order query (joins, subselects) according to
given query plan, and, before running it, call

set local join_collapse_limit = 1;
set local from_collapse_limit = 1;

This will prevent joins/subselects reordering inside current
transaction block, leading to consistent plans. But that gives no 100%
guarantee for chosing, for example, hash join over nested loop.

You can, as noted in presiouse message, experiment with gego_*
constants - especially, lower geqo_threshold to catch better plans
(but this can take many runs). Or, for production, set geqo=off - this
can dramatically increasy query planning, but results would be more
consistent.

>Is it normal to keep having to tune the query-planner's settings, or should it be possible to >set it once, and leave it?

I have collapse limits set for some complex reporting queries, and
think it's adequate solutuon.

>Worse still, doing a cluster of most of the tables and vacuum full analyze made most of the queries >respond much better, but the vox query became very slow again, until I set it to A (which, a few days >ago, did not work well).

Is your autovacuuming tuned correctly? For large tables, i set it
running much more agressivly then in default install.

Best regards,
Sergey Aleynikov