PostgreSQL 17 came with quite some additional features for the optimizer. One of them is about “is null” and “is not null” handling when column have a “not null” constraint defined. In the past it was common to just not add a “is not null” to a query when you are anyway sure that there cannot be any nulls because of a “not null” constraint. If you did it it anyway, there was some overhead because the “is not null” had to be evaluated. This changed with PostgreSQL 17 as the optimizer got smarter for such cases.

This can easily be demonstrated by creating a simple table with one column having a not null constraint:

postgres=# select version();
                                                   version                                                    
--------------------------------------------------------------------------------------------------------------
 PostgreSQL 16.0 on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
(1 row)
postgres=# create table t ( a int not null );
CREATE TABLE
postgres=# insert into t select * from generate_series(1,1000000);
INSERT 0 1000000
postgres=# analyze t;
ANALYZE

… and the same in version 17:

postgres=# select version();
                                                   version                                                    
--------------------------------------------------------------------------------------------------------------
 PostgreSQL 17.0 on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
(1 row)
postgres=# create table t ( a int not null );
CREATE TABLE
postgres=# insert into t select * from generate_series(1,1000000);
INSERT 0 1000000
postgres=# analyze t;
ANALYZE

If we select from that table in version 16 by asking for all the row which are not null we’ll see this:

postgres=# select version();
                                                   version                                                    
--------------------------------------------------------------------------------------------------------------
 PostgreSQL 16.0 on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
(1 row)
postgres=# explain select * from t where a is not null;
                        QUERY PLAN                         
-----------------------------------------------------------
 Seq Scan on t  (cost=0.00..14425.00 rows=1000000 width=4)
   Filter: (a IS NOT NULL)
(2 rows)

The filter (a IS NOT NULL) is evaluated even that we know it does apply to all the rows because of the not null constraint. Doing the same in version 17 looks like this:

postgres=# select version();
                                                   version                                                    
--------------------------------------------------------------------------------------------------------------
 PostgreSQL 17.0 on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
(1 row)
postgres=# explain select * from t where a is not null;
                        QUERY PLAN                         
-----------------------------------------------------------
 Seq Scan on t  (cost=0.00..14425.00 rows=1000000 width=4)
(1 row)

The filter is gone which saves the overhead of evaluating it. The other case is asking for all rows which are null, which obviously cannot be the case because the constraint does not allow that.

PostgreSQL 16 will do a parallel sequential scan over the whole table:

postgres=# select version();
                                                   version                                                    
--------------------------------------------------------------------------------------------------------------
 PostgreSQL 16.0 on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
(1 row)
postgres=# explain select * from t where a is null;
                            QUERY PLAN                             
-------------------------------------------------------------------
 Gather  (cost=1000.00..9591.77 rows=1 width=4)
   Workers Planned: 2
   ->  Parallel Seq Scan on t  (cost=0.00..8591.67 rows=1 width=4)
         Filter: (a IS NULL)
(4 rows)

PostgreSQL 17 will not do that anymore:

postgres=# select version();
                                                   version                                                    
--------------------------------------------------------------------------------------------------------------
 PostgreSQL 17.0 on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
(1 row)
postgres=# explain select * from t where a is null;
                QUERY PLAN                
------------------------------------------
 Result  (cost=0.00..0.00 rows=0 width=0)
   One-Time Filter: false
(2 rows)

Seems to be obvious, but versions before 17 cannot do this. A really nice improvement and only this should make you using PostgreSQL 17 as soon as possible.