X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 46B2C9F9FAC for ; Sun, 17 Sep 2006 19:12:49 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 41108-10 for ; Sun, 17 Sep 2006 19:12:39 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from mail.gmx.net (mail.gmx.net [213.165.64.20]) by postgresql.org (Postfix) with SMTP id D91179F9F80 for ; Sun, 17 Sep 2006 19:12:38 -0300 (ADT) Received: (qmail invoked by alias); 17 Sep 2006 22:12:36 -0000 Received: from dslb-084-057-018-121.pools.arcor-ip.net (EHLO ltmku) [84.57.18.121] by mail.gmx.net (mp034) with SMTP; 18 Sep 2006 00:12:36 +0200 X-Authenticated: #23352258 From: "Milen Kulev" To: Subject: Partition elimination problem Date: Mon, 18 Sep 2006 00:12:33 +0200 Message-ID: <002201c6daa6$6057a220$0a00a8c0@trivadis.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0023_01C6DAB7.23E07220" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.6626 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 Importance: Normal X-Y-GMX-Trusted: 0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.136 tagged_above=0 required=5 tests=AWL, HTML_50_60, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200609/221 X-Sequence-Number: 20907 This is a multi-part message in MIME format. ------=_NextPart_000_0023_01C6DAB7.23E07220 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 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>=3D 0 and id1<=3D10) ) = INHERITS (part); create table part_id1_11_20 ( CHECK ( id1>=3D11 and id1<=3D20) ) = INHERITS (part); --- level2 partitions -- subpartitions for parent partition1 create table part_id1_0_10__id2_0_10 ( CHECK ( id2>=3D 0 and = id2<=3D10) ) INHERITS(part_id1_0_10); create table part_id1_0_10__id2_11_20 ( CHECK ( id2>=3D 11 and = id2<=3D20) ) INHERITS(part_id1_0_10); -- subpartitions for parent partition2 create table part_id1_11_20__id2_0_10 ( CHECK ( id2>=3D 0 and = id2<=3D10) ) INHERITS(part_id1_11_20); create table part_id1_11_20__id2_11_20 ( CHECK ( id2>=3D 11 and = id2<=3D20) ) 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=3D# EXPLAIN ANALYZE select * from part where id1 =3D 3 and = id2 =3D 5; = QUERY PLAN -------------------------------------------------------------------------= ----------------------------------------------- ----------------------------- Result (cost=3D0.00..957.04 rows=3D5 width=3D130) (actual = time=3D1.606..9.216 rows=3D483 loops=3D1) -> Append (cost=3D0.00..957.04 rows=3D5 width=3D130) (actual = time=3D1.602..7.910 rows=3D483 loops=3D1) -> Seq Scan on part (cost=3D0.00..24.85 rows=3D1 width=3D130) = (actual time=3D0.001..0.001 rows=3D0 loops=3D1) Filter: ((id1 =3D 3) AND (id2 =3D 5)) -> Bitmap Heap Scan on part_id1_0_10 part (cost=3D1.02..9.50 = rows=3D1 width=3D130) (actual time=3D0.014..0.014 rows=3D0 loops=3D1) Recheck Cond: (id1 =3D 3) Filter: (id2 =3D 5) -> Bitmap Index Scan on idx_part_id1_0_10 = (cost=3D0.00..1.02 rows=3D5 width=3D0) (actual time=3D0.010..0.010 rows=3D0 loops=3D1) Index Cond: (id1 =3D 3) -> Bitmap Heap Scan on part_id1_11_20 part = (cost=3D2.89..436.30 rows=3D1 width=3D130) (actual time=3D0.025..0.025 rows=3D0 loops=3D1) Recheck Cond: (id1 =3D 3) Filter: (id2 =3D 5) -> Bitmap Index Scan on idx_part_id1_11_20 = (cost=3D0.00..2.89 rows=3D254 width=3D0) (actual time=3D0.021..0.021 rows=3D0 loops=3D1) Index Cond: (id1 =3D 3) -> Bitmap Heap Scan on part_id1_0_10__id2_0_10 part = (cost=3D2.52..255.56 rows=3D1 width=3D130) (actual time=3D1.554..6.526 rows=3D483 loops=3D1) Recheck Cond: (id2 =3D 5) Filter: (id1 =3D 3) -> Bitmap Index Scan on idx_part_id1_0_10__id2_0_10 = (cost=3D0.00..2.52 rows=3D148 width=3D0) (actual time=3D1.410..1.410 rows=3D5242 loops=3D1) Index Cond: (id2 =3D 5) -> Bitmap Heap Scan on part_id1_0_10__id2_11_20 part = (cost=3D2.47..230.82 rows=3D1 width=3D130) (actual time=3D0.034..0.034 rows=3D0 loops=3D1) Recheck Cond: (id2 =3D 5) Filter: (id1 =3D 3) -> Bitmap Index Scan on idx_part_id1_0_10__id2_11_20 = (cost=3D0.00..2.47 rows=3D134 width=3D0) (actual time=3D0.030..0.030 rows=3D0 loops=3D1) Index Cond: (id2 =3D 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 =3D3 ?? pgpool=3D# \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 >=3D 11 AND id1 <=3D 20) Inherits: part Has OIDs: no Best Regards. Milen=20 ------=_NextPart_000_0023_01C6DAB7.23E07220 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Nachricht

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

My setup:

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

--- level 1 partitions on id1 column only
create table=20 part_id1_0_10  ( CHECK ( id1>=3D 0 and  id1<=3D10)  = )=20 INHERITS  (part);
create table part_id1_11_20 ( CHECK ( = id1>=3D11=20 and  id1<=3D20)  ) INHERITS  (part);

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

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

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

pgpool=3D# EXPLAIN  ANALYZE  select * from  = part =20 where   id1 =3D  3 and id2 =3D=20 5;
           &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ;        =20 QUERY=20 PLAN           &nb= sp;           &nbs= p;            = ;            =   
---------------------------------------------------------= -------------------------------------------------------------------------= -------------------
 Result =20 (cost=3D0.00..957.04 rows=3D5 width=3D130) (actual time=3D1.606..9.216 = rows=3D483=20 loops=3D1)
   ->  Append  (cost=3D0.00..957.04 = rows=3D5=20 width=3D130) (actual time=3D1.602..7.910 rows=3D483=20 loops=3D1)
         = ->  Seq=20 Scan on part  (cost=3D0.00..24.85 rows=3D1 width=3D130) (actual = time=3D0.001..0.001=20 rows=3D0=20 loops=3D1)
          = ;    =20 Filter: ((id1 =3D 3) AND (id2 =3D=20 5))
         ->  = Bitmap Heap=20 Scan on part_id1_0_10 part  (cost=3D1.02..9.50 rows=3D1 = width=3D130) (actual=20 time=3D0.014..0.014 rows=3D0=20 loops=3D1)
          = ;    =20 Recheck Cond: (id1 =3D=20 3)
           &= nbsp;  =20 Filter: (id2 =3D=20 5)
           &= nbsp;  =20 ->  Bitmap Index Scan on idx_part_id1_0_10  = (cost=3D0.00..1.02 rows=3D5=20 width=3D0) (actual time=3D0.010..0.010 rows=3D0=20 loops=3D1)
          = ;          =20 Index Cond: (id1 =3D = 3)
        =20 ->  Bitmap Heap Scan on part_id1_11_20=20 part  (cost=3D2.89..436.30 rows=3D1 width=3D130) (actual = time=3D0.025..0.025 rows=3D0=20 loops=3D1)
          = ;    =20 Recheck Cond: (id1 =3D=20 3)
           &= nbsp;  =20 Filter: (id2 =3D=20 5)
           &= nbsp;  =20 ->  Bitmap Index Scan on idx_part_id1_11_20  = (cost=3D0.00..2.89=20 rows=3D254 width=3D0) (actual time=3D0.021..0.021 rows=3D0=20 loops=3D1)
          = ;          =20 Index Cond: (id1 =3D = 3)
        =20 ->  Bitmap Heap Scan on part_id1_0_10__id2_0_10 part =20 (cost=3D2.52..255.56 rows=3D1 width=3D130) (actual time=3D1.554..6.526 = rows=3D483=20 loops=3D1)
          = ;    =20 Recheck Cond: (id2 =3D=20 5)
           &= nbsp;  =20 Filter: (id1 =3D=20 3)
           &= nbsp;  =20 ->  Bitmap Index Scan on idx_part_id1_0_10__id2_0_10 =20 (cost=3D0.00..2.52 rows=3D148 width=3D0) (actual time=3D1.410..1.410 = rows=3D5242=20 loops=3D1)
          = ;          =20 Index Cond: (id2 =3D = 5)
        =20 ->  Bitmap Heap Scan on part_id1_0_10__id2_11_20 part  = (cost=3D2.47..230.82=20 rows=3D1 width=3D130) (actual time=3D0.034..0.034 rows=3D0=20 loops=3D1)
          = ;    =20 Recheck Cond: (id2 =3D=20 5)
           &= nbsp;  =20 Filter: (id1 =3D=20 3)
           &= nbsp;  =20 ->  Bitmap Index Scan on idx_part_id1_0_10__id2_11_20 =20 (cost=3D0.00..2.47 rows=3D134 width=3D0) (actual time=3D0.030..0.030 = rows=3D0=20 loops=3D1)
          = ;          =20 Index Cond: (id2 =3D 5)
 Total runtime: 9.950 ms
(25 = rows)

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

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



Best=20 Regards.
Milen

------=_NextPart_000_0023_01C6DAB7.23E07220--