Maxim Boguk <mboguk@xxxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
