Re: PGSQL 7.4 -> 8.1 migration & performance problem

I wrote:
> So basically 8.1 is being too optimistic about the value of ANDing
> multiple indexes. If you try setting enable_bitmapscan off, you'll
> probably find 8.1 beating 7.4 handily for this query. That's a really
> blunt-instrument solution of course, and I wouldn't recommend it for
> production because it'll probably kill performance elsewhere.

A less brute-force way to fix this would be to adjust the planner cost
parameters. Am I right in guessing that your database is small enough
to fit into RAM on the new server? If so, it would be reasonable to
reduce random_page_cost, perhaps all the way to 1.0, and this would
probably improve the quality of the planner's choices for you. Another
thing you should look at is increasing the cpu-cost parameters. The
numbers in your EXPLAIN ANALYZE results suggest that on your new machine
the cost of processing an index tuple is about 1/50th of the cost of
touching an index page; that is, you ought to have cpu_index_tuple_cost
plus cpu_operator_cost around 0.02. I'd try setting each of them to
0.01 and increasing cpu_tuple_cost a little bit, maybe to 0.02.

regards, tom lane