I've got a fairly simple query using a GROUP BY, but the query optimizer
won't pick the index I've created. Instead, it'll use the indexes for the
constraints on the table instead, which is much slower.
I've tested this on 1.4.189 and 1.4.190 with the following JDBC url:
jdbc:h2:c:/temp/database;AUTO_SERVER=TRUE;MODE=MSSQLServer;CACHE_SIZE=16384
I've attached an example script to replicate the issue
DROP TABLE TBL_A;
DROP TABLE TBL_B;
CREATE TABLE TBL_A
(
id IDENTITY PRIMARY KEY NOT NULL,
name VARCHAR_IGNORECASE(255) NOT NULL,
createDate TIMESTAMP DEFAULT NOW(),
createUser VARCHAR(100) NOT NULL,
comments VARCHAR(2000),
active BOOLEAN DEFAULT TRUE,
UNIQUE KEY TBL_A_UK (name),
);
CREATE TABLE TBL_B
(
id IDENTITY PRIMARY KEY NOT NULL,
tbl_a_id BIGINT NOT NULL,
name VARCHAR(50),
status VARCHAR(30) NOT NULL,
createDate TIMESTAMP DEFAULT NOW(),
createUser VARCHAR(100) NOT NULL,
UNIQUE KEY TBL_B_UK (tbl_a_id, createDate),
FOREIGN KEY (tbl_a_id) REFERENCES public.TBL_A(id) ON DELETE NO ACTION ON
UPDATE NO ACTION,
);
INSERT INTO TBL_A (name, createUser, comments)
SELECT 'package_' || CAST(X as VARCHAR), 'testUser', 'testComment'
FROM SYSTEM_RANGE(1, 100)
WHERE X <= 100;
INSERT INTO TBL_B (tbl_a_id, name, status, createUser, createDate)
SELECT CASE WHEN tbl_a_id = 0 THEN 1 ELSE tbl_a_id END, name, status,
createUser, createDate
FROM (
SELECT
ROUND((RAND() * 100)) AS tbl_a_id,
'item_' || CAST(X AS VARCHAR) as name,
'ok' as status,
'testUser2' as createUser,
DATEADD('SECOND', X, NOW()) as createDate
FROM SYSTEM_RANGE(1, 50000)
WHERE X < 50000
);
CREATE INDEX tbl_b_idx ON tbl_b(tbl_a_id, id);
ANALYZE;
-- Won't use any of the indexes created above. Uses the constraint keys
instead.
-- If you remove both the Foreign Key and the Unique Key on TBL_B, it'll use
the tbl_b_idx index and be much faster
--EXPLAIN ANALYZE
SELECT MAX(b.id) as id
FROM tbl_b b
JOIN tbl_a a ON b.tbl_a_id = a.id
GROUP BY b.tbl_a_id
HAVING a.active = true;
Rerun the script above with the following changes to TBL_B
CREATE TABLE TBL_B
(
id IDENTITY PRIMARY KEY NOT NULL,
tbl_a_id BIGINT NOT NULL,
name VARCHAR(50),
status VARCHAR(30) NOT NULL,
createDate TIMESTAMP DEFAULT NOW(),
createUser VARCHAR(100) NOT NULL,
-- Remove constraints so it chooses our index
--UNIQUE KEY TBL_B_UK (tbl_a_id, createDate),
--FOREIGN KEY (tbl_a_id) REFERENCES public.TBL_A(id) ON DELETE NO ACTION
ON UPDATE NO ACTION,
);
--
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/d/optout.
