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

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(at)amutu(dot)com> wrote:

>
> Jov
> blog: http:amutu.com/blog <http://amutu.com/blog>
>
> 2014-09-19 2:44 GMT+08:00 Robert Nix <robert(at)urban4m(dot)com>:
>
>> 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