Which constraints can the PostgreSQL planner make use of?

Question

I'm interested in how the PostgreSQL 12 planner use constraints. I assume that it can use NOT NULL and UNIQUE, but what about foreign keys? Or CHECK?

Answer 1

For the most part, it does not use them.

The default setting of constraint_exclusion is 'partition', meaning it only tries to use NOT NULL or CHECK constraints to rule out partitions of partitioned tables. If you change this to 'on' instead, then it will try to prove cases where a query over a non-partitioned table can't return any rows because the query condition conflicts with the NOT NULL or CHECK constraint. These proof attempts are not exhaustive, however. If you are wondering about a specific case, you should try it and see.

alter table pgbench_accounts add constraint foobar check (filler is not null);
explain select count(*) from pgbench_accounts where filler is null;
                                        QUERY PLAN                                        
------------------------------------------------------------------------------------------
 Aggregate  (cost=751000.44..751000.45 rows=1 width=8)
   ->  Gather  (cost=1000.00..751000.43 rows=1 width=0)
         Workers Planned: 2
         ->  Parallel Seq Scan on pgbench_accounts  (cost=0.00..750000.33 rows=1 width=0)
               Filter: (filler IS NULL)

set constraint_exclusion TO on;
explain select count(*) from pgbench_accounts where filler is null;
                   QUERY PLAN                   
------------------------------------------------
 Aggregate  (cost=0.00..0.01 rows=1 width=8)
   ->  Result  (cost=0.00..0.00 rows=0 width=0)
         One-Time Filter: false

If you are left joining to a table using a unique or primary key, and are not actually using any columns from that table, then it will just not do the join because it will not change the number of rows returned per each row of the other table (it will always be one), and the contents of those unreferenced columns don't matter. In theory it could do the same thing with an inner join if there is a foreign key constraint, but empirically it does not do this.

It can use foreign key constraints to affect the selectivity estimates, however.