When postgresql runs a query, it tries to avoid looking at tables that do not contain the data it is searching for. postgresql does this to save time. One way it achieves this is by using a feature called constraint exclusion.
Even though newer ways of dividing data in postgres use something called partition pruning, it is still good to know about constraint exclusion. This is especially true if you work with systems that use inheritance to divide data or if you have to take care of old postgres applications. You will still use constraint exclusion when you work with these systems.
We can check the current value of this parameter in postgres.
show constraint_exclusion ;
Result :
constraint_exclusion
----------------------
partition
(1 row)
The constraint_exclusion in postgres is a planner parameter that helps the optimizer look at the CHECK constraints on tables. So what happens is that if the planner can figure out that a table does not have what the query is asking for in the where condition, it will just leave that table out of the execution plan.
Check the metadata from pg_settings.
select * from pg_settings where name = 'constraint_exclusion';
Result :
-[ RECORD 1 ]---+-----------------------------------------------------------------------------------------
name | constraint_exclusion
setting | partition
unit |
category | Query Tuning / Other Planner Options
short_desc | Enables the planner to use constraints to optimize queries.
extra_desc | Table scans will be skipped if their constraints guarantee that no rows match the query.
context | user
vartype | enum
source | default
min_val |
max_val |
enumvals | {partition,on,off}
boot_val | partition
reset_val | partition
sourcefile |
sourceline |
pending_restart | f
Create some sample tables for testing.
CREATE TABLE sales
(
id INT,
sale_date DATE,
amount NUMERIC
);
CREATE TABLE sales_q1
(
CHECK (
sale_date >= DATE '2024-01-01'
AND sale_date < DATE '2024-04-01'
)
) INHERITS (sales);
CREATE TABLE sales_q2
(
CHECK (
sale_date >= DATE '2024-04-01'
AND sale_date < DATE '2024-07-01'
)
) INHERITS (sales);
CREATE TABLE sales_q3
(
CHECK (
sale_date >= DATE '2024-07-01'
AND sale_date < DATE '2024-10-01'
)
) INHERITS (sales);
Insert values into each child table.
INSERT INTO sales_q1
SELECT
g,
DATE '2024-01-01'+(g%90),
g
FROM generate_series(1,100000) g;
INSERT INTO sales_q2
SELECT
g,
DATE '2024-04-01'+(g%90),
g
FROM generate_series(1,100000) g;
INSERT INTO sales_q3
SELECT
g,
DATE '2024-07-01'+(g%90),
g
FROM generate_series(1,100000) g;
Create an index on the column named sale_data in each child table.
CREATE INDEX sales_q1_idx ON sales_q1(sale_date);
CREATE INDEX sales_q2_idx ON sales_q2(sale_date);
CREATE INDEX sales_q3_idx ON sales_q3(sale_date);
To get the latest statistics for the postgres planner, execute the analyze command.
ANALYZE;
Now, temporarily set off to the constraint exclusion parameter.
SET constraint_exclusion = off;
Execute the query below and check its execution plan.
EXPLAIN
SELECT *
FROM sales
WHERE sale_date BETWEEN
'2024-04-15'
AND
'2024-04-30';
Result :
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Append (cost=0.00..1162.87 rows=17753 width=14)
-> Seq Scan on sales sales_1 (cost=0.00..0.00 rows=1 width=40)
Filter: ((sale_date >= '2024-04-15'::date) AND (sale_date <= '2024-04-30'::date))
-> Index Scan using sales_q1_idx on sales_q1 sales_2 (cost=0.29..8.31 rows=1 width=14)
Index Cond: ((sale_date >= '2024-04-15'::date) AND (sale_date <= '2024-04-30'::date))
-> Bitmap Heap Scan on sales_q2 sales_3 (cost=250.23..1057.48 rows=17750 width=14)
Recheck Cond: ((sale_date >= '2024-04-15'::date) AND (sale_date <= '2024-04-30'::date))
-> Bitmap Index Scan on sales_q2_idx (cost=0.00..245.79 rows=17750 width=0)
Index Cond: ((sale_date >= '2024-04-15'::date) AND (sale_date <= '2024-04-30'::date))
-> Index Scan using sales_q3_idx on sales_q3 sales_4 (cost=0.29..8.31 rows=1 width=14)
Index Cond: ((sale_date >= '2024-04-15'::date) AND (sale_date <= '2024-04-30'::date))
(11 rows)
Here, the postgresql checks the table, which is sales, and all the child tables, which are sales_q1, sales_q2, and sales_q3. Only sales_q2 actually has the data we want. So postgres still looks at the other child tables, which is a waste of time and makes the whole process slower
Now, let's try the partition value for this parameter.
SET constraint_exclusion = partition;
Execute the same query again.
EXPLAIN
SELECT *
FROM sales
WHERE sale_date BETWEEN
'2024-04-15'
AND
'2024-04-30';
Result :
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Append (cost=0.00..1146.24 rows=17751 width=14)
-> Seq Scan on sales sales_1 (cost=0.00..0.00 rows=1 width=40)
Filter: ((sale_date >= '2024-04-15'::date) AND (sale_date <= '2024-04-30'::date))
-> Bitmap Heap Scan on sales_q2 sales_2 (cost=250.23..1057.48 rows=17750 width=14)
Recheck Cond: ((sale_date >= '2024-04-15'::date) AND (sale_date <= '2024-04-30'::date))
-> Bitmap Index Scan on sales_q2_idx (cost=0.00..245.79 rows=17750 width=0)
Index Cond: ((sale_date >= '2024-04-15'::date) AND (sale_date <= '2024-04-30'::date))
(7 rows)
When constraint_exclusion is set to partition, postgres looks at the check constraints on tables that are partitioned based on inheritance during query planning.
The date range we are using in the query, from 2024-04-15 to 2024-04-30, is within the range for sales_q2.
So the planner figures out that sales_q1. Sales_q3 will not have any matching rows and will be skipped in the plan. This means only the main table, sales, and the correct child table, sales_q2, are checked.
It reduces work, and it makes the plan more efficient. The sales_q2 and sales tables are scanned because they are the ones that could have the data we need.
The planner excludes sales_q1 and sales_q3 because they do not have the data for the date range. This is how postgres optimizes queries using constraint_exclusion.
Now, temporarily set on this parameter.
SET constraint_exclusion = on;
Execute the same query again.
EXPLAIN
SELECT *
FROM sales
WHERE sale_date BETWEEN
'2024-04-15'
AND
'2024-04-30';
Result :
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Append (cost=0.00..1146.24 rows=17751 width=14)
-> Seq Scan on sales sales_1 (cost=0.00..0.00 rows=1 width=40)
Filter: ((sale_date >= '2024-04-15'::date) AND (sale_date <= '2024-04-30'::date))
-> Bitmap Heap Scan on sales_q2 sales_2 (cost=250.23..1057.48 rows=17750 width=14)
Recheck Cond: ((sale_date >= '2024-04-15'::date) AND (sale_date <= '2024-04-30'::date))
-> Bitmap Index Scan on sales_q2_idx (cost=0.00..245.79 rows=17750 width=0)
Index Cond: ((sale_date >= '2024-04-15'::date) AND (sale_date <= '2024-04-30'::date))
(7 rows)
In this example of inheritance-based partitioning, the plans that the system comes up with for constraint_exclusion = partition and constraint_exclusion = on are the same. This makes sense because both of these settings turn on the constraint exclusion feature for inheritance partitions.
The main difference between them is that the on setting can also use constraint exclusion for tables that have check constraints, not just the ones that are set up with inheritance-based partitioning. On the other hand, the partition setting mostly only works with tables that are set up to work like partitions. Because of this, one setting might need a bit of work to figure out the best plan, but in this case, both settings give us the same good plan for inheritance-based partitioning.
Now, try the query with the sale_dates that do not exist in the child tables.
EXPLAIN ANALYZE
SELECT *
FROM sales
WHERE sale_date='2030-01-01';
Result :
QUERY PLAN
---------------------------------------------------------------------------------------------------
Seq Scan on sales (cost=0.00..0.00 rows=1 width=40) (actual time=0.006..0.008 rows=0.00 loops=1)
Filter: (sale_date = '2030-01-01'::date)
Planning:
Buffers: shared hit=2
Planning Time: 0.172 ms
Execution Time: 0.022 ms
(6 rows)
The search is looking for sales on a date 2030-01-01. This date is not in the range that the smaller tables, like sales_q1, sales_q2, and sales_q3, cover. The postgres looks at the rules it has for these tables and figures out that none of them have sales on this date. So it decides not to look at these tables when it is trying to find the sales. However, it still checks the sales table because it does not have a rule that says it cannot have sales on this date. The main sales table is checked, since there are no sales in it for this date, the search is very fast, and it does not find any sales. The search for sales on 2030-01-01, in the sales table, returns zero results.
The constraint_exclusion parameter is really helpful for postgres. It stops postgres from looking at tables that do not have the rows we want. This is because it uses the check constraints when it is planning a query. We saw what happens when we set constraint_exclusion to off, the postgres has to look at every child table. If we set it to partition or on PostgreSQL can skip tables that cannot give us what we need.
When we use inheritance-based partitioning, setting constraint_exclusion to partition or on gives us the result. This is because both settings help the planner work better with partitioned child tables. Even though new versions of postgresql use something called partition pruning for partitioning, it is still a good idea to understand constraint_exclusion.