Optimize PostgreSQL query timeseries with intervals

Question

I have a table with events with first_seen (datetime), last_seen (datetime) and severity (an integer).

I'm trying to find how many events were active in discreet 15 minute intervals:

WITH intervals AS
  (
      SELECT
        '2016-08-02 00:00:00'::TIMESTAMP
            + (n||' minutes')::INTERVAL AS start_time,
        '2016-08-02 00:00:00'::TIMESTAMP
            + ((n + 15)||' minutes')::INTERVAL AS end_time
      FROM generate_series(0, 24 * 60, 15) n
  )
SELECT
  start_time,
  (SELECT count(*) FROM event
     WHERE first_seen < end_time AND last_seen > start_time
       AND severity = 5) red,
  (SELECT count(*) FROM event
     WHERE first_seen < end_time AND last_seen > start_time
       AND severity = 4) orange,
  (SELECT count(*) FROM event
     WHERE first_seen < end_time AND last_seen > start_time
       AND severity = 3) yellow
FROM intervals;

I also have an index on (first_seen, last_seen, severity).
My problem is that it seems to be a be too slow.
The table has about 100 thousand rows, and to make 100 intervals takes 10 seconds. The index scan seems too slow.

Any ideas how to optimize this query?

Laurenz Albe · Accepted Answer · 2016-10-11 14:24:54Z

1

The best thing is to get rid of the subselects.

Try something like the following (untested, so it may contain errors):

WITH intervals AS
  (
      SELECT
        '2016-08-02 00:00:00'::TIMESTAMP
            + (n||' minutes')::INTERVAL AS start_time,
        '2016-08-02 00:00:00'::TIMESTAMP
            + ((n + 15)||' minutes')::INTERVAL AS end_time
      FROM generate_series(0, 24 * 60, 15) n
  )
SELECT
   start_time,
   sum(CASE WHEN severity = 5 THEN 1 ELSE 0 END) red,
   sum(CASE WHEN severity = 4 THEN 1 ELSE 0 END) orange,
   sum(CASE WHEN severity = 3 THEN 1 ELSE 0 END) yellow
FROM event
   RIGHT OUTER JOIN intervals
      ON first_seen < end_time AND last_seen > start_time
GROUP BY start_time;
ORDER BY start_time;

You may be able to speed up things by two indexes on first_seen and last_seen. A multicolumn index will not help.

edited Oct 11, 2016 at 14:24

answered Oct 11, 2016 at 11:35

Laurenz Albe

257k22 gold badges314 silver badges390 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

joop Over a year ago

Note: generate_series() works with timestamps, too:

WITH intervals AS (       SELECT gs AS start_time         , gs + '15 minutes' AS end_time         FROM generate_series('2016-08-02 00:00:00'::TIMESTAMP                         ,'2016-08-03 00:00:00'::TIMESTAMP                         , '15 minutes'::INTERVAL) gs         )

Laurenz Albe Over a year ago

Yes. I left that part the same to reduce potential confusion.

windravenii Over a year ago

Wow, that's 2 times faster. Thanks :D.

Laurenz Albe Over a year ago

I have added hints for potential indexes.

windravenii Over a year ago

I also needed the intervals that have sum 0 so I added a RIGHT JOIN instead of join. Also an ORDER BY start_time at the end.

|

Collectives™ on Stack Overflow

Optimize PostgreSQL query timeseries with intervals

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related