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

On 19 Sep 2014, at 3:50, Robert Nix <robert@urban4m.com> wrote:

> 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,
sincethe 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
wayI did. 

What may be worth a try is to join against a UNION ALL of your partitions, with each section of the UNION having an
explicirtWHERE clause matching your partitioning constraints. 
The idea there is that such a UNION could provide the explicit constant WHERE clauses that your JOIN implicitly depends
on.

If that works, then the next step would be to try a VIEW using that UNION, which - assuming you automatically generate
yourpartition tables - could be created at the same moment that you create new partitions. 

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.