Marc Schablewski <m...@clickware.de> writes: > we have an issue concerning multi-column indexes not being used by the > planner.
I see no particular bug here, just a rational response to an artificial test case. The planner is being discouraged by the random character of the data in your first index column: that means that an indexscan on that index would jump all over the place while accessing the table. In contrast, the other index is *exactly* in heap order and so using it will result in nice sequential touches of the heap. So with default random_page_cost, the cost to use the two-column index comes out quite a bit higher than the cost to use the one-column index. A bitmap scan is less subject to the random-access problem, but it still loses out compared to following an index that's exactly in heap order. Whether this test case corresponds very well to your real use case is hard to say, but it seems a bit extreme from here. BTW, had you vacuumed the table, the planner would've preferred an index-only scan of the two-column index, since with the table marked all-visible the potential for lots of random fetches from the heap goes away. But you didn't. If these plan choices don't correspond to the actual runtimes you're seeing, that probably suggests that you need to lower random_page_cost for your environment. This test case is small enough to fit in RAM on most machines, so you'd have to set random_page_cost to 1 to expect to get accurate predictions for the test case as-is. I don't know how large your real table is ... regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs