Understanding Partition Elimination in PostgreSQL 11
This is Part-III for my series on Postgres Table partitioning. I’ll encourage you to also read Part-I and II
PostgreSQL Table Partitioning Part I – Implementation Using Inheritance
PostgreSQL Table Partitioning Part II – Declarative Partitioning
In this post, lets compare the READ performance between partitioned and an un-partitioned table. Before that lets first review our table schema and data distribution.
Un-Partitioned Data Set
-- Data Distribution un-partitioned tbl
SELECT logdate, COUNT (logdate)
FROM measurement_np
GROUP BY logdate;
Partitioned Data Set
-- Data Distribution partitioned tbl SELECT logdate, COUNT (logdate) FROM measurement GROUP BY logdate;
Comparing Query Plan (EXPLAIN ANALYZE) and Partition Elimination
1st Execution – on cold cache
-- Un-Partitioned tbl Index Scan using measurement_np_indx_logdate on measurement_np (cost=0.44..416.13 rows=10672 width=16) (actual time=0.031..40.625 rows=10000 loops=1) Index Cond: (logdate = '2006-04-11'::date) -- Partitioned tbl Append (cost=0.43..4.46 rows=1 width=16) (actual time=0.028..2.316 rows=10000 loops=1) -> Index Scan using measurement_y2006m04_logdate_idx on measurement_y2006m04 (cost=0.43..4.45 rows=1 width=16) (actual time=0.028..1.813 rows=10000 loops=1) Index Cond: (logdate = '2006-04-11'::date)
2nd Execution – on hot cache
-- Un-Partitioned tbl Index Scan using measurement_np_indx_logdate on measurement_np (cost=0.44..337.14 rows=8671 width=16) (actual time=0.040..1.750 rows=10000 loops=1) Index Cond: (logdate = '2006-04-11'::date) -- Partitioned tbl Append (cost=0.43..405.61 rows=9519 width=16) (actual time=0.022..1.942 rows=10000 loops=1) -> Index Scan using measurement_y2006m04_logdate_idx on measurement_y2006m04 (cost=0.43..358.01 rows=9519 width=16) (actual time=0.021..1.426 rows=10000 loops=1) Index Cond: (logdate = '2006-04-11'::date)
Conclusion
On in both attempts (cold and hot cache), the data retrieval from ‘partitioned’ tables was faster than the un-partitioned table.