0

I have ~50 mil data entries, while most of them(maybe 80%) have null values in a column(currently I assigned a dummy value to those null entries). However, I'm doing frequent searching on non-null(regarding that column) entries.

I'm not sure what's the performance implication behind null values and indexing. (What I can do is to assign a dummy value for those 80% data if null values will hurt performance in my use case)

I guess null entries will be excluded from the normal index(put into some null index maybe), when I explicitly do searching on non-null values, this should be able to improve the performance since the index size is much smaller in my case

4
  • If you already "have null values in a column", what would "setting null" entail?
    – mustaccio
    Oct 6, 2021 at 18:35
  • @mustaccio sorry for the confusion, I've modified my question accordingly. By setting null, I mean I can either have those null values there, or I can give a dummy value for all those null entries
    – Ziqi Liu
    Oct 6, 2021 at 18:53
  • 1
    I guess you're looking for partial indexes. Also, this.
    – mustaccio
    Oct 6, 2021 at 20:55
  • Does this answer your question? Does PostgreSQL index null values?
    – John K. N.
    Oct 11, 2021 at 18:56

1 Answer 1

1

NULL values are stored in an index just like all other values.

If you want an index to speed up queries on the columns where the value is not NULL, you can use a partial index that will only index the rows that satisfy a condition:

CREATE INDEX ON tab (...) WHERE somecol IS NOT NULL;

The advantages are:

  • this will work with queries that have somecol IS NOT NULL in their WHERE condition and implicitly index that condition

  • the index is smaller

  • you don't have t pay the price for index maintenance if you modify a column where comecol is NULL

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.