Re: Join optimization for inheritance tables

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