Hi,
With this table:
*TABLE_CATALOG**TABLE_SCHEMA**TABLE_NAME**COLUMN_NAME**ORDINAL_POSITION**
COLUMN_DEFAULT**IS_NULLABLE**DATA_TYPE**CHARACTER_MAXIMUM_LENGTH**
CHARACTER_OCTET_LENGTH**NUMERIC_PRECISION**NUMERIC_PRECISION_RADIX**
NUMERIC_SCALE**CHARACTER_SET_NAME**COLLATION_NAME**TYPE_NAME**NULLABLE**
IS_COMPUTED**SELECTIVITY**CHECK_CONSTRAINT**SEQUENCE_NAME**REMARKS**
SOURCE_DATA_TYPE*'TEST''PUBLIC'
<http://localhost/admin/sql/#>'OM'<http://localhost/admin/sql/#>
'B'1'0''NO'4101010100'Unicode''OFF''INTEGER'0false50''null''null'TEST'
'PUBLIC' <http://localhost/admin/sql/#>'OM' <http://localhost/admin/sql/#>
'PK'2null'YES'-5191919100'Unicode''OFF''BIGINT'1false50''null''null
and about 500,00 rows, the following queries on a 1Ghz CPU have run-times:
50 seconds:
SELECT pk FROM om WHERE pk IN (<BIGINT-LIST>)
AND b IN (1,2)
5 seconds:
SELECT pk FROM om WHERE pk IN (<BIGINT-LIST>)
AND b IN (1)
(or any value in place of 1)
< 0.1 seconds:
SELECT pk FROM om WHERE pk IN (<BIGINT-LIST>)
... where<BIGINT-LIST> is a constant/fixed list of ~40 longs, like:
1254619816788000000,1176099280681000000,(.... 3rd-40th)
`explain plan` for the first query returns:
'SELECT PK FROM PUBLIC.OM /* PUBLIC.OM_DATA */ WHERE (PK IN(<BINGINT-LIST>)
AND (B IN(1, 2))'
Both columns ('PK' and 'B') are indexed...
Is this a known optimization todo/issue?
Thank you,
Ken
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
