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

Sergey Aleynikov wrote:
> 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;

It's a simple query, but using a complex view. So I can't really
re-order it.

> 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.

Are you saying that this means that the query planner frequently makes
the wrong choice here?

>
>> 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.

I hadn't changed it from the defaults; now I've changed it to:

autovacuum_max_workers = 6
autovacuum_vacuum_scale_factor = 0.002
autovacuum_analyze_scale_factor = 0.001

is that enough?

The DB isn't growing that much, but  it does seem to need frequent
vacuum/analyze.


Richard