On Fri, May 29, 2020 at 09:53:14PM -0400, John Bachir wrote: > Hi Sergei - I just used the recipe on my production database. I didn't > observe all the expected benefits, I wonder if there were confounding factors > or if I did something wrong. If you have time, I'd love to get your feedback. > Let me know if you need more info. I'd love to write a blog post informing > the world about this potentially game-changing feature!
If you do it right, you can see a DEBUG: postgres=# CREATE TABLE tn (i int); postgres=# ALTER TABLE tn ADD CONSTRAINT nn CHECK (i IS NOT NULL) NOT VALID; postgres=# ALTER TABLE tn VALIDATE CONSTRAINT nn; postgres=# SET client_min_messages=debug; postgres=# ALTER TABLE tn ALTER i SET NOT NULL ; DEBUG: existing constraints on column "tn"."i" are sufficient to prove that it does not contain nulls > SLOW (table scan speed) - didn't have timing on, but I think about same time > as the next one. > ALTER TABLE my_table ALTER COLUMN column1 SET NOT NULL; > > 01:39 SLOW (table scan speed) > ALTER TABLE my_table ALTER COLUMN column2 SET NOT NULL; > > 00:22 - 1/4 time of table scan but still not instant like expected > ALTER TABLE my_table ALTER COLUMN column3 SET NOT NULL; > > 20.403 ms - instant, like expected > ALTER TABLE my_table ALTER COLUMN column4 SET NOT NULL; That the duration decreased every time may have been due to caching? How big is the table vs RAM ? Do you know if the SET NOT NULL blocked or not ? Maybe something else had a nontrivial lock on the table, and those commands were waiting on lock. If you "SET deadlock_timeout='1'; SET log_lock_waits=on;", then you could see that. -- Justin
