Add an index on a new column

Question

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?)

Answer 1

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.