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.

Reply via email to