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
