Optimization for table with text and time columns

Question

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.

PS

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

Answer 1

If the only reason for the index is the above query, then a partial index would better:

CREATE INDEX my_index ON my_table(time desc, service_name) WHERE status='ok';

VACUUM ANALYZE my_table; may help too.

The query plan depends on actual data in the table. In a table with few or zero records a seq scan is cheaper.

If you really dont want the seq scan (and nothin else helps), try to turn off seqscans with SET enable_seqscan = off; before the query.