Hi, This problem should be fixed in the latest nightly build.
Regards, Thomas On Friday, August 16, 2013, Thomas Mueller wrote: > 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 > > -- 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.
