Re: Optimizer problem with multi-column index

Marc Schablewski <ms(at)clickware(dot)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