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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
