Re: Pruning useless tables for queries

On Wed, May 21, 2003 at 12:42:35PM -0400, Tom Lane wrote:
> Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> > One optimisation is for the query planner to drop tables whose output do not
> > affect the final result (where the WHERE clauses and the CHECK constraints
> > prove that no rows can be returned). While this is not the case for simple
> > queries, when involving views and inheritance it's very easy to do.
>
> Under what conditions is this actually going to buy you anything?

Well, I guess it's in situations where your CHECK constraints are used to
indicate range restrictions in subtables, for example partitioning. In fact,
the principle users of this would be for cases where inheritance pulls in
lots of tables that are actually quite separate. If you can use restrictions
to reduce the number of tables I think that's worth it.

In fact, I'd be happy if it *only* applied to inheritence trees. That would
simplify the implementation and not affect the rest of the planner at all.
The issue of optimising constant restrictions would be separate.

> Index-scans with self-contradictory index conditions, for example, fall
> through quite quickly already (look at the scan startup logic in nbtree.c).
> I'm not sure that there's any gain in having the planner duplicate that
> effort.

That only applies in cases where an index-scan is used. If the restriction is
"WHERE FALSE" then the planner produces a sequential scan. Other than a
small amount of code to do with partial indexes, there doesn't appear to be
a lot of work to simplify restrictions in cases where the result is always
true or false.

> > Ideally, you could create a new node would has a RangeTable (I think that's
> > the right term) but produces no output.
>
> We already use Result nodes with resconstantqual qualifiers to handle
> gating of execution of entire subplans (see query_planner()). It might
> be worth thinking about whether that concept is useful to apply at lower
> levels of a plan tree.

Ok, that does make things easier. It obviously wouldn't be needed for
inheritance trees, unless the entire tree was pruned, which seems so
unlikely it's not worth bothering about. But I do think that using it cases
where the restrictions are obviously self-contradictory would be beneficial.

Thanks for the help,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> "the West won the world not by the superiority of its ideas or values or
> religion but rather by its superiority in applying organised violence.
> Westerners often forget this fact, non-Westerners never do."
> - Samuel P. Huntington