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 <am...@amutu.com> wrote: > > Jov > blog: http:amutu.com/blog <http://amutu.com/blog> > > 2014-09-19 2:44 GMT+08:00 Robert Nix <rob...@urban4m.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
