Re: transitive pruning optimization on the right side of a join for partition tables - Mailing list pgsql-general

Yeah, I actually saw that paper but couldn't find a date on it.  Currently their techniques are well outside of the
scopeof my current problem particularly in consideration that I could switch to MySQL which does support the right side
joinpruning.  I figured if MySQL can do it, there might be a good chance Postgres can too or will soon. 

----- Original Message -----
From: "Ondrej Ivanič" <ondrej.ivanic@gmail.com>
Sent: Sun, 9/30/2012 5:40am
To: "Waldo, Ethan" <ewaldo@healthetechs.com>
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] transitive pruning optimization on the right side of a join for partition tables

Hi,

On 30 September 2012 16:36, Waldo, Ethan <ewaldo@healthetechs.com> 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@gmail.com)