Обсуждение: How can i monitor exactly what (partition) tables are accessed by a query?

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.

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.

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, 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.

On 19 September 2014 09:13, David Johnston <david.g.johnston@gmail.com> wrote:
>> 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.

Perhaps. That depends on whether the planner will use the information
that the CHECK constraints in those partition tables provide; it
probably does, though.
Admittedly, a combination of E_NOCOFFEE and E_NOTENOUGHTIME caused me
to mix up the master table (for which that makes a lot more sense) and
the underlying partition tables there.

> 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.

Ah yes, it does. It's really the distribution of those same values in
the other side of the join that's causing the planning issues.

I suppose you could attempt to manually "partition" the left-hand side
of the join. That sounds like a recipe to end up with a really
inefficient query though - can the planner do that and did it discard
the option because it would cost more?

I'm just throwing around some ideas, whether they actually work for
you remains to be seen.

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.