Re: feature idea: use index when checking for NULLs before SET NOT NULL

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