Database Reference
In-Depth Information
Enabling the constraint exclusion
The inal step in implementation of partitioning is enabling the constraint exclusion.
Constraint exclusion is backed by the
CHECK
constraints that we have used in our
child table's
CREATE
syntax. Do remember that if the constraint exclusion is disabled,
then our queries will not use
CHECK
constraints, and as a result, every query scan
will be done on all the child tables and thus will reduce performance; this is why
constraint exclusion to is very important when using partitioned tables.
The steps to enable constraint exclusion are as follows:
1.
Open the
postgresql.conf
ile that is present in the
data
directory on your
default installation path. In most cases, it is
/opt/PostgreSQL/9.4/data
.
2.
Set constraint exclusion
on
with the following row in
postgresql.conf
:
constraint_exclusion = on
Alternatively, you can set constraint exclusion to
on
using the
following command on
psql
:
warehouse_db=# SET constraint_exclusion = on;
Congratulations! Finally, the master table is available for the DML and DDL
operations, and all the
INSERT
,
SELECT
and
DELETE
operations go to the child
tables by date.
Performing DML operations on a partition
table
Before actually performing the
INSERT
operations, let's irst discuss the effect of the
INSERT
statement on the master and child tables. When a user inserts a row in the
master table, our trigger
sales_day_trigger
will be triggered, and it will call our
sales_record_insert()
trigger function, and based on
sales_date
, the insertion
will be made to a speciic child table. Now, let's insert a few records into the
sales_
record
table. We will then perform the
SELECT
statements to verify that records are
properly populated in child tables. This can be done in the following manner:
warehouse_db=# INSERT INTO sales_record
(id, sales_amount, sales_date)
VALUES
(1, 500, TO_DATE('02/12/2014','MM/DD/YYYY'));
warehouse_db=# INSERT INTO sales_record
(id, sales_amount, sales_date)
VALUES
