[h2] Poor performance

[James Sheets]

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 h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.