current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

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 ?
share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

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.

share|improve this answer
    
A very good answer. –  francs Jul 1 at 5:55
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.