Query times change by orders of magnitude as DB ages

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