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.
