Thread: Partition elimination problem

Partition elimination problem

From
"Milen Kulev"
Date:

Hi listers,
I wanted to try PG partitioning (aka constraint exclusion) with two levels .
I am  using  PG 8.1.3 on  RHEL4U2,

My setup:

CREATE TABLE part (
    id1    int not null,
    id2    int not null,
    id3    int not null,
    filler varchar(200)
    );

--- level 1 partitions on id1 column only
create table part_id1_0_10  ( CHECK ( id1>= 0 and  id1<=10)  ) INHERITS  (part);
create table part_id1_11_20 ( CHECK ( id1>=11 and  id1<=20)  ) INHERITS  (part);

--- level2 partitions
-- subpartitions for parent partition1
create table part_id1_0_10__id2_0_10  ( CHECK ( id2>=  0 and  id2<=10) ) INHERITS(part_id1_0_10);
create table part_id1_0_10__id2_11_20 ( CHECK ( id2>= 11 and  id2<=20) ) INHERITS(part_id1_0_10);

-- subpartitions for parent partition2
create table part_id1_11_20__id2_0_10  ( CHECK ( id2>=  0 and  id2<=10) ) INHERITS(part_id1_11_20);
create table part_id1_11_20__id2_11_20 ( CHECK ( id2>= 11 and  id2<=20) ) INHERITS(part_id1_11_20);

I have created indexes on all tables.
My Problem is that  I don't see partiotion elimination feature (Parameer constraint_exclusion is ON):

pgpool=# EXPLAIN  ANALYZE  select * from  part  where   id1 =  3 and id2 = 5;
                                                                     QUERY PLAN                                                  
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=0.00..957.04 rows=5 width=130) (actual time=1.606..9.216 rows=483 loops=1)
   ->  Append  (cost=0.00..957.04 rows=5 width=130) (actual time=1.602..7.910 rows=483 loops=1)
         ->  Seq Scan on part  (cost=0.00..24.85 rows=1 width=130) (actual time=0.001..0.001 rows=0 loops=1)
               Filter: ((id1 = 3) AND (id2 = 5))
         ->  Bitmap Heap Scan on part_id1_0_10 part  (cost=1.02..9.50 rows=1 width=130) (actual time=0.014..0.014 rows=0 loops=1)
               Recheck Cond: (id1 = 3)
               Filter: (id2 = 5)
               ->  Bitmap Index Scan on idx_part_id1_0_10  (cost=0.00..1.02 rows=5 width=0) (actual time=0.010..0.010 rows=0 loops=1)
                     Index Cond: (id1 = 3)
         ->  Bitmap Heap Scan on part_id1_11_20 part  (cost=2.89..436.30 rows=1 width=130) (actual time=0.025..0.025 rows=0 loops=1)
               Recheck Cond: (id1 = 3)
               Filter: (id2 = 5)
               ->  Bitmap Index Scan on idx_part_id1_11_20  (cost=0.00..2.89 rows=254 width=0) (actual time=0.021..0.021 rows=0 loops=1)
                     Index Cond: (id1 = 3)
         ->  Bitmap Heap Scan on part_id1_0_10__id2_0_10 part  (cost=2.52..255.56 rows=1 width=130) (actual time=1.554..6.526 rows=483 loops=1)
               Recheck Cond: (id2 = 5)
               Filter: (id1 = 3)
               ->  Bitmap Index Scan on idx_part_id1_0_10__id2_0_10  (cost=0.00..2.52 rows=148 width=0) (actual time=1.410..1.410 rows=5242 loops=1)
                     Index Cond: (id2 = 5)
         ->  Bitmap Heap Scan on part_id1_0_10__id2_11_20 part  (cost=2.47..230.82 rows=1 width=130) (actual time=0.034..0.034 rows=0 loops=1)
               Recheck Cond: (id2 = 5)
               Filter: (id1 = 3)
               ->  Bitmap Index Scan on idx_part_id1_0_10__id2_11_20  (cost=0.00..2.47 rows=134 width=0) (actual time=0.030..0.030 rows=0 loops=1)
                     Index Cond: (id2 = 5)
 Total runtime: 9.950 ms
(25 rows)

Why PG is searching in part_id1_11_20 table, for example ?  From the check contraint it is pretty
Clear that in this table there are not records with ids =3 ??

pgpool=# \d+ part_id1_11_20
               Table "public.part_id1_11_20"
 Column |          Type          | Modifiers | Description
--------+------------------------+-----------+-------------
 id1    | integer                | not null  |
 id2    | integer                | not null  |
 id3    | integer                | not null  |
 filler | character varying(200) |           |
Indexes:
    "idx_part_id1_11_20" btree (id1)
Check constraints:
    "part_id1_11_20_id1_check" CHECK (id1 >= 11 AND id1 <= 20)
Inherits: part
Has OIDs: no



Best Regards.
Milen

Re: Partition elimination problem

From
Tom Lane
Date:
"Milen Kulev" <makulev@gmx.net> writes:
> My Problem is that  I don't see partiotion elimination feature (Parameer =
> constraint_exclusion is ON):

Your example works as expected for me.  You *sure* you have
constraint_exclusion turned on?

            regards, tom lane

Re: Partition elimination problem

From
"Milen Kulev"
Date:

Hi Tom,
You are right, of course :


pgpool=# set  constraint_exclusion = on ;
SET
pgpool=# explain analyze  select * from part  where id1=3 and id2=5 ;
                                                                     QUERY PLAN                                                  
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=0.00..289.92 rows=3 width=130) (actual time=3.604..27.839 rows=483 loops=1)
   ->  Append  (cost=0.00..289.92 rows=3 width=130) (actual time=3.600..22.550 rows=483 loops=1)
         ->  Seq Scan on part  (cost=0.00..24.85 rows=1 width=130) (actual time=0.001..0.001 rows=0 loops=1)
               Filter: ((id1 = 3) AND (id2 = 5))
         ->  Bitmap Heap Scan on part_id1_0_10 part  (cost=1.02..9.50 rows=1 width=130) (actual time=0.014..0.014 rows=0 loops=1)
               Recheck Cond: (id1 = 3)
               Filter: (id2 = 5)
               ->  Bitmap Index Scan on idx_part_id1_0_10  (cost=0.00..1.02 rows=5 width=0) (actual time=0.009..0.009 rows=0 loops=1)
                     Index Cond: (id1 = 3)
         ->  Bitmap Heap Scan on part_id1_0_10__id2_0_10 part  (cost=2.52..255.56 rows=1 width=130) (actual time=3.578..20.377 rows=483 loops=1)
               Recheck Cond: (id2 = 5)
               Filter: (id1 = 3)
               ->  Bitmap Index Scan on idx_part_id1_0_10__id2_0_10  (cost=0.00..2.52 rows=148 width=0) (actual time=3.460..3.460 rows=5242 loops=1)
                     Index Cond: (id2 = 5)
 Total runtime: 30.576 ms

Now the execution plan  looks good.
And now I have another problem -> constraint_exclusion  is on in the postgresql.conf file.
BUT  in my psql session I see something different ;(. Only after setting this parameter  explicitely in the session, it works.
What I have done wrong  ?
 
 
pgpool=# show constraint_exclusion ;
 constraint_exclusion
----------------------
 off
(1 row)
 
pgpool=# set  constraint_exclusion = on ;
SET
pgpool=#  show constraint_exclusion ;
 constraint_exclusion
----------------------
 on
(1 row)
 
Best Regards.
Milen


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, September 18, 2006 1:14 AM
To: Milen Kulev
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Partition elimination problem


"Milen Kulev" <makulev@gmx.net> writes:
> My Problem is that  I don't see partiotion elimination feature
> (Parameer = constraint_exclusion is ON):

Your example works as expected for me.  You *sure* you have constraint_exclusion turned on?

                        regards, tom lane

Re: Partition elimination problem -> Solved

From
"Milen Kulev"
Date:
.... And sorry for the hassle.
I was  running the db cluster  with .... Tthw wrong(old) postgresql.conf ;(

Best Regrads.
Milen


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, September 18, 2006 1:14 AM
To: Milen Kulev
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Partition elimination problem


"Milen Kulev" <makulev@gmx.net> writes:
> My Problem is that  I don't see partiotion elimination feature
> (Parameer = constraint_exclusion is ON):

Your example works as expected for me.  You *sure* you have constraint_exclusion turned on?

            regards, tom lane