Creating index for both equality and timestamp range queries

Question

I have a table defined like this:

CREATE TABLE sessions (
    session_id BIGSERIAL PRIMARY KEY,
    session_start TIMESTAMP NOT NULL,
    session_end TIMESTAMP NOT NULL,
    site_id BIGINT NOT NULL,
    photo_id BIGINT NOT NULL,
    uuid TEXT NOT NULL
)

CREATE INDEX sessions_lookup ON sessions (
    site_id, photo_id, uuid, session_start, session_end
)

And I need to run queries of this type against it:

SELECT session_id
FROM sessions
WHERE site_id = %s
AND photo_id = %s
AND uuid = %s
AND %s <@ tsrange(
            session_start - interval '3 hours',
            session_end + interval '3 hours'
          )

There's different variations of the range query part (check seperately for both session_start and session_end, use OVERLAPS, etc. but as far as I have tested they all do the same thing).

Using EXPLAIN ANALYZE I get the following results (using actual values instead of placeholders):

 Index Scan using sessions_lookup on sessions  (cost=0.00..8.30 rows=1 width=8) (actual time=0.062..0.063 rows=1 loops=1)
   Index Cond: ((site_id = 10113150) AND (photo_id = 10240980) AND (uuid = '042d6f26-e298-0140-a4cc-7bfd0f9ccd27'::text))
   Filter: (((session_start - '03:00:00'::interval) <= '2014-09-05 09:45:38'::timestamp without time zone) AND ((session_end + '03:00:00'::interval) >= '2014-09-05 09:45:38'::timestamp without time zone))
 Total runtime: 1.384 ms
(4 rows)

Subsequent runs give ~0.080ms query runtime.

Ideally, I would like to use the index as part of the timestamp lookup, but it seems to be completely ignored (same result with or without it in the index). Do I need to alter the order of fields or am I doing something wrong (do I need a different type of index)?

This is tested on a table with 45k records, but if need be I can create a bigger sample set.

I don't think that <@ is btree-indexable, and even if there was a suitable GiST index here I don't think a bitmap index scan is (a) possible in this case or (b) likely to help performance wise anyway. Though it's interesting that <@ is being decomposed into range range predicates that are potentially b-tree indexable; I wasn't aware that was actually possible. — Craig Ringer
– Craig Ringer, Commented Sep 25, 2014 at 12:14

Community · Accepted Answer · 2017-05-23 10:33:23Z

1

First thoughts: the OVERLAPS operator is working like the overlap (&&) range operator. The containment (<@ and variants) operator is a little different, but if you want to test whether a single time-point is within a (date-)time range, they work similar.

The reason behind your query can't use your index is, that you didn't test your columns directly within your query. You test a range expression, which is created by using your columns. In these cases, you can usually set up an expression based index, but in your case, that won't help either (already mentioned by @CraigRinger's comment).

If we can forget a bit about ranges and overlapping, here it is your WHERE clause:

(session_start - interval '3 hours' <= '2014-09-05 09:45:38') AND
(session_end + interval '3 hours' >= '2014-09-05 09:45:38')

Let's do a little math:

(session_start <= '2014-09-05 09:45:38'::timestamp + interval '3 hours') AND
(session_end >= '2014-09-05 09:45:38'::timestamp - interval '3 hours')

This will now use your index.

With parameters:

SELECT session_id
FROM sessions
WHERE site_id = %s
AND photo_id = %s
AND uuid = %s
AND (session_start <= %s::timestamp + interval '3 hours')
AND (session_end >= %s::timestamp - interval '3 hours')

SQLFiddle

edited May 23, 2017 at 10:33

CommunityBot

11 silver badge

answered Sep 25, 2014 at 18:47

pozs

36.6k5 gold badges61 silver badges66 bronze badges

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

1 Comment

Christian P. Over a year ago

I did some tests and came to the same conclusion - I basically have to use the raw column values and manipulate the given timestamp to fit instead for good performance (which is not an issue, just have to do it client-side instead of in SQL). As an added bonus I can add session_id to the end for an Index Only Scan, which is still only 1/7th of the table size despite containing a lot of fields. It all counts :)

Collectives™ on Stack Overflow

Creating index for both equality and timestamp range queries

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related