Hi Noel,
Thanks for your answer.
I will try to rewrite the query to the way you suggest. Unfortunately, due
to a little holiday I won't be able to verify until next week.. :)
Should I report a bug or feature request in some other place, or is this
thread sufficient for that?
Thanks,
Jan
Op vrijdag 3 mei 2013 16:09:28 UTC+2 schreef Noel Grandin het volgende:
>
>
> 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] <javascript:>.
> To post to this group, send email to [email protected]<javascript:>
> .
> 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.
