What happens if filters are applied to the table? What will the optimizer decide to do in order to execute this query in the most efficient way possible? The following example shows us how the PostgreSQL planner will behave:

test=# EXPLAIN SELECT * FROM t_data WHERE t = '2016-01-04';
                            QUERY PLAN 
----------------------------------------------------------------- 
 Append (cost=0.00..95.12 rows=23 width=40) 
   -> Seq Scan on t_data (cost=0.00..0.00 rows=1 width=40) 
         Filter: (t = '2016-01-04'::date) 
   -> Seq Scan on t_data_2016 (cost=0.00..25.00 rows=6 width=40) 
         Filter: (t = '2016-01-04'::date) 
   -> Seq Scan on t_data_2015 (cost=0.00..25.00 rows=6 width=40) 
         Filter: (t = '2016-01-04'::date) 
   -> Seq Scan on t_data_2014 (cost=0.00..25.00 rows=6 width=40) 
         Filter: (t = '2016-01-04'::date) 
   -> Seq Scan on t_data_2013 (cost=0.00..20.12 rows=4 width=40) 
         Filter: (t = '2016-01-04'::date) 
(11 rows) 

PostgreSQL will apply the filter to all the partitions in the structure. It doesn't know that the table name is somehow related to the content of the tables. To the database, names are just names and have nothing to do with what we are looking for. This makes sense, of course, since there is no mathematical justification for doing anything else.

The point now is: how can we teach the database that the 2016 table only contains 2016 data, the 2015 table only contains 2015 data, and so on? Table constraints are here to do exactly that. They teach PostgreSQL about the content of those tables and therefore allow the planner to make smarter decisions than before. This feature is called constraint exclusion and helps dramatically speed up queries in many cases.

The following listing shows how table constraints can be created:

test=# ALTER TABLE t_data_2013 
   ADD CHECK (t < '2014-01-01'); 
ALTER TABLE 
test=# ALTER TABLE t_data_2014 
   ADD CHECK (t >= '2014-01-01' AND t < '2015-01-01'); 
ALTER TABLE 
test=# ALTER TABLE t_data_2015 
   ADD CHECK (t >= '2015-01-01' AND t < '2016-01-01'); 
ALTER TABLE 
test=# ALTER TABLE t_data_2016 
   ADD CHECK (t >= '2016-01-01' AND t < '2017-01-01'); 
ALTER TABLE 

For each table, a CHECK constraint can be added.

In PostgreSQL, these constraints can overlap – this is not forbidden and can make sense in some cases. However, it is usually better to have non-overlapping constraints because PostgreSQL has the option to prune more tables.

Here's what happens after adding those table constraints:

test=# EXPLAIN SELECT * FROM t_data WHERE t = '2016-01-04';
                            QUERY PLAN 
----------------------------------------------------------------- 
 Append (cost=0.00..25.00 rows=7 width=40) 
   -> Seq Scan on t_data (cost=0.00..0.00 rows=1 width=40) 
         Filter: (t = '2016-01-04'::date) 
   -> Seq Scan on t_data_2016 (cost=0.00..25.00 rows=6 width=40) 
         Filter: (t = '2016-01-04'::date) 
(5 rows)

The planner will be able to remove many of the tables from the query and only keep those that potentially contain the data. This query can greatly benefit from a shorter and more efficient plan. In particular, if those tables are really large, removing them can boost speed considerably.