Postgresql – Possible to use a field within a query in PostgreSQL

postgresqlpostgresql-9.4

I want to query all events that are going on right now. Each events record has at and minutes fields for the time range of the event. I might query the events table like this:

SELECT * FROM events WHERE at >= NOW() - INTERVAL '2 hours' AND at <= NOW()

This assumes the event is 2 hours long. But I don't know that. The actual length is stored in the minutes field. How can I use that field in my query? Or is there a better mechanism for me? I've unsuccessfully tried things like this:

SELECT * FROM events WHERE at >= NOW() - INTERVAL minutes ' minutes' AND at <= NOW()

One little caveat is that performance does matter, somewhat. There is a large number of events in our database and that is growing fast.

Best Answer

You can use:

WHERE at >= NOW() - minute * INTERVAL '1 minute' AND at <= NOW()

The Postgres documentation page has similar examples: Date/Time Functions and Operators

The above is not going to be very efficient but you could add an index on at + minute * INTERVAL '1 minute' and then modify the query condition:

WHERE at <= NOW() AND (at + minute * INTERVAL '1 minute') >= NOW()

But it would probably be better if you used a gist index on the range (from when the event starts until it finishes), with something like this:

CREATE INDEX event_start_end_idx ON events 
  USING gist (tsrange(at, at + minute * INTERVAL '1 minute'));

and then use the "contains" (@>) range operator:

WHERE tsrange(at, at + minute * INTERVAL '1 minute'))  @>  NOW()

Related Solutions

Postgresql – Optimizing a query on a large table (EXPLAIN ANALYZE result inside)

A few thoughts. Consider range partitioning on "timestamp", it will reduce the amount of work your query have to do. Further optimization might be to calculate and store the agg for "closed" partitions. You will of course have to recalculate this when you modify historical information.

As a bonus it will be much easier to roll-out historical data that is no longer needed.

As mentioned this is just some thoughts and reflections, it may or it may not fit your situation. One pitfall may be the number of different intervals that you use for reporting. If larger intervals is not multiples of the partitioning interval this wont work well.

Edit: How is the plan affected by creating an temp table and an index on "timestamp"?

create index X on market_trades ( timestamp );

create temp table T ( time_range tsrange );

insert into T ( time_range )
WITH vals AS (
    SELECT '2013-08-19 0:00'::timestamp  AS frame_start,
       '2013-08-26 0:00'::timestamp  AS frame_end,
       '17h'::interval               AS interval_length
),   intervals AS (
    SELECT tsrange(start_time,
               lead(start_time, 1, frame_end) OVER (ORDER BY start_time   NULLS FIRST)) AS time_range
    FROM (
        SELECT generate_series(frame_start, frame_end, interval_length) AS  
               start_time, frame_end
        FROM vals
    ) _
    WHERE start_time < frame_end
)
SELECT time_range
FROM intervals i;

create index x on T ( time_range );

analyze t;

SELECT time_range, count(td.id) AS agg
FROM T
LEFT JOIN market_trades td
    ON td.timestamp <@ T.time_range
GROUP  BY T.time_range
ORDER  BY T.time_range;

PostgreSQL – How to Check for Contiguous Date Intervals

First, we combine intervals that overlap to find all the contiguous "islands" of the intervals:

with c as
  ( select *, max(end_date) over (order by start_date
                                  rows between unbounded preceding
                                           and 1 preceding)
                as previous_max
    from my_table
  )
select start_date, 
       coalesce(lead(previous_max) over (order by start_date),
                (select max(end_date) from my_table)
               ) as end_date
from c 
where previous_max < start_date 
   or previous_max is null ;

After that, it's easy to check if a given interval in completely surrounded by one of the found contiguous islands.

   with c as
  ( select *, max(end_date) over (order by start_date
                                  rows between unbounded preceding
                                           and 1 preceding)
                as previous_max
    from my_table
  ) ,
cont as
  ( select start_date, 
           coalesce(lead(previous_max) over (order by start_date),
                    (select max(end_date) from my_table)
                   ) as end_date
    from c 
    where previous_max < start_date 
       or previous_max is null  
   )
select *
from cont
where tsrange(start_date, end_date)
       @>                             -- contains
      tsrange('2015-04-02 22:10:00', '2015-04-02 22:30:00') 
limit 1 ;

Test at SQLfiddle

Best Answer

Related Solutions

Postgresql – Optimizing a query on a large table (EXPLAIN ANALYZE result inside)

PostgreSQL – How to Check for Contiguous Date Intervals

Related Question