Thanks, David. I have read that page many times but clearly I have forgotten this:
- 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 asCURRENT_TIMESTAMP cannot be optimized, since the planner cannot know which partition the function value might fall into at run time. I had worked around this "issue" some time ago but I clearly should have documented _why_ I worked around it in the way I did. On Thu, Sep 18, 2014 at 9:39 PM, David G Johnston < david.g.johns...@gmail.com> wrote: > Robert Nix wrote > > I'm experiencing a problem with queries apparently not using the check > > constraints of my partition tables (tried constraint_exclusion =partition > > and =on with same results) and explain isn't sufficient to diagnose the > > issue because the value for the check constraint in the query comes from > a > > join condition. > > > > What i need is a way to see exactly what tables are actually accessed by > > the query. > > > > When i hardcode the check constraint column's value into the query, the > > explain plan reports what i expect it should be executing but the > > performance of the query indicates that the partitions are not actually > > being used when the check constraint value is obtained from a join > > condition. > > > > Any and all help appreciated. > > -- > > .nix > > Please provide a minimal schema and example query so we can explain exactly > where your misunderstanding is coming from. Generally, though, a partiton > must be excluded during plan time so the data in a table will not effect > the > final plan - only constants can do that. > > You should read this: > > http://www.postgresql.org/docs/9.3/static/ddl-partitioning.html > > and then ask specific questions with, ideally, working examples. > > And you should also provide an EXPLAIN ANALYZE since that will show almost > everything that is touched by the executor. > > David J. > > > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/How-can-i-monitor-exactly-what-partition-tables-are-accessed-by-a-query-tp5819534p5819582.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- .nix
