constraint checking on partitions
I have several partitions on a history table that are partitioned by a date
range (monthly). However, it's possible for an unexpected but valid date
(either very far in the future or very far in the past) to come in the data
set and so there is an "overflow" table.
Say table A is parent, B is April data, C is June data, D is July data, and
O is overflow data.
I set several stored procedures to facilitate the adding of triggers,
constraints, etc for partitions. These procs, in addition to adding the
constraint the normal partitions, also add a "NOT" constraint to the
overflow table. i.e., when the July partition is created with
alter table D add constraint onlyjuly check (date1 >= '2009-07-01' and date1
< '2009-07-01')
Then this is also run
alter table O add constraint notjuly check (NOT(date1 >= '2009-07-01' and
date1 < '2009-07-01'))
The planner excludes correctly except that it always checks O.
It doesn't seem to be able to use the multiple constraints on O.
Are multiple "NOT" constraints too much for the planner for excluding
partitions?
postgres=# select version();
version
----------------------------------------------------------------------------
------------------------------------------
PostgreSQL 8.4.0 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC)
3.4.6 20060404 (Red Hat 3.4.6-8.0.1), 64-bit
Chris Spotts
"Chris Spotts" <rfusca@gmail.com> writes:
Then this is also run
alter table O add constraint notjuly check (NOT(date1 >= '2009-07-01' and
date1 < '2009-07-01'))
The planner excludes correctly except that it always checks O.
What are the actual queries you're hoping it will exclude for?
regards, tom lane
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, July 09, 2009 1:52 PM
To: Chris Spotts
Cc: 'postgres list'
Subject: Re: [GENERAL] constraint checking on partitions"Chris Spotts" <rfusca@gmail.com> writes:
Then this is also run
alter table O add constraint notjuly check (NOT(date1 >= '2009-07-01'
and
date1 < '2009-07-01'))
The planner excludes correctly except that it always checks O.
What are the actual queries you're hoping it will exclude for?
regards, tom lane
[Spotts, Christopher]
I mistyped, that should be
alter table D add constraint onlyjuly check (date1 >= '2009-07-01' and date1
< '2009-08-01')
Then this is also run
alter table O add constraint notjuly check (NOT(date1 >= '2009-07-01' and
date1 < '2009-08-01'))
If I ran a select * from A where date1 >= '2009-07-02' and date1 <
'2009-07-15' then I would think it wouldn't check O.
"Chris Spotts" <rfusca@gmail.com> writes:
I mistyped, that should be
alter table D add constraint onlyjuly check (date1 >= '2009-07-01' and date1
< '2009-08-01')
Then this is also run
alter table O add constraint notjuly check (NOT(date1 >= '2009-07-01' and
date1 < '2009-08-01'))
If I ran a select * from A where date1 >= '2009-07-02' and date1 <
'2009-07-15' then I would think it wouldn't check O.
Works for me ...
regression=# create table a (date1 date);
CREATE TABLE
regression=# create table july() inherits(a);
CREATE TABLE
regression=# create table other() inherits(a);
CREATE TABLE
regression=# alter table other add constraint notjuly check (NOT(date1 >= '2009-07-01' and date1 < '2009-08-01'));
ALTER TABLE
regression=# explain select * from a where date1 >= '2009-07-02' and date1 < '2009-07-15';
QUERY PLAN
----------------------------------------------------------------------------------------
Result (cost=0.00..92.00 rows=24 width=4)
-> Append (cost=0.00..92.00 rows=24 width=4)
-> Seq Scan on a (cost=0.00..46.00 rows=12 width=4)
Filter: ((date1 >= '2009-07-02'::date) AND (date1 < '2009-07-15'::date))
-> Seq Scan on july a (cost=0.00..46.00 rows=12 width=4)
Filter: ((date1 >= '2009-07-02'::date) AND (date1 < '2009-07-15'::date))
(6 rows)
regression=#
regards, tom lane
If I ran a select * from A where date1 >= '2009-07-02' and date1 <
'2009-07-15' then I would think it wouldn't check O.
[Spotts, Christopher]
I oversimplified this too much - but I figured out what was happening.
If you added the June table as well and added a separate NOT constraint for
June, and then wrote the query
"SELECT * from A where date1 >= '2009-06-01' and date1 < '2009-07-05'" the
planner can't match them to individual constraints, so it doesn't exclude.
Theoretically the planner could logically "AND" them together to get better
exclusion, but it must not be.