Re: How is random_page_cost=4 ok?

Josh Berkus <josh(at)agliodbs(dot)com> writes:

>> I don't think random_page_cost actually corresponds with any real number
>> anymore. I just treat it as an uncalibrated knob you can turn and benchmark
>> the results at.
>
> And, frankly, not a useful knob. You get much more useful results out of
> effective_cache_size and cpu_* costs than you get out of messing with
> random_page_cost, unless you're running on SSD or something which would justify
> a lower RPC, or if you're compensating for our poor n-distinct estimation for
> very large tables.

Uh, that doesn't make much sense. effective_cache_size is only used currently
to estimate intra-query caching effects. It doesn't compensate for stead-state
cache hit rates.

And "our poor n-distinct estimation" is a problem which manifests by having
inconsistent estimates for number of tuples. It could be high one day and low
the next, so I don't see how biasing in any specific direction could be
helpful. In any case adjusting random_page_cost would be missing the target by
a wide margin since it's not going to fix the tuple count estimate itself in
any way and the rest of the plan will be predicated on that estimate, not just
the estimated cost of the scan.

Adjusting the cpu_* costs together amounts to the same thing as adjusting
seq_page_cost and random_page_cost together since the numbers are all relative
to each other and that's the whole set. Ie, doubling all the cpu_* costs is
the same has halving the two disk costs.

In any case your experience doesn't match mine. On a machine with a sizable
raid controller setting random_page_cost higher does generate, as expected,
plans with more bitmap heap scans which are in fact faster.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's PostGIS support!