0

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
  • "doesn't use this index." contradicts "system is doing an index scan". – a_horse_with_no_name Jul 25 at 8:54
  • 2
    Please edit your question and add the execution plan generated using explain (analyze, buffers, format text) (not just a "simple" explain) as formatted text and make sure you prevent the indention of the plan: paste the text without changing it, then put ``` on the line before the plan and on a line after the plan. Please also include complete create index statements for all indexes as well. – a_horse_with_no_name Jul 25 at 8:54
  • 1
    An index on (status, time) might be better. – a_horse_with_no_name Jul 25 at 8:55
  • 1
    How many rows have time >= '2019-07-23'? How many have status = 'ok'? How many have both? – jjanes Jul 25 at 14:48
  • there are over 11.000.000 rows with time >= '2019-07-23' and about 10.000.000 rows with status = 'ok' – MichaelRazum Jul 25 at 17:48
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.

  • 1
    I don't think there is any benefit of having "desc" in the index definition. Also, adding "id" as a third column in the index would allow index-only scans and is probably worth doing. – jjanes Jul 25 at 14:50
  • I added this index but still, the SQL needs a lot. Not sure if there is a way to improve. – MichaelRazum Jul 25 at 17:50
  • Should i edit the answer according to @jjanes comment? (time, service_name, id) its a bit wierd, cause the question is also evolved. – n3ko Aug 1 at 1:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.