PostgreSQL partition pruning?

Question

In postgres partition is done by child tables. My problem are,

1. when we issue a query to master table , does the query optimizer do partition pruning ? i.e select appropriate child table rather than going through all the child tables.
2. How does the query optimizer do the partition pruning ? My concern is unlike other databases we don't give partition column in postgres partitioning process. So how does the query optimizer knows what is the partition column ?

Answer 1

when we issue a query to master table , does the query optimizer do partition pruning ? i.e select appropriate child table rather than going through all the child tables.

Yes if the query is simple enough to prove with constraint exclusion. See the manual.

How does the query optimizer do the partition pruning

By proving that the query cannot access those partitions using CHECK constraints. See the documentation link above.

Because the constraint values must be known at planning time there are some cases where the planner cannot exclude partitions because it doesn't know the values of predicates early enough in query execution. Sometimes these queries land up needing to be split into two parts manually.

You can see which partitions are being scanned by using EXPLAIN.

• francs: A very good answer.