Constraint exclusion not working - need different constraints?

From: Doug Gorley <doug(at)gorley(dot)ca>
To: pgsql-general(at)postgresql(dot)org
Subject: Constraint exclusion not working - need different constraints?
Date: 2015-05-27 21:22:14
Message-ID: 20150527142214.akz9k6wav40cwcoo@webmail.internethosting.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Good day,

I'm attempting to implement table partitioning with constraint exclusions,
but I'm not seeing the planner limit the plan to an appropriate set of
child tables.  I'm wondering if the functions in my constraints are making
the exclusion impossible.

My constraints look like this:

    ALTER TABLE response_data.queries_response_2015w23
      ADD CONSTRAINT queries_response_2015w23_timestamp_check
          CHECK (
             date_part('year'::text, "timestamp"::timestamp without time zone) = 2015::double precision AND
             date_part('week'::text, "timestamp"::timestamp without time zone) = 23::double precision
          );

And my query looks like this:

    explain select * from public.queries_response where age("timestamp"::timestamp) < '24 hours';
   
http://www.postgresql.org/docs/9.4/static/ddl-partitioning.html gives this caveat:

    "Constraint exclusion only works when the query's WHERE clause contains
    constants (or externally supplied parameters). For example, a comparison
    against a non-immutable function such as CURRENT_TIMESTAMP cannot be
    optimized, since the planner cannot know which partition the function
    value might fall into at run time."
   
Will I likely need to replace the date_part functions with actual dates to
make this work?

Thanks,
--
Doug Gorley | doug(at)gorley(dot)ca

Responses

Browse pgsql-general by date

  From Date Subject
Next Message PT 2015-05-27 21:31:40 Re: Fwd: Raster performance
Previous Message PT 2015-05-27 20:34:30 Re: Allowing postgresql to accept 0xff syntax for data types that it makes sense for?