Hi, Thanks a lot for reporting the problem and analyzing the root cause!
I just found out the problem also exists without using MVCC. Maybe there is an additional problem when using MVCC, I will need to check. But I think it is related to the change in version 1.3.172, "Issue 389: When there is a multi-column primary key, H2 does not seem to always pick the right index". This change tries to use the index that best matches the sort order. However, in this case it uses the (internal) primary key instead of the scan index, and because of that it picks the wrong plan. I think the bug is in BaseIndex.getCostRangeIndex, after the comment "if the ORDER BY clause matches the ordering of this index,". Here it tries to check which one is the best index for the sort order, but it compares the column indexes of the select statement (sort by the first column of the select statement) with the column index of the index (index by column x of the table). I will try to create a few test cases and then fix the bug. Regards, Thomas On Fri, Aug 16, 2013 at 12:36 PM, Noel Grandin <[email protected]>wrote: > I have traced the problem down to this chunk of code in > org.command.dml.Select#prepare() : > > if (sort != null && !isQuickAggregateQuery && !isGroupQuery) { > Index index = getSortIndex(); > if (index != null) { > Index current = topTableFilter.getIndex(); > if (current.getIndexType().isScan() || current == index) { > topTableFilter.setIndex(index); > if (!topTableFilter.hasInComparisons()) { > // in(select ...) and in(1,2,3) my return the key > in another order > sortUsingIndex = true; > } > } else if (index.getIndexColumns().length >= > current.getIndexColumns().length) { > IndexColumn[] sortColumns = index.getIndexColumns(); > IndexColumn[] currentColumns = > current.getIndexColumns(); > boolean swapIndex = false; > for (int i = 0; i < currentColumns.length; i++) { > if (sortColumns[i].column != > currentColumns[i].column) { > swapIndex = false; > break; > } > if (sortColumns[i].sortType != > currentColumns[i].sortType) { > swapIndex = true; > } > } > if (swapIndex) { > topTableFilter.setIndex(index); > sortUsingIndex = true; > } > } > } > } > > In version 171, we set "sortUsingIndex" to true because current is a > PageDataIndex ie. isScan() returns true > In version 173, "current" is a MultiVersionIndex which has a > PageDelegateIndex as it's base, so isScan() returns false. > > Something in our optimiser changed, and it's generating different plans. > > I'll only be able to look more at this next week. > > > On 2013-08-16 06:42, Max Sidnin wrote: > > Hi, > "analyze" before select doesn't affect the result: 171 ~ 70ms, 173 ~ 3200ms > "explain analyze" shows that version 173 has scanCount=250001 (entire > table) against 171 scanCount=1600 (offset+limit) > * > 1.3.171* > > EXPLAIN SELECT DISTINCT NAME FROM T1 WHERE STATUS = 0 ORDER BY NAME ASC > LIMIT 400 OFFSET 1200 > --------------------------------------------------- > SELECT DISTINCT > NAME > FROM PUBLIC.T1 > /* PUBLIC.IDX_T1_NAME */ > WHERE STATUS = 0 > ORDER BY 1 > LIMIT 400 OFFSET 1200 > /* index sorted */ > --------------------------------------------------- > > > EXPLAINE ANALYZE SELECT DISTINCT NAME FROM T1 WHERE STATUS = 0 ORDER BY > NAME ASC LIMIT 400 OFFSET 1200 > --------------------------------------------------- > SELECT DISTINCT > NAME > FROM PUBLIC.T1 > /* PUBLIC.IDX_T1_NAME */ > /* scanCount: 1600 */ > WHERE STATUS = 0 > ORDER BY 1 > LIMIT 400 OFFSET 1200 > /* index sorted */ > /* > total: 7 > T1.IDX_T1_NAME read: 3 (42%) > T1.T1_DATA read: 4 (57%) > */ > --------------------------------------------------- > Total request time: 91 ms. > > > * > 1.3.173 > * > EXPLAIN SELECT DISTINCT NAME FROM T1 WHERE STATUS = 0 ORDER BY NAME ASC > LIMIT 400 OFFSET 1200 > --------------------------------------------------- > SELECT DISTINCT > NAME > FROM PUBLIC.T1 > /* PUBLIC.PRIMARY_KEY_A */ > WHERE STATUS = 0 > ORDER BY 1 > LIMIT 400 OFFSET 1200 > --------------------------------------------------- > > > EXPLAINE ANALYZE SELECT DISTINCT NAME FROM T1 WHERE STATUS = 0 ORDER BY > NAME ASC LIMIT 400 OFFSET 1200 > --------------------------------------------------- > SELECT DISTINCT > NAME > FROM PUBLIC.T1 > /* PUBLIC.PRIMARY_KEY_A */ > /* scanCount: 250001 */ > WHERE STATUS = 0 > ORDER BY 1 > LIMIT 400 OFFSET 1200 > /* > total: 517 > T1.T1_DATA read: 517 (100%) > */ > --------------------------------------------------- > Total request time: 3348 ms. > > > Regards, > Max Sidnin > -- > 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 [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/h2-database. > For more options, visit https://groups.google.com/groups/opt_out. > > > -- > 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 [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/h2-database. > For more options, visit https://groups.google.com/groups/opt_out. > -- 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.
