Postgres greater than or null

user_928489 0
Viewed 766

I am trying to efficiently use an index for the greater than or null query.

Example of a query would be:

select * from table where date > '2020-01-01' or date is null

What index if any do I need that postgres will be able to do this efficiently. I tried doing an index with

create index date on table (date asc nulls last)

but it does not seem to work and the best I am able to get is two bitmaps scans (one for greater than and one for null).

postgresql sql
2 Answers

If you are able to rewrite your condition, you could replace the null value with a date that is guaranteed to be greater than the comparison value:

where coalesce(date, 'infinity') > date '2020-01-01'

Then create an index on that expression:

create index on the_table ( (coalesce(date, 'infinity')) )

See also PostgreSQL docs:

user_3303150

Does Postgres use the index correctly when you use union all?

select *
from table
where date > '2020-01-01' 
union all
select *
from table
where date is null;

The issue might be the inequality combined with the NULL comparison. If this is some sort of "end date", then you might consider using some far out future value such as 9999-01-01 or infinity.

user_11440350
Related