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.
