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

On Sun, Nov 22, 2009 at 10:31 AM, Richard Neill <rn214(at)cam(dot)ac(dot)uk> wrote:
> 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?

It sounds like your tables are getting bloated. If you have
autovacuum turned on, this shouldn't be happening. What sort of
workload is this? What PG version?

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

Because it changes the plan you get.

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

See other responses.

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

Leave it.

...Robert