Re: using index or check in ALTER TABLE SET NOT NULL

On Tue, Nov 28, 2017 at 1:59 PM, Sergei Kornilov <sk@zsrv.org> wrote:
> I write patch to speed up ALTER TABLE SET NOT NULL by check existed check constraints or indexes. Huge phase 3 with
verifytable data will be skipped if table has valid check constraint cover "alteredfield IS NOT NULL" condition or by
SPIquery if found index with compatible condition or regular amsearchnulls index on processed field. 

Doing this based on the existence of a valid constraint which implies
that no nulls can be present seems like a good idea.  Doing it based
on an index scan doesn't necessarily seem like a good idea.  We have
no guarantee at all that the index scan will be faster than scanning
the table would have been, and a single table scan can do multiple
verification steps if, for example, multiple columns are set NOT NULL
at the same time.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company