constraint partition issue

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)"

Responses

Browse pgsql-general by date

  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