2

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?

4
  • I dont know the answer to your question, but I can imagine that it is possible to construct a check constraint that is too complicated for the optimizer to take advantage of. Commented Jun 6, 2020 at 20:42
  • You may be interested in the following article:blog.jooq.org/2017/09/28/… It is a couple of years old, but it may give you a couple of ideas to try for postgres 12 Commented Jun 6, 2020 at 20:55
  • Very interesting read! Thanks a lot. I hope postgres 12 comes out better than 9.6 did there.
    – Rasmus
    Commented Jun 7, 2020 at 7:52
  • In the spirit of what you are asking there is some pretty cool research going on in Homotopy Type Theory. You may be interested in cosette.cs.washington.edu . The idea is to prove whether two queries against a given schema are equivalent. Any vendor that implements this fully-fledged will have a killer app no doubt Commented Jun 10, 2020 at 21:09

1 Answer 1

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.

1
  • Constraint exclusion is a pretty narrow definition of the question. Commented Jun 7, 2020 at 14:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.