Re: PGSQL 7.4 -> 8.1 migration & performance problem

[Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>]

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