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(at)urban4m(dot)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, 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.

What may be worth a try is to join against a UNION ALL of your partitions, with each section of the UNION having an explicirt WHERE 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 your partition 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.