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 On Wednesday, December 5, 2012 7:13:08 PM UTC+4, martin wrote: > > 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/-/CYHUHl7PeRMJ. 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.
