You can use the attached sql to reproduce this issue, but I'm not sure you can
get the above result at the first time that is because when optimizer think the
2 index scan have the same cost, it will choose the first one it found, the order
depends on RelationGetIndexList. If so, you may try drop and create j1_i_im5 index.
The sense behind this patch is we still use the cost based optimizer, just when we
we find out the 2 index scans have the same cost, we prefer to use the index which
have more qual filter on Index Cond. This is implemented by adjust the qual cost
on index filter slightly higher.
Thanks for the example and the explanation.
The execution time difference in your example is pretty high to account for executing the filter on so many rows. My guess is this has to do with the heap access. For applying the filter the entire row needs to be fetched from the heap. So we should investigate this case from that angle. Another guess I have is the statistics is not correct and hence the cost is wrong.
I believe this is a statistics issue and then the cost is wrong. More characters of this
issue are: 1). If a data is out of range in the old statistics, optimizer will given an 1 row
assumption. 2). based on the 1 row assumption, for query "col1=out_of_range_val AND
col2 = any_value" Index (col1, col2) and (col1, col3) will have exactly same cost for current
cost model. 3). If the statistics was wrong, (col1, col3) maybe a very bad plan as shown
above, but index (col1, col2) should always better/no worse than (col1, col3) in any case.
4). To expand the rule, for query "col1 = out_of_range_val AND col2 = any_value AND col3 = any_val",
index are (col1, col2, col_m) and (col1, col_m, col_n), the former index will aways has better/no worse
than the later one. 5). an statistics issue like this is not uncommon, for example
an log based application, creation_date is very easy to out of range in statistics.
so we need to optimize the cost model for such case, the method is the patch I mentioned above.
I can't have a solid data to prove oracle did something similar, but based on the talk with my
customer, oracle is likely did something like this.