why does't optimizer use composite index?

[martin]

I have a simple table with 3 indexes:

create table metric_data
(
  metric_instance_id bigint,
  timestamp long,
  value double,
  foreign key (metric_instance_id) references metric_instance(metric_instance_id) on delete cascade
);

create index on metric_data(metric_instance_id);
create index on metric_data(timestamp);
create index on metric_data(metric_instance_id, timestamp);

I'm seeing the following result from explain analyze.  Why doesn't the composite index get used for this query?  Is there anyway I can get it to?  I have run ANALYZE on the db.  There are millions of rows in the table.

SELECT
    MIN(TIMESTAMP) AS MIND
FROM PUBLIC.METRIC_DATA MD
    /* PUBLIC.CONSTRAINT_INDEX_1A: METRIC_INSTANCE_ID = 610 */
    /* scanCount: 201350 */
WHERE MD.METRIC_INSTANCE_ID = 610
/*
total: 101689
METRIC_DATA.CONSTRAINT_INDEX_1A read: 1278 (1%)
METRIC_DATA.METRIC_DATA_DATA read: 100411 (98%)
*/

Thanks,
Martin

[Noel Grandin]

H2 isn't smart enough to do index-only scans, particular for aggregate
queries.

Patches are welcome :-)

[Sergi Vladykin]

Hi,

You could try to use

SELECT TIMESTAMP AS MIND
FROM PUBLIC.METRIC_DATA MD
WHERE MD.METRIC_INSTANCE_ID = 610
ORDER BY METRIC_INSTANCE_ID, TIMESTAMP
LIMIT 1

It should do what you want.

Sergi