From: | Richard Neill <rn214(at)cam(dot)ac(dot)uk> |
---|---|
To: | |
Cc: | Richard Neill <rn214(at)cam(dot)ac(dot)uk>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Query times change by orders of magnitude as DB ages |
Date: | 2009-11-25 12:27:28 |
Message-ID: | 4B0D22B0.1040806@cam.ac.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Neill | 2009-11-25 12:34:26 | How exactly does Analyze work? |
Previous Message | Richard Neill | 2009-11-25 12:22:40 | Re: Query times change by orders of magnitude as DB ages |