Re: Join optimization for inheritance tables

Tom,

If the partitioning implementation does not make progress (and does not
make it for 8.5), don't you think that this could be an interesting
contribution to consider for this release?
I have put on the wiki
(http://wiki.postgresql.org/wiki/Join_optimization_for_inheritance_tables)
the results obtained so far and the use case where it is most used.

As I understand it, partitioning will certainly lead to some significant
changes/enhancements to the planner. Do you think it is realistic to get
that for 8.5?

manu

> Herodotos Herodotou <Herodotos(dot)Herodotou(at)asterdata(dot)com> writes:
>
>> This patch extends the query optimizer to consider joins between child tables when hierarchies are joined together.
>>
>
> I looked over this patch a bit. I am of the opinion that this is a lot
> of work towards a dead-end direction :-(. The patch contains a large
> amount of rather inefficient code that attempts to reverse-engineer
> knowledge about whether an inheritance tree forms a range-partitioned
> table. We already know that reasoning from first principles using a set
> of arbitrary per-table constraints doesn't scale very well, and this
> would make it a lot worse. pgsql-hackers have been talking for some
> time about implementing an explicit partitioning feature, which would
> give the planner direct knowledge about this type of table structure
> without expending nearly so much work (and then expending it all over
> again for the next query). I think the right way to go at it is to
> implement that first, and then see about teaching the planner to plan
> like this.
>
> We could possibly extract a subset of the patch that just deals with
> pushing joins down through appends, without the constraint-exclusion
> logic that tries to eliminate provably empty join pairs. But I'm
> dubious that that would be worthwhile by itself. I think you need the
> constraint exclusion to eliminate a good deal of work before this is
> actually better than doing a single join above the append.
>
> There are a number of other things I don't like, such as the restrictive
> and not-obviously-necessary requirement that all the join clauses be
> simple "Var op Var" clauses. But the main thing is that a large
> fraction of the patch is doing something I don't think we should try
> to do.
>
> regards, tom lane
>
>

--
Emmanuel Cecchet
Aster Data Systems
Web: http://www.asterdata.com