Fast retrieval of records with NULL in postgres

Question

I have a postgres database table with 2 million records and a script that selects rows based on a certain column being NULL. It then modifies the data, storing values in the NULL column and then selects the next batch of data to process. However, the following query that retrieves the rows with NULL values is extremely slow:

SELECT id, name, data_values FROM my_table WHERE data_values is NULL;

data_values is of type JSONB. data_values is not currently indexed, but my research suggests that postgres indexes do not store NULL values so this would not help.

Are there workarounds for speeding up the SELECT query? Store a boolean in indexed column? Filtered indices?

Since you haven’t described the modification process it leaves the question open: why not just run an update query where data_values is null. Also set an index on other fields, like id, should speed up all queries quite a lot. — Ethan
– Ethan, Commented Sep 28, 2020 at 20:52
Did you try a filtered index? create index on my_table (id, name) where data_values is null — user330315
– user330315, Commented Sep 28, 2020 at 20:55
I don't know what your research consisted of, but the default index method (btree) certainly stores NULLs. But a filtered index would probably be better. — jjanes
– jjanes, Commented Sep 29, 2020 at 1:14

Laurenz Albe · Accepted Answer · 2020-09-29 06:24:34Z

3

PostgreSQL stores NULL values in B-tree indexes, so the IS NULL predicate can use an index.

However, I would consider a B-tree index on a jsonb column only if the values are short – long index entries make an index inefficient, and beyond a certain, not too large, size you will get an error.

In this case, you could consider a partial index:

CREATE INDEX ON my_table (id) WHERE data_values IS NULL;

Such an index can be used as well, and it has the advantage of being smaller. Also, the index does not have to be modified if you modify a jsonb.

answered Sep 29, 2020 at 6:24

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Collectives™ on Stack Overflow

Fast retrieval of records with NULL in postgres

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