CODEWITHSUNDEEP
postgresqlindexingnull
Erik Karlsson
Erik Karlsson

Reputation: 608

Is Postgres smart and using my is not null index in this query?

I have a index like this on my candidates and their first_name column:

CREATE INDEX ix_public_candidates_first_name_not_null 
ON public.candidates (first_name) 
WHERE first_name IS NOT NULL;

Is Postgres smart enough to know that an equal operator means it can't be null or am I just lucky that my "is not null" index is used in this query?

select * from public.candidates where first_name = 'Erik'

Analyze output:

Bitmap Heap Scan on candidates  (cost=57.46..8096.88 rows=2714 width=352) (actual time=1.481..18.847 rows=2460 loops=1)
  Recheck Cond: (first_name = 'Erik'::citext)
  Heap Blocks: exact=2256
  ->  Bitmap Index Scan on ix_public_candidates_first_name_not_null  (cost=0.00..56.78 rows=2714 width=0) (actual time=1.204..1.204 rows=2460 loops=1)
        Index Cond: (first_name = 'Erik'::citext)
Planning time: 0.785 ms
Execution time: 19.340 ms

Upvotes: 1

Views: 822

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247320

The PostgreSQL optimizer is not based on lucky guesses.

It can indeed infer that anything that matches an equality condition cannot be NULL; the proof is the execution plan you show.

Upvotes: 1

Related Questions