Hi
I came across the following performance problem: when using an IN
clause with tuples, the optimizer always decides for a full table
scan, which is very slow. A reformulated query where the IN clause
only uses simple values instead of tuples runs very fast.
Here's how to reproduce:
-- v 1.2.147
DROP TABLE IF EXISTS TEST;
CREATE TABLE TEST (
ID1 INTEGER NOT NULL,
ID2 INTEGER NOT NULL,
PRIMARY KEY (ID1, ID2)
);
INSERT INTO TEST SELECT X, X * 2 FROM SYSTEM_RANGE (1, 1000000);
EXPLAIN ANALYZE SELECT * FROM TEST WHERE (ID1, ID2) IN ((5,10),
(30,60)); -- slow
EXPLAIN ANALYZE SELECT * FROM TEST WHERE (ID1) IN (5,30); -- fast
Execution plan of slow query:
SELECT TEST.ID1, TEST.ID2
FROM PUBLIC.TEST /* PUBLIC.TEST.tableScan */ /* scanCount: 1000001 */
WHERE (ID1, ID2) IN((5, 10), (30, 60))
/*
total: 9656
TEST.TEST_DATA read: 9656 (100%)
*/
Execution plan of fast query:
SELECT TEST.ID1, TEST.ID2
FROM PUBLIC.TEST /* PUBLIC.PRIMARY_KEY_273: ID1 IN(5, 30) */ /*
scanCount: 3 */
WHERE ID1 IN(5, 30)
Is this a known problem?
Thanks,
Remo
--
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.
