Partitioning is commonly used in PostgreSQL to manage large tables by splitting data into smaller child partitions. While this helps organize data, the way postgres executes queries on partitioned tables also depends on several planner parameters. These settings influence whether unnecessary partitions are skipped, how aggregate operations are performed, and how joins between partitioned tables are executed.
1. enable_partition_pruning
show enable_partition_pruning ;
Result:
enable_partition_pruning
--------------------------
on
(1 row)
Check more about this parameter metadata from pg_settings.
select * from pg_settings where name = 'enable_partition_pruning';
Result:
-[ RECORD 1 ]---+--------------------------------------------------------------------------------------------------------------------------------------------
name | enable_partition_pruning
setting | on
unit |
category | Query Tuning / Planner Method Configuration
short_desc | Enables plan-time and execution-time partition pruning.
extra_desc | Allows the query planner and executor to compare partition bounds to conditions in the query to determine which partitions must be scanned.
context | user
vartype | bool
source | default
min_val |
max_val |
enumvals |
boot_val | on
reset_val | on
sourcefile |
sourceline |
pending_restart | f
Create sample tables.
CREATE TABLE sales (
id INT,
sale_date DATE,
amount NUMERIC
)
PARTITION BY RANGE (sale_date);
Create its partition tables also.
CREATE TABLE sales_2026_01
PARTITION OF sales
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE sales_2026_02
PARTITION OF sales
FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
CREATE TABLE sales_2026_03
PARTITION OF sales
FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');
CREATE TABLE sales_2026_04
PARTITION OF sales
FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');
CREATE TABLE sales_2026_05
PARTITION OF sales
FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');
CREATE TABLE sales_2026_06
PARTITION OF sales
FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');
Insert values.
INSERT INTO sales
SELECT
g,
DATE '2026-01-01' + (random()*30)::int,
(random()*1000)::int
FROM generate_series(1,10000) g;
INSERT INTO sales
SELECT
g+10000,
DATE '2026-02-01' + (random()*27)::int,
(random()*1000)::int
FROM generate_series(1,10000) g;
INSERT INTO sales
SELECT
g+20000,
DATE '2026-03-01' + (random()*30)::int,
(random()*1000)::int
FROM generate_series(1,10000) g;
INSERT INTO sales
SELECT
g+30000,
DATE '2026-04-01' + (random()*29)::int,
(random()*1000)::int
FROM generate_series(1,10000) g;
INSERT INTO sales
SELECT
g+40000,
DATE '2026-05-01' + (random()*30)::int,
(random()*1000)::int
FROM generate_series(1,10000) g;
INSERT INTO sales
SELECT
g+50000,
DATE '2026-06-01' + (random()*29)::int,
(random()*1000)::int
FROM generate_series(1,10000) g;
Now set off to this parameter for testing purposes.
SET enable_partition_pruning=off;
Execute the query below and check its query plan.
EXPLAIN ANALYZE
SELECT *
FROM sales
WHERE sale_date<'2026-03-01';
Result:
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Append (cost=0.00..1180.02 rows=20004 width=12) (actual time=0.015..82.131 rows=20000.00 loops=1)
Buffers: shared hit=330
-> Seq Scan on sales_2026_01 sales_1 (cost=0.00..180.00 rows=10000 width=12) (actual time=0.012..13.919 rows=10000.00 loops=1)
Filter: (sale_date < '2026-03-01'::date)
Buffers: shared hit=55
-> Seq Scan on sales_2026_02 sales_2 (cost=0.00..180.00 rows=10000 width=12) (actual time=0.017..13.858 rows=10000.00 loops=1)
Filter: (sale_date < '2026-03-01'::date)
Buffers: shared hit=55
-> Seq Scan on sales_2026_03 sales_3 (cost=0.00..180.00 rows=1 width=12) (actual time=0.345..0.347 rows=0.00 loops=1)
Filter: (sale_date < '2026-03-01'::date)
Rows Removed by Filter: 10000
Buffers: shared hit=55
-> Seq Scan on sales_2026_04 sales_4 (cost=0.00..180.00 rows=1 width=12) (actual time=0.333..0.334 rows=0.00 loops=1)
Filter: (sale_date < '2026-03-01'::date)
Rows Removed by Filter: 10000
Buffers: shared hit=55
-> Seq Scan on sales_2026_05 sales_5 (cost=0.00..180.00 rows=1 width=12) (actual time=0.316..0.318 rows=0.00 loops=1)
Filter: (sale_date < '2026-03-01'::date)
Rows Removed by Filter: 10000
Buffers: shared hit=55
-> Seq Scan on sales_2026_06 sales_6 (cost=0.00..180.00 rows=1 width=12) (actual time=0.314..0.316 rows=0.00 loops=1)
Filter: (sale_date < '2026-03-01'::date)
Rows Removed by Filter: 10000
Buffers: shared hit=55
Planning:
Buffers: shared hit=40
Planning Time: 0.257 ms
Execution Time: 108.632 ms
(28 rows)
Here we can see that when the value of the parameter named enable_partition_pruning is off, then a query that involves the partition column actually scans all partitions. This takes more execution time. The scanning of all partition tables is unwanted here.
Here we can use this parameter to make the planner choose only the partition tables based on the partition column.
Now set on this parameter and re-execute the same query.
SET enable_partition_pruning=on;
EXPLAIN ANALYZE
SELECT *
FROM sales
WHERE sale_date<'2026-03-01';
Result:
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Append (cost=0.00..460.00 rows=20000 width=12) (actual time=0.027..80.997 rows=20000.00 loops=1)
Buffers: shared hit=110
-> Seq Scan on sales_2026_01 sales_1 (cost=0.00..180.00 rows=10000 width=12) (actual time=0.023..13.997 rows=10000.00 loops=1)
Filter: (sale_date < '2026-03-01'::date)
Buffers: shared hit=55
-> Seq Scan on sales_2026_02 sales_2 (cost=0.00..180.00 rows=10000 width=12) (actual time=0.019..13.869 rows=10000.00 loops=1)
Filter: (sale_date < '2026-03-01'::date)
Buffers: shared hit=55
Planning Time: 0.119 ms
Execution Time: 107.536 ms
(10 rows)
Here we can see that it only scans the child partition tables, and it didn't scan the complete partitions. This takes half of the planning time by comparing the previous query plan with enable_partition_pruning as off.
2. enable_partitionwise_aggregate
The main purpose of this parameter is to set the planner to choose the type of aggregation execution in child partition tables.
show enable_partitionwise_aggregate ;
Result:
enable_partitionwise_aggregate
--------------------------------
off
(1 row)
Check the metadata from pg_settings.
select * from pg_settings where name = 'enable_partitionwise_aggregate';
Result:
-[ RECORD 1 ]---+------------------------------------------------
name | enable_partitionwise_aggregate
setting | off
unit |
category | Query Tuning / Planner Method Configuration
short_desc | Enables partitionwise aggregation and grouping.
extra_desc |
context | user
vartype | bool
source | default
min_val |
max_val |
enumvals |
boot_val | off
reset_val | off
sourcefile |
sourceline |
pending_restart | f
Now set ‘on’ to this parameter for testing.
SET enable_partitionwise_aggregate = on;
Now create the table named sales and also its partitions.
DROP TABLE IF EXISTS sales CASCADE;
CREATE TABLE sales (
id INT,
sale_date DATE,
amount INT
)
PARTITION BY RANGE (sale_date);
CREATE TABLE sales_2026_01
PARTITION OF sales
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE sales_2026_02
PARTITION OF sales
FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
CREATE TABLE sales_2026_03
PARTITION OF sales
FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');
CREATE TABLE sales_2026_04
PARTITION OF sales
FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');
Insert the values.
INSERT INTO sales
SELECT
g,
DATE '2026-01-01' + (random()*119)::int,
(random()*1000)::int
FROM generate_series(1,500000) g;
INSERT 0 500000
Now execute the query that contains aggregate functions.
EXPLAIN (ANALYZE, VERBOSE)
SELECT COUNT(*)
FROM sales;
Result:
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=7763.59..7763.60 rows=1 width=8) (actual time=497.530..503.754 rows=1.00 loops=1)
Output: count(*)
Buffers: shared hit=2705
-> Gather (cost=2732.50..7763.56 rows=10 width=8) (actual time=340.599..503.724 rows=6.00 loops=1)
Output: (PARTIAL count(*))
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=2705
-> Parallel Append (cost=1732.50..6762.56 rows=4 width=8) (actual time=361.267..479.730 rows=2.00 loops=3)
Buffers: shared hit=2705
Worker 0: actual time=374.618..471.190 rows=2.00 loops=1
Buffers: shared hit=886
Worker 1: actual time=369.442..471.355 rows=2.00 loops=1
Buffers: shared hit=885
-> Partial Aggregate (cost=1760.00..1760.01 rows=1 width=8) (actual time=374.613..374.619 rows=1.00 loops=1)
Output: PARTIAL count(*)
Buffers: shared hit=704
Worker 0: actual time=374.613..374.619 rows=1.00 loops=1
Buffers: shared hit=704
-> Parallel Seq Scan on public.sales_2026_03 sales_2 (cost=0.00..1548.80 rows=84480 width=0) (actual time=0.028..188.318 rows=130152.00 loops=1)
Buffers: shared hit=704
Worker 0: actual time=0.028..188.318 rows=130152.00 loops=1
Buffers: shared hit=704
-> Partial Aggregate (cost=1732.50..1732.51 rows=1 width=8) (actual time=369.438..369.445 rows=1.00 loops=1)
Output: PARTIAL count(*)
Buffers: shared hit=693
Worker 1: actual time=369.438..369.445 rows=1.00 loops=1
Buffers: shared hit=693
-> Parallel Seq Scan on public.sales_2026_01 sales (cost=0.00..1524.60 rows=83160 width=0) (actual time=0.027..185.994 rows=128095.00 loops=1)
Buffers: shared hit=693
Worker 1: actual time=0.027..185.994 rows=128095.00 loops=1
Buffers: shared hit=693
-> Partial Aggregate (cost=1675.00..1675.01 rows=1 width=8) (actual time=118.437..118.443 rows=1.00 loops=3)
Output: PARTIAL count(*)
Buffers: shared hit=670
Worker 0: actual time=96.549..96.554 rows=1.00 loops=1
Buffers: shared hit=182
Worker 1: actual time=101.887..101.893 rows=1.00 loops=1
Buffers: shared hit=192
-> Parallel Seq Scan on public.sales_2026_04 sales_3 (cost=0.00..1474.00 rows=80400 width=0) (actual time=0.009..59.411 rows=41285.67 loops=3)
Buffers: shared hit=670
Worker 0: actual time=0.010..48.280 rows=33670.00 loops=1
Buffers: shared hit=182
Worker 1: actual time=0.009..51.193 rows=35520.00 loops=1
Buffers: shared hit=192
-> Partial Aggregate (cost=1595.00..1595.01 rows=1 width=8) (actual time=339.736..339.742 rows=1.00 loops=1)
Output: PARTIAL count(*)
Buffers: shared hit=638
-> Parallel Seq Scan on public.sales_2026_02 sales_1 (cost=0.00..1403.60 rows=76560 width=0) (actual time=0.027..170.726 rows=117896.00 loops=1)
Buffers: shared hit=638
Planning:
Buffers: shared hit=34
Planning Time: 0.181 ms
Execution Time: 503.822 ms
(54 rows)
From this query plan, we can see that each child partition has its own aggregation. Actually it goes to each child partition and performs the sequential scan and then performs the partial aggregation. At final postgresql combines all the parietal aggregation gives the result by combining it.
Now set ‘off’ to the parameter and check the same query again.
SET enable_partitionwise_aggregate = off;
EXPLAIN (ANALYZE, VERBOSE)
SELECT COUNT(*)
FROM sales;
Result:
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=8208.90..8208.91 rows=1 width=8) (actual time=954.678..959.988 rows=1.00 loops=1)
Output: count(*)
Buffers: shared hit=2705
-> Gather (cost=8208.68..8208.89 rows=2 width=8) (actual time=954.512..959.961 rows=3.00 loops=1)
Output: (PARTIAL count(*))
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=2705
-> Partial Aggregate (cost=7208.68..7208.69 rows=1 width=8) (actual time=937.583..937.594 rows=1.00 loops=3)
Output: PARTIAL count(*)
Buffers: shared hit=2705
Worker 0: actual time=929.117..929.128 rows=1.00 loops=1
Buffers: shared hit=898
Worker 1: actual time=929.378..929.388 rows=1.00 loops=1
Buffers: shared hit=883
-> Parallel Append (cost=0.00..6687.85 rows=208334 width=0) (actual time=0.014..703.445 rows=166666.67 loops=3)
Buffers: shared hit=2705
Worker 0: actual time=0.014..697.380 rows=166020.00 loops=1
Buffers: shared hit=898
Worker 1: actual time=0.013..697.120 rows=163174.00 loops=1
Buffers: shared hit=883
-> Parallel Seq Scan on public.sales_2026_03 sales_3 (cost=0.00..1469.60 rows=76560 width=0) (actual time=0.010..189.049 rows=130152.00 loops=1)
Buffers: shared hit=704
Worker 1: actual time=0.010..189.049 rows=130152.00 loops=1
Buffers: shared hit=704
-> Parallel Seq Scan on public.sales_2026_01 sales_1 (cost=0.00..1446.50 rows=75350 width=0) (actual time=0.011..183.581 rows=128095.00 loops=1)
Buffers: shared hit=693
Worker 0: actual time=0.011..183.581 rows=128095.00 loops=1
Buffers: shared hit=693
-> Parallel Seq Scan on public.sales_2026_04 sales_4 (cost=0.00..1398.57 rows=72857 width=0) (actual time=0.006..56.013 rows=41285.67 loops=3)
Buffers: shared hit=670
Worker 0: actual time=0.006..51.432 rows=37925.00 loops=1
Buffers: shared hit=205
Worker 1: actual time=0.005..44.593 rows=33022.00 loops=1
Buffers: shared hit=179
-> Parallel Seq Scan on public.sales_2026_02 sales_2 (cost=0.00..1331.51 rows=69351 width=0) (actual time=0.011..167.248 rows=117896.00 loops=1)
Buffers: shared hit=638
Planning:
Buffers: shared hit=45
Planning Time: 0.340 ms
Execution Time: 960.032 ms
(41 rows)
Now, in this query plan, it actually performs a sequential scan on all the child partitions and appends all the rows. Then it performs a single partial aggregate based on the appended rows, and it gives the final aggregation output.
3. enable_partitionwise_join
The main purpose of this parameter is to specify the planner to choose which method for partition wise join between child partitions.
show enable_partitionwise_join;
Result:
enable_partitionwise_join
---------------------------
off
(1 row)
Check the metadata from pg_settings.
select * from pg_settings where name = 'enable_partitionwise_join';
Result:
-[ RECORD 1 ]---+--------------------------------------------
name | enable_partitionwise_join
setting | off
unit |
category | Query Tuning / Planner Method Configuration
short_desc | Enables partitionwise join.
extra_desc |
context | user
vartype | bool
source | default
min_val |
max_val |
enumvals |
boot_val | off
reset_val | off
sourcefile |
sourceline |
pending_restart | f
Now set ‘on’ to this parameter.
SET enable_partitionwise_join = on;
Create the tables for testing the functionality of the parameter named enable_partitionwise_join.
DROP TABLE IF EXISTS orders CASCADE;
CREATE TABLE orders (
order_id INT,
order_date DATE,
customer_id INT
)
PARTITION BY RANGE (order_date);
Create the partition tables also.
CREATE TABLE orders_2026_01
PARTITION OF orders
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE orders_2026_02
PARTITION OF orders
FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
CREATE TABLE orders_2026_03
PARTITION OF orders
FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');
CREATE TABLE orders_2026_04
PARTITION OF orders
FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');
Create another table and also its partition table.
DROP TABLE IF EXISTS payments CASCADE;
CREATE TABLE payments (
payment_id INT,
order_date DATE,
amount INT
)
PARTITION BY RANGE (order_date);
CREATE TABLE payments_2026_01
PARTITION OF payments
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE payments_2026_02
PARTITION OF payments
FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
CREATE TABLE payments_2026_03
PARTITION OF payments
FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');
CREATE TABLE payments_2026_04
PARTITION OF payments
FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');
Insert values in both tables.
INSERT INTO orders
SELECT
g,
DATE '2026-01-01' + (random()*119)::int,
(random()*1000)::int
FROM generate_series(1,500000) g;
INSERT INTO payments
SELECT
g,
DATE '2026-01-01' + (random()*119)::int,
(random()*1000)::int
FROM generate_series(1,500000) g;
Now execute the query and check its query plan.
EXPLAIN
SELECT *
FROM orders o
JOIN payments p
ON o.order_date = p.order_date;
Result:
QUERY PLAN
-----------------------------------------------------------------------------------------------
Append (cost=3581.78..34173433.85 rows=2091940865 width=24)
-> Hash Join (cost=3581.78..6066671.35 rows=535427408 width=24)
Hash Cond: (o_1.order_date = p_1.order_date)
-> Seq Scan on orders_2026_01 o_1 (cost=0.00..1978.99 rows=128399 width=12)
-> Hash (cost=1977.46..1977.46 rows=128346 width=12)
-> Seq Scan on payments_2026_01 p_1 (cost=0.00..1977.46 rows=128346 width=12)
-> Hash Join (cost=3287.03..5615884.53 rows=495346040 width=24)
Hash Cond: (o_2.order_date = p_2.order_date)
-> Seq Scan on orders_2026_02 o_2 (cost=0.00..1814.47 rows=117747 width=12)
-> Hash (cost=1814.79..1814.79 rows=117779 width=12)
-> Seq Scan on payments_2026_02 p_2 (cost=0.00..1814.79 rows=117779 width=12)
-> Hash Join (cost=3616.60..6170885.69 rows=544273306 width=24)
Hash Cond: (p_3.order_date = o_3.order_date)
-> Seq Scan on payments_2026_03 p_3 (cost=0.00..2007.44 rows=130244 width=12)
-> Hash (cost=1996.82..1996.82 rows=129582 width=12)
-> Seq Scan on orders_2026_03 o_3 (cost=0.00..1996.82 rows=129582 width=12)
-> Hash Join (cost=3450.70..5860287.97 rows=516894111 width=24)
Hash Cond: (o_4.order_date = p_4.order_date)
-> Seq Scan on orders_2026_04 o_4 (cost=0.00..1914.72 rows=124272 width=12)
-> Hash (cost=1905.31..1905.31 rows=123631 width=12)
-> Seq Scan on payments_2026_04 p_4 (cost=0.00..1905.31 rows=123631 width=12)
JIT:
Functions: 40
Options: Inlining true, Optimization true, Expressions true, Deforming true
(24 rows)
Here we can see that hash node execution happens between each child partition from both tables that have the same time partition range.
Here, each partition-wise join is independent. This is the perfect example of partition-wise joining.
Now set ‘on’ to this parameter and re-execute the same query.
SET enable_partitionwise_join = off;
EXPLAIN
SELECT *
FROM orders o
JOIN payments p
ON o.order_date = p.order_date;
Result:
QUERY PLAN
---------------------------------------------------------------------------------------------
Hash Join (cost=18897.00..23920991.80 rows=2083143880 width=24)
Hash Cond: (p.order_date = o.order_date)
-> Append (cost=0.00..10205.00 rows=500000 width=12)
-> Seq Scan on payments_2026_01 p_1 (cost=0.00..1977.46 rows=128346 width=12)
-> Seq Scan on payments_2026_02 p_2 (cost=0.00..1814.79 rows=117779 width=12)
-> Seq Scan on payments_2026_03 p_3 (cost=0.00..2007.44 rows=130244 width=12)
-> Seq Scan on payments_2026_04 p_4 (cost=0.00..1905.31 rows=123631 width=12)
-> Hash (cost=10205.00..10205.00 rows=500000 width=12)
-> Append (cost=0.00..10205.00 rows=500000 width=12)
-> Seq Scan on orders_2026_01 o_1 (cost=0.00..1978.99 rows=128399 width=12)
-> Seq Scan on orders_2026_02 o_2 (cost=0.00..1814.47 rows=117747 width=12)
-> Seq Scan on orders_2026_03 o_3 (cost=0.00..1996.82 rows=129582 width=12)
-> Seq Scan on orders_2026_04 o_4 (cost=0.00..1914.72 rows=124272 width=12)
JIT:
Functions: 10
Options: Inlining true, Optimization true, Expressions true, Deforming true
(16 rows)
When enable_partitionwise_join is 'off,' PostgreSQL first appends all partitions from both partitioned tables and then performs a single join on the combined data. This prevents the planner from joining corresponding partition pairs independently.
Partitioning alone does not always give the best query performance. The postgres provides planner parameters such as enable_partition_pruning, enable_partitionwise_aggregate, and enable_partitionwise_join to decide how partitioned tables should be scanned, aggregated, and joined. Understanding how these parameters change the execution plan helps you choose the right settings for your workload and makes it easier to analyze query performance on partitioned tables.