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 BDAF69FA2AD for ; Mon, 18 Sep 2006 03:08:19 -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 62626-03 for ; Mon, 18 Sep 2006 03:08:09 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from mail.gmx.net (mail.gmx.de [213.165.64.20]) by postgresql.org (Postfix) with SMTP id 25EAB9F9FB1 for ; Mon, 18 Sep 2006 03:08:04 -0300 (ADT) Received: (qmail invoked by alias); 18 Sep 2006 06:08:02 -0000 Received: from dslb-084-057-017-073.pools.arcor-ip.net (EHLO ltmku) [84.57.17.73] by mail.gmx.net (mp019) with SMTP; 18 Sep 2006 08:08:02 +0200 X-Authenticated: #23352258 From: "Milen Kulev" To: "'Tom Lane'" Cc: Subject: Re: Partition elimination problem Date: Mon, 18 Sep 2006 08:07:51 +0200 Message-ID: <000001c6dae8$cc658170$0a00a8c0@trivadis.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0001_01C6DAF9.8FEFD810" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.6626 In-Reply-To: <27954.1158534812@sss.pgh.pa.us> Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 X-Y-GMX-Trusted: 0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.203 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO, HTML_50_60, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200609/223 X-Sequence-Number: 20909 This is a multi-part message in MIME format. ------=_NextPart_000_0001_01C6DAF9.8FEFD810 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hi Tom, You are right, of course : pgpool=3D# set constraint_exclusion =3D on ; SET pgpool=3D# explain analyze select * from part where id1=3D3 and = id2=3D5 ; = QUERY PLAN -------------------------------------------------------------------------= ----------------------------------------------- ----------------------------- Result (cost=3D0.00..289.92 rows=3D3 width=3D130) (actual = time=3D3.604..27.839 rows=3D483 loops=3D1) -> Append (cost=3D0.00..289.92 rows=3D3 width=3D130) (actual = time=3D3.600..22.550 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.009..0.009 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=3D3.578..20.377 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=3D3.460..3.460 rows=3D5242 loops=3D1) Index Cond: (id2 =3D 5) Total runtime: 30.576 ms Now the execution plan looks good.=20 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 ? =20 =20 pgpool=3D# show constraint_exclusion ; constraint_exclusion ---------------------- off (1 row) =20 pgpool=3D# set constraint_exclusion =3D on ; SET pgpool=3D# show constraint_exclusion ; constraint_exclusion ---------------------- on (1 row) =20 Best Regards.=20 Milen=20 -----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" writes: > My Problem is that I don't see partiotion elimination feature > (Parameer =3D constraint_exclusion is ON): Your example works as expected for me. You *sure* you have = constraint_exclusion turned on? regards, tom lane ------=_NextPart_000_0001_01C6DAF9.8FEFD810 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Nachricht

Hi Tom,
You are right, of course = :


pgpool=3D#=20 set  constraint_exclusion =3D on ;
SET
pgpool=3D# explain = analyze =20 select * from part  where id1=3D3 and id2=3D5=20 ;
           &n= bsp;           &nb= sp;           &nbs= p;            = ;            =         =20 QUERY=20 PLAN           &nb= sp;           &nbs= p;            = ;            =   
---------------------------------------------------------= -------------------------------------------------------------------------= -------------------
 Result =20 (cost=3D0.00..289.92 rows=3D3 width=3D130) (actual time=3D3.604..27.839 = rows=3D483=20 loops=3D1)
   ->  Append  (cost=3D0.00..289.92 = rows=3D3=20 width=3D130) (actual time=3D3.600..22.550 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=20 rows=3D1 width=3D130) (actual 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.009..0.009 rows=3D0=20 loops=3D1)
          = ;          =20 Index Cond: (id1 =3D = 3)
        =20 ->  Bitmap Heap Scan on part_id1_0_10__id2_0_10 part  = (cost=3D2.52..255.56=20 rows=3D1 width=3D130) (actual time=3D3.578..20.377 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=3D3.460..3.460 = rows=3D5242=20 loops=3D1)
          = ;          =20 Index Cond: (id2 =3D 5)
 Total runtime: 30.576 ms

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


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


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

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

       =20        =20         regards, tom=20 lane

 ------=_NextPart_000_0001_01C6DAF9.8FEFD810--