CODEWITHSUNDEEP
sqlpostgresql
Blaž Šnuderl
Blaž Šnuderl

Reputation: 378

Postgres greater than or null

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

Upvotes: 9

Views: 2790

Answers (2)

user330315
user330315

Reputation:

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:

Upvotes: 11

Gordon Linoff
Gordon Linoff

Reputation: 1269953

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.

Upvotes: 2

Related Questions