Skip site navigation (1) Skip section navigation (2)

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

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 (view raw or flat)
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


In response to

Responses

pgsql-performance by date

Next:From: Richard NeillDate: 2009-11-25 12:34:26
Subject: How exactly does Analyze work?
Previous:From: Richard NeillDate: 2009-11-25 12:22:40
Subject: Re: Query times change by orders of magnitude as DB ages

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group