Planner does not use Check Constraints

Question

Please consider the following objects:

create table invoices_2011 (
  invoice_id bigint not null,
  invoice_date date not null,
  constraint invoice_line_2011_ck1 CHECK (invoice_date >= '2011-01-01' AND 
      invoice_date < '2012-01-01')
);

create table invoices_2012 (
  invoice_id bigint not null,
  invoice_date date not null,
  constraint invoice_line_2012_ck1 CHECK (invoice_date >= '2012-01-01' AND
      invoice_date < '2013-01-01')
);

create table invoices_2013 (
  invoice_id bigint not null,
  invoice_date date not null,
  constraint invoice_line_2013_ck1 CHECK (invoice_date >= '2013-01-01' AND
      invoice_date < '2014-01-01')
);

create or replace view invoices as
select * from invoices_2011
union all 
select * from invoices_2012
union all 
select * from invoices_2013;

If I look at the explain plan for the following query:

select * from invoices where invoice_date > '2013-10-01';

It indicates that the only actual physical table to be scanned is invoices_2013, which I would expect.

However, when I look at the explain plan for this query (today is 10/11/13):

select * from invoices where invoice_date > date_trunc ('month', current_date)

It scans all three tables.

Does anyone know of any way to force the inspection/interpolation of the function in such a way that the check constraint could make use of it?

Clodoaldo Neto · Accepted Answer · 2013-10-12T12:43:20.533

The problem is that the where clause must match the check constraint. As neither date_trunc() nor current_date are immutable they will not be "inlined" in the query meaning that those functions evaluation will only happen at query execution time, after the query planning phase, and so the planner will not know if the condition matches the check constraint

To give the planner the information it needs the query will have to be built dynamically

create or replace function select_from_invoices(
    _d date
) returns setof invoices as $body$

begin

return query execute $$
    select *
    from invoices
    where invoice_date > $1
    $$
    using date_trunc('month', _d)
;

end;
$body$ language plpgsql;

Now the query will only be planned after the result of date_trunc is concatenated to the query string.

Execute it:

select *
from select_from_invoices(current_date);

Planner does not use Check Constraints

1 Answers1