From: Fernando Hevia <fhevia@ip-tel.com.ar>
To: 'PostgreSQL SQL List' <pgsql-sql@postgresql.org>
Subject: Constraint exclusion
Date: Wed, 20 Jun 2007 19:05:51 -0300
Message-ID: <03c801c7b387$2ad9eb70$8f01010a@iptel.com.ar> (raw)
In-Reply-To: <c2d9e70e0706191633k9043adaq132470f61dd851ee@mail.gmail.com>
It seems constraint exclusion is not working with my partitioned tables and
the rules I wrote.
This is my partition deployment:
-- Master table
CREATE TABLE table_master (
setuptime timestamp with time zone NOT NULL,
...
};
-- Partitions
CREATE TABLE table_p01 INHERITS (table_master);
CREATE TABLE table_p02 INHERITS (table_master);
...
CREATE TABLE table_p12 INHERITS (table_master);
-- Constraints: one partition per month
ALTER TABLE table_p01 ADD CONSTRAINT chk_table_p01_setuptime CHECK
(EXTRACT(MONTH FROM setuptime) = 1::DOUBLE PRECISION);
ALTER TABLE table_p02 ADD CONSTRAINT chk_table_p02_setuptime CHECK
(EXTRACT(MONTH FROM setuptime) = 2::DOUBLE PRECISION);
...
ALTER TABLE table_p12 ADD CONSTRAINT chk_table_p12_setuptime CHECK
(EXTRACT(MONTH FROM setuptime) = 12::DOUBLE PRECISION);
-- Insert rules for each partition table
CREATE OR REPLACE RULE rule_master_insert_01 AS ON INSERT TO table_master
WHERE (EXTRACT(MONTH FROM setuptime) = 1::double precision)
DO INSTEAD INSERT INTO table_p01 VALUES (
NEW.setuptime
}
...
-- Index on setuptime for each partition table
CREATE INDEX idx_table_01_setuptime ON table_p01 USING btree (setuptime);
...
-- Constraint exclusion
> show constraint_exclusion
on
-- Execution plan
Explain select * from table_master where setuptime between
'2007.04.01'::timestamptz and '2007.06.01'::timestamptz
QUERY PLAN
----------------------------------------------------------------------------
Result (cost=0.00..102699.64 rows=2333387 width=528)
-> Append (cost=0.00..102699.64 rows=2333387 width=528)
-> Seq Scan on table (cost=0.00..1194.28 rows=12015 width=320)
Filter: ((setuptime >= '2007-04-01 00:00:00-03'::timestamp
with time zone) AND (setuptime <= '2007-06-01 00:00:00-03'::timestamp with
time zone))
-> Index Scan using idx_table_p01_setuptime on table_p01 table
(cost=0.00..3.03 rows=1 width=285)
Index Cond: ((setuptime >= '2007-04-01
00:00:00-03'::timestamp with time zone) AND (setuptime <= '2007-06-01
00:00:00-03'::timestamp with time zone))
-> Index Scan using idx_table_p02_setuptime on table_p02 table
(cost=0.00..3.02 rows=1 width=286)
Index Cond: ((setuptime >= '2007-04-01
00:00:00-03'::timestamp with time zone) AND (setuptime <= '2007-06-01
00:00:00-03'::timestamp with time zone))
-> Index Scan using idx_table_p03_setuptime on table_p03 table
(cost=0.00..5.95 rows=1 width=233)
Index Cond: ((setuptime >= '2007-04-01
00:00:00-03'::timestamp with time zone) AND (setuptime <= '2007-06-01
00:00:00-03'::timestamp with time zone))
-> Seq Scan on table_p04 table (cost=0.00..50117.83 rows=1139895
width=232)
Filter: ((setuptime >= '2007-04-01 00:00:00-03'::timestamp
with time zone) AND (setuptime <= '2007-06-01 00:00:00-03'::timestamp with
time zone))
-> Seq Scan on table_p05 table (cost=0.00..51343.54 rows=1181467
width=231)
Filter: ((setuptime >= '2007-04-01 00:00:00-03'::timestamp
with time zone) AND (setuptime <= '2007-06-01 00:00:00-03'::timestamp with
time zone))
-> Index Scan using idx_table_p07_setuptime on table_p07 table
(cost=0.00..4.83 rows=1 width=528)
Index Cond: ((setuptime >= '2007-04-01
00:00:00-03'::timestamp with time zone) AND (setuptime <= '2007-06-01
00:00:00-03'::timestamp with time zone))
-> Index Scan using idx_table_p08_setuptime on table_p08 table
(cost=0.00..4.83 rows=1 width=528)
Index Cond: ((setuptime >= '2007-04-01
00:00:00-03'::timestamp with time zone) AND (setuptime <= '2007-06-01
00:00:00-03'::timestamp with time zone))
-> Index Scan using idx_table_p09_setuptime on table_p09 table
(cost=0.00..4.83 rows=1 width=528)
Index Cond: ((setuptime >= '2007-04-01
00:00:00-03'::timestamp with time zone) AND (setuptime <= '2007-06-01
00:00:00-03'::timestamp with time zone))
-> Index Scan using idx_table_p10_setuptime on table_p10 table
(cost=0.00..4.83 rows=1 width=528)
Index Cond: ((setuptime >= '2007-04-01
00:00:00-03'::timestamp with time zone) AND (setuptime <= '2007-06-01
00:00:00-03'::timestamp with time zone))
-> Index Scan using idx_table_p11_setuptime on table_p11 table
(cost=0.00..4.83 rows=1 width=528)
Index Cond: ((setuptime >= '2007-04-01
00:00:00-03'::timestamp with time zone) AND (setuptime <= '2007-06-01
00:00:00-03'::timestamp with time zone))
-> Index Scan using idx_table_p12_setuptime on table_p12 table
(cost=0.00..4.83 rows=1 width=528)
Index Cond: ((setuptime >= '2007-04-01
00:00:00-03'::timestamp with time zone) AND (setuptime <= '2007-06-01
00:00:00-03'::timestamp with time zone))
-> Index Scan using idx_table_p06_setuptime on table_p06 table
(cost=0.00..3.04 rows=1 width=273)
Index Cond: ((setuptime >= '2007-04-01
00:00:00-03'::timestamp with time zone) AND (setuptime <= '2007-06-01
00:00:00-03'::timestamp with time zone))
(28 rows)
The plan shows that it scans the indexes for all partitions when it should
only scan indexes for partitions 4 and 5. Is my assumption correct? If it
is, could someone point me out what I am doing wrong? I can't figure out why
it doesn't work.
I think the caveats mentioned in the manual about constraint exclusion have
been taken into account here but I might have missed something.
Regards,
Fernando.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: fhevia@ip-tel.com.ar
Subject: Re: Constraint exclusion
In-Reply-To: <03c801c7b387$2ad9eb70$8f01010a@iptel.com.ar>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox