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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
