Re: Re: How can i monitor exactly what (partition) tables are accessed by a query?

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(dot)g(dot)johnston(at)gmail(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
.nix