Postgres - date index for reading dates before or after (<, <=, >, >=)

Question

I am having trouble with my date index when my condition is either before or after a given date. Using operators (<, <=, >, >=).

My table looks like this

CREATE TABLE date_playground
(
  id          BIGSERIAL   NOT NULL,
  due_date    date        NOT NULL,

  primary key (id)
);

And my index is

CREATE INDEX date_playground_idx_due_date
ON date_playground (due_date);

When my query is

-- SAMPLE  1 
EXPLAIN ANALYZE SELECT
  *
FROM  date_playground
WHERE due_date = '2019-09-07' :: DATE;

This uses the index

QUERY PLAN
Index Scan using date_playground_idx_due_date on date_playground  (cost=0.42..279.73 rows=75 width=287) (actual time=0.043..0.071 rows=10 loops=1)
  Index Cond: (due_date = '2019-09-07'::date)
Planning Time: 0.636 ms
Execution Time: 0.140 ms

However when I use the stated operators - <, <=, >, >=

-- SAMPLE 2
EXPLAIN ANALYZE SELECT
  *
FROM  ln.amortization_schedule_active a
WHERE due_date <= '2019-09-07' :: DATE;

QUERY PLAN
Seq Scan on date_playground  (cost=0.00..35133.86 rows=396944 width=287) (actual time=0.032..443.417 rows=404839 loops=1)
  Filter: (due_date <= '2019-09-07'::date)
  Rows Removed by Filter: 331870
Planning Time: 0.437 ms
Execution Time: 483.891 ms

It never uses the index, it always performs a full scan.

Can anyone suggest how can I optimize the second query?

Thanks!

This is not full scan, this is range scan, equal operator will do a unique scan — Ed Bangga
– Ed Bangga, Commented Nov 13, 2019 at 6:19
@ϻᴇᴛᴀʟ so if that's the case, this is expected? Is there some way that I could improve the second query? — Aaron
– Aaron, Commented Nov 13, 2019 at 6:24
yes this is expected behavior, you might check this link dba.stackexchange.com/questions/236970/… — Ed Bangga
– Ed Bangga, Commented Nov 13, 2019 at 6:29
Try running vacuum again and if postgres sticks to using the same plan as you've mentioned for the second query, then postgres is already choosing the best plan. You can observe that by forcing postgres to use index scan by disabling sequential scan. See postgresql.org/docs/11/runtime-config-query.html — Rahul Sharma
– Rahul Sharma, Commented Nov 13, 2019 at 6:31
The second query returns about half of the rows of the table. A index lookup would be slower than a Seq Scan — user330315
– user330315, Commented Nov 13, 2019 at 6:41

Laurenz Albe · Accepted Answer · 2019-11-13 07:23:33Z

3

PostgreSQL could use the index in your second query, but it correctly chooses not to do that because it would be inefficient.

For a query that selects half the rows in the table, a sequential scan is more efficient than an index scan: The index scan would have to visit most of the table blocks anyway, so why bother with random I/O or building a bitmap?

answered Nov 13, 2019 at 7:23

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

1 Comment

Aaron Over a year ago

No I understand, I tried changing the date (close to MIN(due_date)) and it used the index.

Collectives™ on Stack Overflow

Postgres - date index for reading dates before or after (<, <=, >, >=)

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