I added some debug and this is the output: 2015-12-19 14:36:15 jdbc[3]: Plan : calculate cost for plan [B, A] 2015-12-19 14:36:15 jdbc[3]: Plan : for table filter B 2015-12-19 14:36:15 jdbc[3]: Table : potential plan item cost 509,990 index PUBLIC.TBL_B.tableScan 2015-12-19 14:36:15 jdbc[3]: Table : potential plan item cost 509,990 index PUBLIC.PRIMARY_KEY_4B 2015-12-19 14:36:15 jdbc[3]: Table : potential plan item cost 509,990 index PUBLIC.TBL_B_UK_INDEX_4 2015-12-19 14:36:15 jdbc[3]: Table : potential plan item cost 509,990 index PUBLIC.TBL_B_IDX 2015-12-19 14:36:15 jdbc[3]: Plan : best plan item cost 504,890 index PUBLIC.TBL_B.tableScan 2015-12-19 14:36:15 jdbc[3]: Plan : for table filter A 2015-12-19 14:36:15 jdbc[3]: Table : potential plan item cost 11,000 index PUBLIC.TBL_A.tableScan 2015-12-19 14:36:15 jdbc[3]: Table : potential plan item cost 30 index PUBLIC.PRIMARY_KEY_4 2015-12-19 14:36:15 jdbc[3]: Table : potential plan item cost 11,000 index PUBLIC.TBL_A_UK_INDEX_4 2015-12-19 14:36:15 jdbc[3]: Plan : best plan item cost 29 index PUBLIC.PRIMARY_KEY_4 2015-12-19 14:36:15 jdbc[3]: Plan : plan cost 15,500,156 2015-12-19 14:36:15 jdbc[3]: Plan : calculate cost for plan [A, B] 2015-12-19 14:36:15 jdbc[3]: Plan : for table filter A 2015-12-19 14:36:15 jdbc[3]: Table : potential plan item cost 11,000 index PUBLIC.TBL_A.tableScan 2015-12-19 14:36:15 jdbc[3]: Table : potential plan item cost 11,000 index PUBLIC.PRIMARY_KEY_4 2015-12-19 14:36:15 jdbc[3]: Table : potential plan item cost 11,000 index PUBLIC.TBL_A_UK_INDEX_4 2015-12-19 14:36:15 jdbc[3]: Plan : best plan item cost 10,780 index PUBLIC.TBL_A.tableScan 2015-12-19 14:36:15 jdbc[3]: Plan : for table filter B 2015-12-19 14:36:15 jdbc[3]: Table : potential plan item cost 509,990 index PUBLIC.TBL_B.tableScan 2015-12-19 14:36:15 jdbc[3]: Table : potential plan item cost 509,990 index PUBLIC.PRIMARY_KEY_4B 2015-12-19 14:36:15 jdbc[3]: Table : potential plan item cost 1,020 index PUBLIC.TBL_B_UK_INDEX_4 2015-12-19 14:36:15 jdbc[3]: Table : potential plan item cost 1,020 index PUBLIC.TBL_B_IDX 2015-12-19 14:36:15 jdbc[3]: Plan : best plan item cost 1,014 index PUBLIC.TBL_B_UK_INDEX_4 2015-12-19 14:36:15 jdbc[3]: Plan : plan cost 10,952,417
The interesting part is the following two lines: 2015-12-19 14:36:15 jdbc[3]: Table : potential plan item cost 1,020 index PUBLIC.TBL_B_UK_INDEX_4 2015-12-19 14:36:15 jdbc[3]: Table : potential plan item cost 1,020 index PUBLIC.TBL_B_IDX which means that our index cost calculation does not know how to take into account that retrieving from a primary key index is way cheaper than retrieving from any other index. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
