I would expect this variant of your query to have the best chance of
using the index:
SELECT * FROM TestTable where (FirstCol,SecCol) IN ( ('VAL_X_400',
'VAL_Y_2000'), ('VAL_X_400', 'VAL_Y_3000') )
But digging around the code in
ConditionInConstantSet#createIndexConditions
it looks like our support for using composite indexes in complicated
queries is not good.
This is beyond my ability to fix - it would require surgery in
IndexCondition and other places to cope with composite indexes.
But for now, if you reformulate like this:
SELECT * FROM TestTable where (FirstCol = 'VAL_X_400' AND SecCol =
'VAL_Y_2000')
UNION
SELECT * FROM TestTable where (FirstCol = 'VAL_X_400' AND SecCol =
'VAL_Y_3000')
you should get decent performance.
On 2013-05-02 10:50, Jan Hoeve wrote:
Hi,
I am trying to improve performance of SQL queries on a H2 database.
One query remains slow though: a select with a IN clause on a
composite primary key.
Testscript to create/fill/select:
DROP TABLE IF EXISTS TestTable;
CREATE TABLE IF NOT EXISTS TestTable (
FirstCol VARCHAR(2048) NOT NULL ,
SecCol VARCHAR(2048) NOT NULL ,
PRIMARY KEY (FirstCol, SecCol));
INSERT INTO TestTable select CONCAT('VAL_X_',X) AS XX,
'VAL_Y_2000' AS YY from SYSTEM_RANGE(1, 2000);
INSERT INTO TestTable select CONCAT('VAL_X_',X) AS XX,
'VAL_Y_4000' AS YY from SYSTEM_RANGE(2001, 4000);
INSERT INTO TestTable select CONCAT('VAL_X_',X) AS XX,
'VAL_Y_6000' AS YY from SYSTEM_RANGE(4001, 6000);
INSERT INTO TestTable select 'VAL_X_400' AS XX, CONCAT('VAL_Y_',X)
AS YY from SYSTEM_RANGE(1, 1980);
explain analyze SELECT * FROM TestTable where FirstCol =
'VAL_X_400' AND SecCol IN ('VAL_Y_2000', 'VAL_Y_3000')
The query plan is:
PLAN<http://127.0.1.1:8082/query.do?jsessionid=ea09b5232afea7d0e97720dfdc206a44#>
SELECT
TESTTABLE.FIRSTCOL,
TESTTABLE.SECCOL
FROM PUBLIC.TESTTABLE
/* PUBLIC.PRIMARY_KEY_60E: SECCOL IN('VAL_Y_2000', 'VAL_Y_3000')
AND FIRSTCOL = 'VAL_X_400'
*/
/* scanCount: 1982 */
WHERE (SECCOL IN('VAL_Y_2000', 'VAL_Y_3000'))
AND (FIRSTCOL = 'VAL_X_400')
Notice the large scanCount.
Experiments:
* when changing the test query to "SELECT * FROM TestTable where
FirstCol = 'VAL_X_400' AND SecCol IN ('VAL_Y_2000')", the
scanCount drops to 2.
* when changing the test query to "SELECT * FROM TestTable where
SecCol IN ('VAL_Y_2000', 'VAL_Y_3000')" the scanCount drops to 1000.
Thinking about this query, the scanCount should be 2, because I can
rewrite the query to the following form which should use the primary
key index:
SELECT * FROM TestTable where (FirstCol = 'VAL_X_400' AND SecCol =
'VAL_Y_2000') OR (FirstCol = 'VAL_X_400' AND SecCol = 'VAL_Y_3000')
But in that case the scanCount increases to a whopping 7981.
Can anyone please point out what I am doing wrong here?
Thanks, Jan
--
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?hl=en.
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
