Обсуждение: BUG #10254: Joined Constraints not invoked on date ranges

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?

christopher.hamel@zimmer.com writes:
> 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?

No.  We currently deduce equality transitively, so the planner is able to
extract the constraint l.transaction_date = '2014-03-01' from your query
(and then use that to reason about the check constraints on l's children).
But there's nothing comparable for inequalities, and it's not clear that
adding such logic to the planner would be a net win.  It would be more
complicated than the equality case and less often useful.

            regards, tom lane