Constraint exclusion

From: "Fernando Hevia" <fhevia(at)ip-tel(dot)com(dot)ar>
To: "'PostgreSQL SQL List'" <pgsql-sql(at)postgresql(dot)org>
Subject: Constraint exclusion
Date: 2007-06-20 22:05:51
Message-ID: 03c801c7b387$2ad9eb70$8f01010a@iptel.com.ar
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-es-ayuda pgsql-sql

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.

In response to

Responses

Browse pgsql-es-ayuda by date

  From Date Subject
Next Message Gabriel Hermes Colina Zambra 2007-06-20 22:31:27 Re: Ejecutar \copy desde VB
Previous Message Luis Guevara 2007-06-20 21:24:40 Re: Lentitud de postgres

Browse pgsql-sql by date

  From Date Subject
Next Message Gabriel Hermes Colina Zambra 2007-06-20 22:31:27 Re: Ejecutar \copy desde VB
Previous Message Alvaro Herrera 2007-06-20 12:58:57 Re: Ejecutar \copy desde VB