1

I'm adding a new column (bigints, initial values all null), to a large (~5 billion rows) Postgres (9.6) table.

I want to add an index on the row (algorithm: concurrently), and I'm wondering if there's a way I can save it the time of scanning through all five billion null fields to add them to the index, since I'm adding the index when the column is brand new and has no data. Is there any kind of optimization for this, or should I just let it run? (Is it even theoretically possible for there to be an optimization, or does it need to add something to the index even for null fields?)

2

No, sorry.

PostgreSQL generally indexes NULL fields, so would it would have to add an entry for each row. You can make an index not index NULLs (using a partial index, or using a non-default type of index which doesn't index NULLs), but it is not implemented for such an index to observe an all-NULL constraint on the column and so take a shortcut of not scanning the table. It would uselessly scan the table looking for not-NULLs anyway. Interesting idea though.

| improve this answer | |
  • 1
    Nice. A partial index with WHERE (new_column IS NOT NULL) will of course be created much faster than a full index, as it'll write almost nothing. – ypercubeᵀᴹ Apr 11 '19 at 21:28

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.