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