From: | <Helen_Yelluas(at)McAfee(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | constraint partition issue |
Date: | 2011-03-23 00:31:00 |
Message-ID: | FFC972810E421E4F81BE200B8EABC3D006D8723E3F@AMERSNCEXMB2.corp.nai.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Please help me understanding this execution plan :
I have a table and 3 levels of partitions .
All the tables on second level have date constraints.
Execution plan shows that all partitions are checked by the optimizer,
Nothing is excluded. My test example worked fine but this one does not.
Thank you for help.
Helen
CREATE TABLE summary_total
(
counter bigint DEFAULT 0,
destgeo_id integer DEFAULT 1,
direction integer DEFAULT 1,
mlapp_id integer DEFAULT 1
)
CREATE TABLE summary_daily_data
(
)
INHERITS (summary_total)
CREATE TABLE summ_dly_1505500
(
)
INHERITS (summary_daily_data)
ALTER TABLE summ_dly_1505500
ADD CONSTRAINT ch_date CHECK
(datex >= '2011-03-22 00:00:00'::timestamp without time zone AND
datex < '2011-03-23 00:00:00'::timestamp without time zone);
CREATE TABLE summ_dly_1505600
(
)
INHERITS (summary_daily_data)
ALTER TABLE summ_dly_1505600
ADD CONSTRAINT ch_date CHECK
(datex >= '2011-03-23 00:00:00'::timestamp without time zone AND
datex < '2011-03-24 00:00:00'::timestamp without time zone);
CREATE TABLE summ_dly_1505600
(
)
INHERITS (summary_daily_data)
ALTER TABLE summ_dly_1505600
ADD CONSTRAINT ch_date CHECK
(datex >= '2011-03-23 00:00:00'::timestamp without time zone AND
datex < '2011-03-24 00:00:00'::timestamp without time zone);
CREATE TABLE summ_dly_1505700
(
)
INHERITS (summary_daily_data)
ALTER TABLE summ_dly_1505700
ADD CONSTRAINT ch_date CHECK
(datex >= '2011-03-24 00:00:00'::timestamp without time zone AND
datex < '2011-03-25 00:00:00'::timestamp without time zone);
explain select * from summary_daily_data
where datex = '2011-03-24 00:00:00'::timestamp without time zone;
"Result (cost=0.00..8559.68 rows=1722 width=73)"
" -> Append (cost=0.00..8559.68 rows=1722 width=73)"
" -> Seq Scan on summary_daily_data (cost=0.00..19.88 rows=4 width=73)"
" Filter: (datex = '2011-03-24 00:00:00'::timestamp without time zone)"
" -> Seq Scan on summ_dly_1505500 summary_daily_data (cost=0.00..19.88 rows=4 width=73)"
" Filter: (datex = '2011-03-24 00:00:00'::timestamp without time zone)"
" -> Seq Scan on summ_dly_1505600 summary_daily_data (cost=0.00..19.88 rows=4 width=73)"
" Filter: (datex = '2011-03-24 00:00:00'::timestamp without time zone)"
" -> Seq Scan on summ_dly_1505700 summary_daily_data (cost=0.00..19.88 rows=4 width=73)"
" Filter: (datex = '2011-03-24 00:00:00'::timestamp without time zone)"
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2011-03-23 00:38:18 | Re: Trigger Function return values |
Previous Message | Scott Ribe | 2011-03-22 22:28:27 | Re: Trigger Function return values |