Re: multi-column index

Whoa Josh! I don't believe you're going to reduce the cost by 10 times
through a bit of tweaking - not without lowering the sequential scan
cost as well.

The only thing I can think of is perhaps his primary index drastically
needs repacking. Otherwise, isn't there a real anomaly here? Halving the
key width might account for some of it, but it's still miles out of court.

Actually, I'm surprised the planner came up with such a low cost for the
single column index, unless ... perhaps correlation statistics aren't
used when determining costs for multi-column indexes?

Josh Berkus wrote:

>Pretty simple, really. Look at the cost calculations for the index scan for
>the multi-column index. PostgreSQL believes that:
>The cost of a seq scan is 4788.14
>The cost of an 2-column index scan is 36720.39
>The cost of a 1-column index scan is 916.24
>
>Assuming that you ran each of these queries multiple times to eliminate
>caching as a factor, the issue is that the cost calculations are wrong. We
>give you a number of GUC variables to change that:
>effective_cache_size
>random_page_cost
>cpu_tuple_cost
>etc.
>
>See the RUNTIME-CONFIGURATION docs for more details.
>