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

On Friday, September 19, 2014, Alban Hertroys <haramrae(at)gmail(dot)com> wrote:

> On 19 Sep 2014, at 3:50, Robert Nix <robert(at)urban4m(dot)com <javascript:;>>
> 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.
>

That makes no sense. If you join against partitions instead of the parent
then the contents of the where clause on those partition queries is
irrelevant. Furthermore, combining a bunch of of queries via union is
exactly what PostgreSQL is doing when it executes the original plan -
it's just you are doing it manually.

I may be getting your thoughts confused here but if so that's mostly due to
the lack of any concrete query examples to evaluate.

David J.