Обсуждение: 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)
--
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
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 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
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 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 > > > 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). You kind of answered your own question there. -- Vik Fearing