Re: Join optimization for inheritance tables

Hi Jeff,

On Tue, Sep 22, 2009 at 8:06 PM, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> I think you mean that the planning time is in milliseconds, not seconds.
>

The planning time is actually in seconds. Even without our feature,
planning takes a few seconds since the optimizer deals with hundreds
or even thousands of child tables. With our feature, planning time
increases by 2-3X but then again, running time improves by 6-10X. I
have added a paragraph under performance evaluation in the wiki page (
http://wiki.postgresql.org/wiki/Join_optimization_for_inheritance_tables
) in order to provide a better insight on what's happening under the
covers.

> The results seem good, and trading planning time for execution time
> seems like a reasonable idea in the case of partitioned tables. We
> already work harder planning when constraint_exclusion='partition', so
> there is some precedent (I don't know if that's a good precedent to
> follow or not).
>

With constraint_exclusion=partition, single child tables that cannot
produce tuples (based on their check constraints and filter
conditions) are filtered out. Our patch goes one step further by
identifying which joins of child tables can/cannot produce tuples
(based on their check constraints and join conditions).

> How does it compare to using merge-append?
>

Merge-append is essentially a special case of our patch. Greg Stark
compared it with our patch and made some good commends in a previous
email thread ( message id =
407d949e0907061514i5f1a044r691d1d74eaefb067(at)mail(dot)gmail(dot)com )

Thank you,

~Hero

--
Herodotos Herodotou
Graduate Student
Department of Computer Science, Duke University
Email: hero(at)cs(dot)duke(dot)edu