Postgres: Is it necessary to add an index for a "where is null" select query?

Question

Given a table people e.g.:


id  name  emailed_at
1   Foo   2018-01-01
2   Bar   NULL

Is it necessary to add an index to emailed_at to have a performant SELECT * FROM people WHERE emailed_at IS NULL query?

UPDATE: The actual table has around 12 columns, hundreds of thousands of rows, and only the most recent (~ 1 day old) records should have a NULL in that field.

An index is only as helpful as its selectivity. If most records have null value for emailed_at, the index is not going to help. If only a few nulls are present, the index is going to speed things up. — Sergey Kalinichenko
– Sergey Kalinichenko, Commented Apr 26, 2018 at 18:45
How many rows does the table have? What percentage of them have a null email? — The Impaler
– The Impaler, Commented Apr 26, 2018 at 19:11
Create the index then check the execution plan and you will know — user330315
– user330315, Commented Apr 26, 2018 at 19:34

Gordon Linoff · Accepted Answer · 2018-04-26 18:47:13Z

3

For two rows, there is no performance issue at all. For a large table, an index is going to help. IS NULL is the same as any other "equality" comparison. It can take advantage of an index.

The benefit of indexing is when the table spans multiple (many?) data pages. An index generally reduces the number of data pages that need to be processed. If a lot of the emailed_at records are NULL (such as 50% in your example), then any given page is probably going to have NULL values -- and the index is much less useful.

answered Apr 26, 2018 at 18:47

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

2 Comments

jemminger Over a year ago

Updated with more info. Sounds like an index will help?

Gordon Linoff Over a year ago

@jemminger . . . Yes, an index should be helpful in the case you describe.

Collectives™ on Stack Overflow

Postgres: Is it necessary to add an index for a "where is null" select query?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related