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

Question

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

Your execution plan shows that the index is used, so what exactly is your question? — user330315
– user330315, Commented Sep 3, 2018 at 8:27
@a_horse_with_no_name I tried to find some documentation if this is the actual expected case, or if I to guarantee this behavior need to add a 'first_name is not null' check as well to the query? — Erik Karlsson
– Erik Karlsson, Commented Sep 3, 2018 at 8:45

Laurenz Albe · Accepted Answer · 2018-09-03 08:50:19Z

1

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.

answered Sep 3, 2018 at 8:50

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related