Thread: constraint partition issue
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)"
<Helen_Yelluas@McAfee.com> writes:
> 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);
Um ... datex is coming from where, again?
regards, tom lane
sorry, my table has many columns and missed the datex when make it look smaller. datex is the column in the table. This is the driving constraint. My task is to have 30 days of data with 5mln to 10 mln rec in 1 hour table, so I partition it by hour table and combine them into days, which are combined into month total table. My query will have " where datex between a and b" , for 2-5 days ,across the "day" boundaries , so that partition exclusion is so much important. I don't undertsand why the optimizer goes to the children when it hits the constraint on the parent that shows the date range on that parent ? thank you. Helen -- View this message in context: http://postgresql.1045698.n5.nabble.com/constraint-partition-issue-tp4258004p4259142.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
hyelluas <helen_yelluas@mcafee.com> writes:
> sorry, my table has many columns and missed the datex when make it look
> smaller.
> datex is the column in the table. This is the driving constraint.
> My task is to have 30 days of data with 5mln to 10 mln rec in 1 hour table,
> so I partition it by hour table and combine them into days, which are
> combined into month total table. My query will have " where datex between a
> and b" , for 2-5 days ,across the "day" boundaries , so that partition
> exclusion is so much important.
> I don't undertsand why the optimizer goes to the children when it hits the
> constraint on the parent that shows the date range on that parent ?
Well, you've been very careful to hide all the information that might
let anyone explain it. Possibly relevant information here includes the
datatype of the datex column, the Postgres version you're running, and
what you've got the constraint_exclusion parameter set to.
regards, tom lane
You are right to the point, Tom. The datex is timestamp with time zone and the constraints were created as timestamp without time zone. As soon as I fixed that , it all started working. thank you! Helen -- View this message in context: http://postgresql.1045698.n5.nabble.com/constraint-partition-issue-tp4258004p4259853.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
hyelluas <helen_yelluas@mcafee.com> writes:
> You are right to the point, Tom.
> The datex is timestamp with time zone and the constraints were created as
> timestamp without time zone.
> As soon as I fixed that , it all started working.
OK. In case anybody is wondering *why* that fixed it: a comparison
between timestamp with time zone and timestamp without time zone isn't
immutable, because it depends on the "timezone" runtime parameter to
interpret the timestamp without time zone. So the planner can't rely
on deductions about the comparison while making the plan --- they might
not be true anymore by the time the plan is executed. Upshot is that
the constraint exclusion machinery ignores these constraints.
regards, tom lane