h2 uses wrong index #4161
Description
h2 uses the wrong index, if more than one index exists,
even if the other one is a perfect match to the where-condition.
the following example demonstrates this.
of course this is a simplified example, but in production in a very large table
this can make the difference between minutes and milliseconds.
Welcome to H2 Shell 2.3.232 (2024-08-11)
sql> CREATE TABLE tbl ( c1 INTEGER, c2 INTEGER, c3 INTEGER, c4 INTEGER, c5 INTEGER, c6 INTEGER, c7 INTEGER );
sql> CREATE INDEX idx1 ON tbl ( c1, c2, c3, c4, c5 );
# correct choice if only 1 index exists:
sql> EXPLAIN ANALYZE UPDATE tbl SET c6=6 WHERE c1=1 AND c2=2 AND c3=3 AND c4=4 AND c5=5;
PLAN
UPDATE "PUBLIC"."TBL"
/* PUBLIC.IDX1: C1 = 1
AND C2 = 2
AND C3 = 3
AND C4 = 4
AND C5 = 5
*/
/* scanCount: 1 */
SET
"C6" = 6
WHERE ("C1" = 1)
AND ("C2" = 2)
AND ("C3" = 3)
AND ("C4" = 4)
AND ("C5" = 5)
sql> CREATE INDEX idx2 ON tbl ( c1, c7 );
# wrong choice if 2 indices exist:
sql> EXPLAIN ANALYZE UPDATE tbl SET c6=6 WHERE c1=1 AND c2=2 AND c3=3 AND c4=4 AND c5=5;
PLAN
UPDATE "PUBLIC"."TBL"
/* PUBLIC.IDX2: C1 = 1 */
/* scanCount: 1 */
SET
"C6" = 6
WHERE ("C1" = 1)
AND ("C2" = 2)
AND ("C3" = 3)
AND ("C4" = 4)
AND ("C5" = 5)