การเชื่อมตารางและการรวมผลแบบแยกตามพาร์ทิชัน

When two tables are partitioned the same way, PostgreSQL can join matching partitions to each other instead of joining the whole tables. This is a partition-wise join.

The same idea applies to grouping: a partition-wise aggregate.

These optimizations are off by default because they increase planning cost. Turn them on per session or in postgresql.conf.

For a partition-wise join the tables must share the same partition key type and identical bounds. Otherwise the planner cannot pair partitions.

Two tables partitioned by the same range on customer_id.

With matching partitions, this join runs as several small joins, each fitting in memory more easily.

Smaller per-partition joins mean:

• Smaller hash tables that fit in work_mem
• Better cache locality
• Opportunities for parallel workers per partition

Use EXPLAIN to confirm. You will see an Append node over several join nodes rather than one giant join.

If you group by the partition key, each partition is aggregated independently and the results are concatenated. No global sort or hash across the whole table is needed.

With many partitions, considering each one increases planning time. Enable these settings only when partition counts are moderate and the query benefits clearly.

Partition-wise joins combine well with partition pruning: pruning removes irrelevant partitions first, then the remaining ones are joined pair-by-pair.

If only one table is partitioned, or the bounds differ, PostgreSQL falls back to a normal join over the appended partitions.

What is required for a partition-wise join?

You learned to speed up queries on partitioned tables with partition-wise joins and aggregates. Enable the settings, ensure matching bounds, verify with EXPLAIN, and combine with pruning for the best results.