Hi, On 30 September 2012 16:36, Waldo, Ethan <ewaldo@xxxxxxxxxxxxxxxx> wrote: > My question is, does > postgresql support transitive pruning optimization on the right side of a > join for partition tables? If so, how do I get that to work? If not, are > there plans for this and when should a release with this feature be > expected? Few guys implemented this using Postgres (8.3) and published their results: Join Optimization Techniques for Partitioned Tables ABSTRACT Table partitioning splits a table into smaller parts that can be accessed, stored, and maintained independent of one an- other. The main use of partitioning used to be in reduc- ing the time to access large base tables in parallel systems. Partitioning has evolved into a powerful mechanism to im- prove the overall manageability of both centralized and par- allel database systems. Partitioning simplifies administra- tive tasks like data loading, removal, backup, statistics main- tenance, and storage provisioning. More importantly, SQL extensions and MapReduce frameworks now enable applica- tions and user queries to specify how derived tables should be partitioned. However, query optimization techniques have not kept pace with the rapid advances in usage and user con- trol of table partitioning. We address this gap by developing new techniques to generate efficient plans for SQL queries involving multiway joins over partitioned tables. Our tech- niques are designed for easy incorporation into bottom-up query optimizers in centralized and parallel database sys- tems. We have prototyped these techniques in PostgreSQL and in a parallel database system composed of PostgreSQL nodes managed by Hadoop. An extensive evaluation shows that our partition-aware optimization techniques, with low overhead, generate plans that are often an order of magni- tude better than plans produced by current optimizers. 8. CONCLUSION Query optimization technology has not kept pace with the growing usage and user control over table partitioning. We addressed this gap by developing new partition-aware optimization techniques to generate efficient plans for SQL queries. We made the following contributions: • Our new techniques are designed for easy incorporation into bottom-up query optimizers for both centralized and parallel systems. • We have prototyped these techniques in PostgreSQL and in a parallel shared-nothing database system composed of PostgreSQL nodes managed by Hadoop. • An extensive evaluation showed that our optimizer, with low optimization-time overhead, generates plans that are often an order of magnitude better than plans produced by current optimizers. www.cs.duke.edu/~shivnath/tmp/paqo_draft.pdf -- Ondrej Ivanic (ondrej.ivanic@xxxxxxxxx) -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
