CODEWITHSUNDEEP
sqlpostgresqlindexing
narmaps
narmaps

Reputation: 413

Fast retrieval of records with NULL in postgres

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?

Upvotes: 5

Views: 2841

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247300

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.

Upvotes: 3

Related Questions