การตัดและการยกเว้นพาร์ทิชัน

Welcome to a key optimization technique in PostgreSQL: Partition Pruning. This is where the database intelligently skips scanning partitions that cannot possibly contain the data a query is looking for.

Think of it as filtering bookshelves: if you're looking for a book published in 2023, you wouldn't check shelves marked '1990-1999' or '2000-2010'.

When you execute a query on a partitioned table, PostgreSQL's query planner examines the WHERE clause. It compares the conditions in your query to the definitions of your table's partitions.

If the query's conditions guarantee that certain partitions cannot possibly hold any matching rows, the optimizer simply excludes those partitions from the scan plan. This significantly reduces the amount of data that needs to be read from disk.

Partition pruning is most evident with range-partitioned tables, especially those partitioned by date or timestamp. For example, if you have a table partitioned by month, and you query for data from a specific week, only the relevant month partition(s) will be scanned.

This is incredibly powerful for time-series data, as queries often target specific timeframes.

Let's create a simple range-partitioned table and see how EXPLAIN shows pruning. We'll partition by sale_date.

Notice how the EXPLAIN output will only show a scan on the relevant partition, not the others.

Partition pruning also works effectively with list-partitioned tables. If your table is partitioned by a discrete value, like a region or a status code, and your query filters on that specific value, only the corresponding partition will be scanned.

This is useful when you often query data specific to certain categories or groups.

Here's an example using a list-partitioned table based on a region column. Observe the EXPLAIN output to see only the 'North' partition being scanned.

PostgreSQL employs two main types of pruning:

• Static Pruning: Occurs at query planning time. The planner can see the explicit values in your WHERE clause and immediately exclude partitions.
• Dynamic Pruning: Happens during query execution. This is for more complex cases, like when the partition key is filtered by the result of a subquery or a parameter from a join. The database determines which partitions to scan as it runs.

While powerful, partition pruning isn't always possible:

• Complex Expressions: If your WHERE clause uses a function or complex expression on the partition key (e.g., EXTRACT(MONTH FROM sale_date) = 1).
• Non-Partition Key Filters: Queries filtering only on columns not part of the partition key will scan all partitions.
• Joins: Pruning with joins can be trickier, especially if the join condition doesn't directly involve the partition key or if the values are not known until runtime.

To confirm that partition pruning is working, always use EXPLAIN (or EXPLAIN ANALYZE). Look for lines like:

• -> Partition Selector (Dyanmic Partition Pruning)
• -> Append (partitions: 1)
• -> Result (partitions: 1)

The key is seeing a limited number of partitions selected, rather than scanning the entire partitioned table or all its child tables.

Understanding partition pruning is crucial for optimizing queries on large partitioned tables. Let's test your knowledge!

You've mastered partition pruning! You now understand that it's a vital PostgreSQL optimization that:

• Significantly reduces the amount of data scanned.
• Works by comparing WHERE clauses with partition definitions.
• Is especially effective with range and list partitions.
• Can be static (planning time) or dynamic (execution time).
• Can be verified using EXPLAIN.

By leveraging partition pruning, you ensure your queries run as efficiently as possible on large datasets!