Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id AC31A9FB6FD for ; Wed, 20 Jun 2007 19:05:53 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 23517-01 for ; Wed, 20 Jun 2007 19:05:48 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from pyxis.powervt.com.ar (host3.powervt.com.ar [200.43.63.66]) by postgresql.org (Postfix) with ESMTP id 0A9769FB66E for ; Wed, 20 Jun 2007 19:05:47 -0300 (ADT) Received: from pcfhevia ([201.216.249.98]) by pyxis.powervt.com.ar (IPGroup Mail Server) with ESMTP id 1OT48953 for ; Wed, 20 Jun 2007 19:05:44 -0300 From: "Fernando Hevia" To: "'PostgreSQL SQL List'" Subject: Constraint exclusion Date: Wed, 20 Jun 2007 19:05:51 -0300 Organization: IP-TEL Message-ID: <03c801c7b387$2ad9eb70$8f01010a@iptel.com.ar> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook 11 In-Reply-To: X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028 Thread-Index: AceyylAUOqWMCMIxSmaeq+F9GecHVAAneVaw X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/149 X-Sequence-Number: 28639 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.