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

[Robert Nix <robert@xxxxxxxxxxx>]

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

On Thu, Sep 18, 2014 at 9:22 PM, Jov <amutu@xxxxxxxxx> wrote:

Jov

blog: http:amutu.com/blog

2014-09-19 2:44 GMT+08:00 Robert Nix <robert@xxxxxxxxxxx>:

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.

 

Any and all help appreciated.

--

.nix

--

.nix