On 8 January 2016 at 13:13, Vitaly Burovoy <vitaly.buro...@gmail.com> wrote:
> On 1/8/16, Simon Riggs <si...@2ndquadrant.com> wrote: > > On 8 January 2016 at 12:49, Vitaly Burovoy <vitaly.buro...@gmail.com> > > wrote: > > > > > >> In Postgres9.1 a new feature was implemented [1] for adding PK and > >> UNIQUE constraints using indexes created concurrently, but constraints > >> NOT NULL and CHECK still require full seqscan of a table. New CHECK > >> constraint allows "NOT VALID" option but VALIDATE CONSTRAINT still > >> does seqscan (with RowExclusiveLock, but for big and constantly > >> updatable table it is still awful). > >> > >> It is possible to find wrong rows in a table without seqscan if there > >> is an index with a predicate allows to find such rows. There is no > >> sense what columns it has since it is enough to check whether > >> index_getnext for it returns NULL (table is OK) or any tuple (table > >> has wrong rows). > >> > > > > You avoid a full seqscan by creating an index which also does a full seq > > scan. > > > > How does this help? The lock and scan times are the same. > > I avoid not a full seqscan, but a time when table is under > ExclusiveLock: index can be build concurrently without locking table. That is exactly what ADD ...NOT VALID and VALIDATE already does, as of 9.4. -- Simon Riggs http://www.2ndQuadrant.com/ <http://www.2ndquadrant.com/> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
