Not Null Constraint vs Query Planning
If I have a NOT NULL constraint on a column, and then run a query where
that column IS NULL, does the optimizer "short-circuit" the query to return
0 rows right away?
If so, is there a way to see that it is doing so? I've been running a few
explain plans this morning and they all look the same.
Here I create a table in PG10 with 10 million dummy rows, no indexes,
vacuum/analyzed. I then query before and after and don't notice much
difference. Wondering if there's any way to see an indication that the
constraint was used in the query planning. My sample runs are at the end of
this email.
The REAL reason for this is that I'm wondering if I created a NOT NULL
check constraint with "NOT VALID" would that then NOT be considered in such
a "short-circuit" case until I ran the VALIDATE CONSTRAINT on it? Perhaps I
should have just asked this in its own thread but I started diving into the
query plan thing.
postgres=# explain (analyze, buffers) select name from people where
created_at is null;
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Seq Scan on people (cost=0.00..203093.21 rows=1 width=33) (actual
time=5365.886..5365.886 rows=0 loops=1)
Filter: (created_at IS NULL)
Rows Removed by Filter: 10000000
Buffers: shared hit=12828 read=90265
Planning time: 2.558 ms
Execution time: 5379.862 ms
(6 rows)
postgres=# alter table people alter column created_at set not null;
ALTER TABLE
postgres=# vacuum analyze people;
VACUUM
postgres=# explain (analyze, buffers) select name from people where
created_at is null;
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Seq Scan on people (cost=0.00..203092.49 rows=1 width=33) (actual
time=2339.254..2339.254 rows=0 loops=1)
Filter: (created_at IS NULL)
Rows Removed by Filter: 10000000
Buffers: shared hit=12938 read=90155
Planning time: 0.390 ms
Execution time: 2339.274 ms
(6 rows)
--
Don Seiler
www.seiler.us
On 02/03/2020 18:09, Don Seiler wrote:
The REAL reason for this is that I'm wondering if I created a NOT NULL
check constraint with "NOT VALID" would that then NOT be considered in such
a "short-circuit" case until I ran the VALIDATE CONSTRAINT on it? Perhaps I
should have just asked this in its own thread but I started diving into the
query plan thing.
You cannot do this because NOT NULL isn't a real constraint (meaning it
does not appear in pg_constraint). There have been several attempts to
make it a real constraint over the years but so far nothing has come of
them.
--
Vik Fearing
On Mon, Mar 2, 2020, 12:30 Vik Fearing <vik@postgresfriends.org> wrote:
On 02/03/2020 18:09, Don Seiler wrote:
The REAL reason for this is that I'm wondering if I created a NOT NULL
check constraint with "NOT VALID" would that then NOT be considered insuch
a "short-circuit" case until I ran the VALIDATE CONSTRAINT on it?
Perhaps I
should have just asked this in its own thread but I started diving into
the
query plan thing.
You cannot do this because NOT NULL isn't a real constraint (meaning it
does not appear in pg_constraint). There have been several attempts to
make it a real constraint over the years but so far nothing has come of
them
Using the check constraint method seemed to allow for the "not valid" step.
I'm curious what the difference is between a NOT NULL check constraint
versus setting the column to NOT NULL (assuming both are validated).
Don.
On 03/03/2020 00:02, Don Seiler wrote:
On Mon, Mar 2, 2020, 12:30 Vik Fearing <vik@postgresfriends.org> wrote:
On 02/03/2020 18:09, Don Seiler wrote:
The REAL reason for this is that I'm wondering if I created a NOT NULL
check constraint with "NOT VALID" would that then NOT be considered insuch
a "short-circuit" case until I ran the VALIDATE CONSTRAINT on it?
Perhaps I
should have just asked this in its own thread but I started diving into
the
query plan thing.
You cannot do this because NOT NULL isn't a real constraint (meaning it
does not appear in pg_constraint). There have been several attempts to
make it a real constraint over the years but so far nothing has come of
themUsing the check constraint method seemed to allow for the "not valid" step.
I'm curious what the difference is between a NOT NULL check constraint
versus setting the column to NOT NULL (assuming both are validated).
You kind of answered your own question there.
--
Vik Fearing