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
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/h2-database/-/z6NGe55ZjfIJ.
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.
