The following bug has been logged on the website:
Bug reference: 10254
Logged by: Christopher Hamel
Email address: christopher.hamel@zimmer.com
PostgreSQL version: 9.3.0
Operating system: RedHat 6.4
Description:
If you have this theoretical structure:
create table stage.header (
id int not null,
transaction_date date not null
);
create table stage.line (
header_id int not null,
transaction_date date not null,
line_id int not null
);
create table stage.header_2013 (
constraint header_2013_ck1 check (transaction_date >= '2013-01-01' and
transaction_date < '2014-01-01')
) inherits (stage.header);
create table stage.header_2014 (
constraint header_2014_ck1 check (transaction_date >= '2014-01-01' and
transaction_date < '2015-01-01')
) inherits (stage.header);
create table stage.line_2013 (
constraint line_2013_ck1 check (transaction_date >= '2013-01-01' and
transaction_date < '2014-01-01')
) inherits (stage.line);
create table stage.line_2014 (
constraint line_2014_ck1 check (transaction_date >= '2014-01-01' and
transaction_date < '2015-01-01')
) inherits (stage.line);
If I run an explain plan on the following query:
select *
from
stage.header h
join stage.line l on
h.id = l.header_id and
h.transaction_date = l.transaction_date
where
h.transaction_date = '2014-03-01'
It correctly invokes the check constraint on both h and l and only reads the
"2014" tables.
However, if I change the "= 2014-03-01" to "> 2014-03-01" the check
constraint is ignored.
If I specifically invoke the range on both the h and l tables, it will work
fine, but since the join specifies those fields have to be the same, can
that condition be propagated automatically?