I have a simple table with the following columns:
- id (int4)
- time (timestamp)
- name (varchar)
- service_name (varchar)
For every service (there are not more than 500 different names), there are a lot of entries. The status varies, but basically only 'ok' is interesting.
I tried to optimize this query:
SELECT service_name, max(id) AS max_id FROM mytable where time >= '2019-07-23' and status = 'ok' GROUP BY service_name;
There exists already an index on the time column. I tried to add another one using the statement:
create index my_index on my_table(time desc, service_name, status);
But that didn't help. PostgreSQL (9.6) doesn't use this index. As I can see the database management system is doing an index scan that consumes most of the time.
Any Idea how I could improve the performance? Thanks a lot! Right now the query needs more than four seconds. That is a bit too much.
Some numbers: There are currently more than 500 million rows in the table.
PPS: Added execution plan
HashAggregate (cost=221449.30..221453.55 rows=425 width=24) (actual time=21852.251..21852.330 rows=409 loops=1) Group Key: service_name Buffers: shared hit=4202089 read=70842 -> Index Only Scan using my_table_time_service_name on my_table hh (cost=0.70..187312.22 rows=6827415 width=24) (actual time=0.044..13948.546 rows=11192087 loops=1) Index Cond: ("time" >= '2019-07-23 20:58:52.886332'::timestamp without time zone) Heap Fetches: 4139020 Buffers: shared hit=4202089 read=70842 Planning time: 0.233 ms Execution time: 21852.411 ms