Postgres indexing for timestamp range does not work

Question

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

Please **[edit]** your question and add the [execution plan](https://www.postgresql.org/docs/current/static/using-explain.html) generated using **`explain (analyze, buffers, format text)`** (_not_ just a "simple" explain) as [formatted text](http://stackoverflow.com/help/formatting) and make sure you preserve the indention of the plan. Paste the text, 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. — , Jun 13 '22 at 11:30
Yeah try using the `between` syntax and compare the performance, it should be resolve this. Let us know whether it worked — Sridhar Cr, Jun 13 '22 at 12:23
@SridharCr I can not use `between` because I need to compare `start_date` and `end_date` with different dates — Vladislav Kiper, Jun 13 '22 at 12:43
These are very different queries. The one that doesn't use an index (fortunately!) has to find 1971694 record, and skips just 28307. It would be a lot of work to use an index for this. — Frank Heikens, Jun 13 '22 at 12:45

score 1 · Answer 1 · answered Jun 13 '22 at 15:07

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.

Postgres indexing for timestamp range does not work

1 Answers1