Is there any way to index the following query?
SELECT run_id, MAX ( frame ) , MAX ( time ) FROM run.frames_stat GROUP BY run_id;
I've tried creating sorted (non-composite) indexes on
time, and an index on
run_id, but the query planner doesn't use them.
- Unfortunately (and for reasons I won't get into) I cannot change the query
frames_stattable has 42 million rows
- The table is unchanging (no further inserts/deletes will ever take place)
- The query was always slow, it's just gotten slower because this dataset is larger than in the past.
- There are no indexes on the table
- We are using Postgres 9.4
- The db's "work_mem" size is 128MB (if that's relevant).
- Hardware: 130GB Ram, 10 core Xeon
CREATE TABLE run.frame_stat ( id bigint NOT NULL, run_id bigint NOT NULL, frame bigint NOT NULL, heap_size bigint NOT NULL, "time" timestamp without time zone NOT NULL, CONSTRAINT frame_stat_pkey PRIMARY KEY (id) )
HashAggregate (cost=1086240.000..1086242.800 rows=280 width=24) (actual time=14182.426..14182.545 rows=280 loops=1) Group Key: run_id -> Seq Scan on zulu (cost=0.000..770880.000 rows=42048000 width=24) (actual time=0.037..4077.182 rows=42048000 loops=1)