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.Herodotou@asterdata.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