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

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:

hannu=# create table empty_master_table (
hannu(# id serial,
hannu(# data text,
hannu(# constraint table_must_be_empty check(false)
hannu(# );
NOTICE: CREATE TABLE will create implicit sequence
"empty_master_table_id_seq" for "serial" column "empty_master_table.id"
CREATE TABLE
hannu=# insert into empty_master_table (data) values (NULL);
ERROR: new row for relation "empty_master_table" violates check
constraint "tab le_must_be_empty"
hannu=# create table first_partition() inherits (empty_master_table);
CREATE TABLE
hannu=# \d first_partition
Table "public.first_partition"
Column | Type | Modifiers
--------+---------
+--------------------------------------------------------------------
id | integer | not null default nextval
('public.empty_master_table_id_seq'::text)
data | text |
Check constraints:
"table_must_be_empty" CHECK (false)
Inherits: empty_master_table

hannu=# alter table first_partition drop constraint table_must_be_empty;
ALTER TABLE
hannu=# \d first_partition
Table "public.first_partition"
Column | Type | Modifiers
--------+---------
+--------------------------------------------------------------------
id | integer | not null default nextval
('public.empty_master_table_id_seq'::text)
data | text |
Inherits: empty_master_table

hannu=# \d empty_master_table
Table "public.empty_master_table"
Column | Type | Modifiers
--------+---------
+--------------------------------------------------------------------
id | integer | not null default nextval
('public.empty_master_table_id_seq'::text)
data | text |
Check constraints:
"table_must_be_empty" CHECK (false)

hannu=# insert into first_partition(data) values ('first_partition');
INSERT 19501405 1
hannu=#

I imagine that this kind of thing does not work well with pg_dump, but
it is at least possible.

> Currently, there is no restriction that all constraints *must* be
> mutually exclusive, nor even that the constraints may be similar on each
> table. This can be useful for some designs where the inheritance
> hierarchy is not "disjoint", as UML would term this situation.

actually this is GOOD, as this way I can have a constraint on both
insert_timestamp and primary_key fields, which are mostly but not
absolutely in the same order. And also to add extra IN (X,Y,Z)
constraints for some other fields.

> 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.

> CURRENT RESTRICTIONS
>
> It is not yet possible to specify that Constraints on child tables will
> be mutually exclusive of each other. Currently, it would be up to the
> designer to ensure that, if desired.
>
> It is not yet possible to specify that an inheritance parent has no
> rows, and, if so, should always be excluded from the query.

I think that a simple "CHECK(false)" constraint should be enough for
this.

> 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.

> Currently, there is no way to
> tell which Constraints have been inherited from the parent, so exclusion
> tests will be re-executed against all child tables. This will cause
> additional optimization time.

Have you done any performance testing, i.e. what is the actual impact of
CE on planning time ?

> Currently, all child tables will be considered. It may be possible in
> the future to pre-sort the list of child tables, so that optimization
> time can be reduced for parent tables with large numbers of partitions.
>
> Currently, there is no index on the pg_inherits system table. As a
> result, parents with more than 1000 child tables are likely to
> experience longer than desirable planning times for their queries.

Am I right that this is a general postgresql issue and has nothing to do
with CE ?

> 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?

Or do I have to make sure that it is not lifted out of that subquery
using something like pl/pgsql function ?

> Other existing restrictions on Inherited tables continue to apply.

WHat happens for multiple inheritance ?

Is it detected and then also excluded from CE ?

Or is that just a "don't do it" item ?

> Further enhancements to the CE feature can be expected in the future.

Great! :D

And a big Thank You!

--
Hannu Krosing <hannu(at)tm(dot)ee>