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