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

Query times change by orders of magnitude as DB ages

From: Richard Neill <rn214(at)cam(dot)ac(dot)uk>
To: PostgreSQL Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Query times change by orders of magnitude as DB ages
Date: 2009-11-22 15:31:19
Message-ID: 4B095947.7010106@cam.ac.uk (view raw or flat)
Thread:
Lists: pgsql-performance
Dear All,

Thanks for your help earlier with the previous question. I wonder if I 
might ask another.


We have various queries that need to run, of which I'm going to focus on 
2, "vox" and "du_report".

Both of them are extremely sensitive to the precise values of 
random_page_cost and seq_page_cost. Experimentally, I've used:

  A:  seq_page_cost = 0.25;  random_page_cost = 0.75
  B:  seq_page_cost = 0.5;  random_page_cost = 2
  C: seq_page_cost = 1;  random_page_cost = 4

(and a few in between).


If I pick the wrong one, then either vox becomes 2 orders of magnitude 
slower (22ms -> 3.5 seconds), or du_report becomes 10x slower. I can't 
use the same setting for both.

So, as a very ugly hack, I've tuned the sweet spots for each query.
Vox normally sits at B; du_report at C.


Now, the real killer is that the position of that sweet spot changes 
over time as the DB ages over a few days (even though autovacuum is on).

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


*  Why is the query planner so precisely sensitive to the combination of 
page costs and time since last vacuum full?

* Why is it that what improves one query can make another get so much worse?

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

* 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?


Tuning this feels rather like adjusting several old radios, which are 
exceptionally finicky about the precise settings, having a very sharp 
resonance peak (in different places), and which drift out of tune at 
different rates. I must be doing something wrong, but what?

Thanks for your advice,

Richard



Responses

pgsql-performance by date

Next:From: Sergey AleynikovDate: 2009-11-22 16:13:46
Subject: Re: Query times change by orders of magnitude as DB ages
Previous:From: Richard NeillDate: 2009-11-22 15:14:22
Subject: Re: Postgres query completion status?

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