Re: Postgresql selecting strange index for simple query

Maxim Boguk <mboguk(at)masterhost(dot)ru> writes:
> Tom Lane wrote:
>> Could you send me a dump of this test_table off-list? It seems like
>> there must be something strange about the stats of last_change_time,
>> but I don't feel like guessing about what it is ...

> Here attached is small part of table (1160 rows) wich show same wrong index selection.

Thanks. It turns out the funny behavior is not because of
last_change_time, but because wrong_index's *first* column is well
correlated with the table ordering. In the test case it actually
gets a 1.0 correlation score because it's a constant, but I suppose
that in your real example the leading column is well correlated with
the row insertion order.

The planner is favoring the multicolumn index more than it should
because of the correlation, which is an estimation bug that I've
started a thread about on pgsql-hackers. In the meantime, though,
it seems that the issue only occurs for a limited range of
random_page_cost settings. At high random_page_cost the index page
fetch cost increases enough to make the "wrong" index not be favored,
and at low random_page_cost the index per-tuple costs do the same.
So as a workaround you might look into whether your fully-cached
situation is modeled better by having both random_page_cost and
seq_page_cost less than 1.0. In the past we've seen some evidence
that setting them both to 0.1 or so produces results that are more
accurate for databases that are fully cached in RAM. (Alternatively
you can jack up the various CPU costs, but there are more of them to
mess with.)

regards, tom lane