CODEWITHSUNDEEP
postgresqlindexingsql-timestamp
Vladislav Kiper
Vladislav Kiper

Reputation: 11

Postgres indexing for timestamp range does not work

I have a following table with 1.000.000 rows

create table event
(
    id         serial
        constraint event_pk
            primary key,
    type       text      not null,
    start_date timestamp not null,
    end_date   timestamp not null,
    val        text
);

and I need to execute a following SQL query

EXPLAIN (analyse, buffers, format text)
SELECT *
from event
WHERE end_date >= '2010-01-12T18:00:00'::timestamp
  AND start_date <= '2010-01-13T00:00:00'::timestamp;

Please note that end_date in being compared with date which is eariler than that for start_date
The question is what index should I create for such query?
I've tried following one:

create index my_index
    on event (end_date, start_date desc);

But it doesn't work, I can see that sequential search is being used

Seq Scan on event  (cost=0.00..53040.01 rows=1967249 width=57) (actual time=0.142..149.163 rows=1971694 loops=1)
  Filter: ((end_date >= '2010-01-12 18:00:00'::timestamp without time zone) AND (start_date <= '2010-01-13 00:00:00'::timestamp without time zone))
  Rows Removed by Filter: 28307
  Buffers: shared hit=15762 read=7278
Planning:
  Buffers: shared hit=4
Planning Time: 0.127 ms
Execution Time: 201.610 ms

I can not understand why my index is not working, because if we just try following index and query:

create index simple
    on event (start_date, end_date DESC);


SELECT *
from event
WHERE event.start_date >= '2010-01-12T18:00:00'::timestamp
  AND event.end_date <= '2011-01-13T00:00:00'::timestamp;

indexing works just fine

    Bitmap Heap Scan on event  (cost=466.91..23418.68 rows=18035 width=57) (actual time=1.954..8.551 rows=15944 loops=1)
  Recheck Cond: ((start_date >= '2010-01-12 00:00:00'::timestamp without time zone) AND (end_date <= '2011-01-13 00:00:00'::timestamp without time zone))
  Heap Blocks: exact=7694
  Buffers: shared hit=7734 read=26
  ->  Bitmap Index Scan on simple  (cost=0.00..462.40 rows=18035 width=0) (actual time=1.314..1.314 rows=15944 loops=1)
        Index Cond: ((start_date >= '2010-01-12 00:00:00'::timestamp without time zone) AND (end_date <= '2011-01-13 00:00:00'::timestamp without time zone))
        Buffers: shared hit=55 read=11
Planning:
  Buffers: shared hit=8
Planning Time: 0.133 ms
Execution Time: 9.025 ms

This query does not do what I need, but I'm just wondering now why this index works for such query, but my index above does not work for query which I need

Upvotes: 1

Views: 560

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246533

The answer is right there in the execution plan:

Seq Scan on event  (...) (actual ... rows=1971694 ...)
  Filter: ((end_date >= '2010-01-12 18:00:00'::timestamp without time zone) AND (start_date <= '2010-01-13 00:00:00'::timestamp without time zone))
  Rows Removed by Filter: 28307

the query returns almost two million rows, and the filter only removes 30000. It is more efficient to use a sequential scan than an index scan in that case.

Upvotes: 1

Related Questions