Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.johnston@xxxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



--
.nix

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux