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

[Robert Nix]

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.

[Jov]

Jov
blog: http:amutu.com/blog <http://amutu.com/blog>

Post by Robert Nix
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.

How did you find the partitions are not actually being used?
You can try to use explain analyze to see the acutally running paln.

Post by Robert Nix
Any and all help appreciated.
--
.nix

[Robert Nix]

Joy,

The explain plan shows that all partitions will be scanned but i believe
that plan isn't valid because the check constraint that dictates which
partition to access can't be known until the query is executed due to the
value being a join. You can see what i mean using the SQL below.

I have no "proof" that the partition isn't being used except for query
execution timings. When I execute the same join query but i hardcode the
check constraint value, the query executes significantly faster,
essentially with the same timing as using a single partition table directly.


create table parent(n integer);
create table data_partitions.child1() inherits(parent);
alter table data_partitions.child1 add constraint ck1child check (n=1);
insert into data_partitions.child1 values(1);
create table data_partitions.child2() inherits(parent);
alter table data_partitions.child2 add constraint ck2child check (n=2);
insert into data_partitions.child2 values(2);
create table joiner(m integer, n integer);
insert into joiner values(0,1),(1,2);
-- These two obviously use the partitions.
explain select * from parent where n = 1;
explain select * from parent where n = 2;
-- This one doesn't use the partition (in the execution plan).
explain select * from parent join joiner on parent.n = joiner.n and
joiner.m = 0;
-- This does use the proper partition.
explain select * from parent join joiner on parent.n = joiner.n and
joiner.m = 0 and joiner.n=1;


Thanks

Post by Jov
Jov
blog: http:amutu.com/blog <http://amutu.com/blog>

Post by Robert Nix
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.

How did you find the partitions are not actually being used?
You can try to use explain analyze to see the acutally running paln.

Post by Robert Nix
Any and all help appreciated.
--
.nix

[David G Johnston]

Post by Robert Nix
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.

[Robert Nix]

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 <

Post by David G Johnston

Post by Robert Nix
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

Post by Robert Nix
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.
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.
--
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.
--
http://www.postgresql.org/mailpref/pgsql-general

[Alban Hertroys]

Post by Robert Nix
Thanks, David.
• 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.

[David Johnston]

Post by Robert Nix

Post by Robert Nix
Thanks, David.
• 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.

Post by Robert Nix
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.

[Alban Hertroys]

Post by David Johnston

Post by Alban Hertroys
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.

Post by David Johnston
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.