The EXPLAIN statement displays the execution plan of a statement. You can use the EXPLAIN statement to confirm that EDB Postgres Advanced Server is pruning partitions from the execution plan of a query.
This example shows the efficiency of partition pruning. Create a simple table:
Perform a constrained query that includes the EXPLAIN statement:
The resulting query plan shows that the server scans only the sales_asia table. That's the table in which a row with a country value of INDIA is stored.
Suppose you perform a query that searches for a row that matches a value not included in the partitioning key:
The resulting query plan shows that the server must look in all of the partitions to locate the rows that satisfy the query:
Constraint exclusion also applies when querying subpartitioned tables:
When you query the table, the query planner prunes any partitions or subpartitions from the search path that can't contain the desired result set: