Re: Join optimization for inheritance tables

Nedyalko Borisov <nedyalko(at)asterdata(dot)com> writes:
> In summary, we are making two suggestions:
> 1. Extend the optimizer to consider joins between child tables when hierarchies are joined together.

We already handle this for the case where the join is nestloop with
inner index scan, and I'm not convinced that there's any real gain to be
had for other join types.

> 2. Add the "Empty Check Constraint", which would enforce that a particular table is to remain empty.

The trouble with that is that a constraint that doesn't propagate to its
child tables is a weird beast that I'd just as soon not invent.

We are currently thinking about inventing an explicit notion of
partitioned tables. If we had that, it would be reasonable to have
a special kind of "parent" table for a partitioned set and refuse to
allow any data in that relation. But I'm not excited about contorting
the general constraint mechanism in the way that would be necessary to
express this as a constraint.

regards, tom lane