Re: [Bizgres-general] A Guide to Constraint Exclusion

On N, 2005-07-14 at 21:29 +0100, Simon Riggs wrote:
> On Thu, 2005-07-14 at 00:13 +0300, Hannu Krosing wrote:
> > On K, 2005-07-13 at 11:53 +0100, Simon Riggs wrote:
>
> > > We aren't able to exclude the parent table from the above query because
> > > no Constraint was defined upon it. Since, in our example, the parent is
> > > empty there will be little effect on the query performance. It would be
> > > a mistake to attempt to get around this by placing a Constraint on the
> > > parent, since that would then automatically be created on all child
> > > tables also. So we can never exclude the parent without automatically
> > > excluding *all* of the children also.
> >
> > At least in 8.0 you can drop the inherited constraint from child table:
>
> Yes, you can. But in discussion on -hackers on 20 May there was
> agreement (OK, me agreeing with Tom) that being able to drop inherited
> constraints was a bug (or at least a deprecated feature...)

Why is it a bug ?

Tom wrote on May 20:
> I think a good argument can be made that the above behavior is a bug,
> and that the ALTER command should have been rejected. We've gone to
> great lengths to make sure you can't ALTER a child table to make it
> incompatible with the parent in terms of the column names and types;
> shouldn't this be true of check constraints as well?

There is a good fundamental reason why we dont let people drop columns
from children or to add them to parent ONLY: if we did not, then there
would be no way to query from the hierarchy.

I can't see any such reason for forbidding dropping constraints from
child tables or disallowing CREAETE CONSTRAINT ON parent ONLY , at least
not for CHECK constraints.

And even disallowing it can probably be circumvented by a clever
functional CHECK constraint, which checks for also the table it is
defined on.

OTOH, disallowing all this for child FOREIGN KEY's seems logical though.

OTOOH, to be symmetrical with previous, we should also have UNIQUE and
PK constraints that span all the inheritance hierarchy, but I don't want
to go there now :)

> > > CE does not prevent direct access to one of the child tables in an
> > > inheritance hierarchy. In this case, no exclusion test would be
> > > performed. Exclusion tests are performed *only* when the parent table in
> > > an inheritance hierarchy is accessed. Exclusion tests are performed even
> > > if the inheritance hierarchy is many levels deep (e.g. parent-child-
> > > grandchild). CE also supports multiple inheritance.
> >
> > I'd like to see an option to ALWAYS do CE, inheritance or union (all) or
> > even simple queries.
>
> I've argued against that, but I guess if there is enough opinion I can
> be persuaded.
>
> My argument is that most constraints look something like
> CHECK ( salary > 0 and salary < 1000000)
> If you ask a query like
> select count(*) from emp where salary < -10000
> then CE would work great. But you don't find many people asking such
> nonsensical questions, or at least very often. Those queries do get
> asked, but they seem either naive or "data profiling" type queries. So
> my conclusion is that for most constraints and most queries, CE is
> literally just a waste of planning time.

CE may have some use for automatically generated queries, but in general
I agree with you.

Btw, not just UNION ALL, but also simple UNION, INTERSECT and EXCEPT
could probably be taught to use CE at some stage.

Also CE could at some stage be used to drop the UNIQUEifying nodes from
UNION (without ALL), if it can prove that the UNION is already UNIQUE.

> > > If a parent table has a Constraint defined upon it, then this will be
> > > automatically copied to all child tables.
> >
> > But they can be removed later if desired.
>
> But should not be...

FOREIGN KEY constraints should not, but I think that simple CHECK's
could.

> > Have you done any performance testing, i.e. what is the actual impact of
> > CE on planning time ?
>
> I think "bad" would be my assessment. But not "very bad". But this must
> be offset by the extraordinary time saving in execution time.
>
> Planning time depends upon the complexity of the query, the number and
> complexity of the constraints and the number of child tables.
>
> I think I would guess currently at child tables of around 1 GB, with
> sizes as small as 100 MB being reasonable. Trying to keep table size
> relatively evenly distributed would be useful also. With those
> recommendations it will always be worth 0.1 sec investment in trying to
> avoid 100+ sec sequential scan times. But again, you need to consider
> your workload.

That's why I asked for GUC, not a default behaviour ;)

> > > CE checks will not currently recognise STABLE functions within a query.
> > > So WHERE clauses such as
> > > DateKey > CURRENT DATE
> > > will not cause exclusion because CURRENT DATE is a STABLE function.
> > >
> > > CE checks are not made when the parent table is involved in a join.
> >
> > Is this also the case where parent table is inside subquery and that
> > subquery is involved in a join?
>
> My comment was too terse. What I meant was that you can't do dynamic
> exclusion based upon the results of a join. i.e. PPUC2

but what about _static_ exlusion based on constraints ?

I mean if there is a left side table with say a single partition having
CHECK(id_order BETWEEN 1 AND 1000)
(either originally or left after eliminating other by other constraints)

and 3 right side partition with
CHECK(key_order BETWEEN 1 AND 1000)
CHECK(key_order BETWEEN 1001 AND 2000)
CHECK(key_order BETWEEN 2001 AND 3000)

then the 3rd one could be eliminated statically from a join on
id_order=key_order

...

> > WHat happens for multiple inheritance ?
>
> CE works.
>
> > Is it detected and then also excluded from CE ?
>
> No
>
> > Or is that just a "don't do it" item ?
>
> Thats a religious debate.... I'll leave that one.

:)

> Best Regards, Simon Riggs

And thanks for the good work so far!

--
Hannu Krosing <hannu(at)skype(dot)net>