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 generated using explain (analyze, buffers, format text) (not just a "simple" explain) as formatted text 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. — user330315
– user330315, Commented Jun 13, 2022 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
– Sridhar Cr, Commented Jun 13, 2022 at 12:23
@SridharCr I can not use between because I need to compare start_date and end_date with different dates — Vladislav Kiper
– Vladislav Kiper, Commented Jun 13, 2022 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
– Frank Heikens, Commented Jun 13, 2022 at 12:45

Laurenz Albe · Accepted Answer · 2022-06-13 15:07:44Z

1

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.

answered Jun 13, 2022 at 15:07

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Collectives™ on Stack Overflow

Postgres indexing for timestamp range does not work

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related