Postgres greater than or null

Question

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).

Answer 1

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:

• Date/Time Types, 8.5.1.4. Special Values for infinity value
• Conditional Expressions, 9.18.2. COALESCE for coalesce function

Answer 2

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.