Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 3A5609F9E34 for ; Mon, 29 Oct 2007 08:09:25 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 25767-08 for ; Mon, 29 Oct 2007 08:09:15 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.247]) by postgresql.org (Postfix) with ESMTP id E11A99F9E31 for ; Mon, 29 Oct 2007 08:09:18 -0300 (ADT) Received: by an-out-0708.google.com with SMTP id b33so193689ana for ; Mon, 29 Oct 2007 04:09:17 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; bh=Sc/yYMEr8s11HGm/Z7lEbUR4V5Le4vm3NYWig8mX6z4=; b=dZEgIrXkpGxaIZUxDVVUZoh9CfkEE36NUty+hlSRtqbis3tjNkdCiiICL0YbZLSrmNznahtIvv2pxY7i301PmV5S0QEM7vWZuEmCMiH6QGgE73HwacCdrFGdhONLy1AogYUOVbQLFsNRLKYKNezjM24peAGXyScmKA7BuNNR4Ls= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=eVrTK9Al/9Rd8W/Eh7B5A+QGvywUGn4bml6mANWbwrnuqYgN+GhG8wVAa4XB+f2Ni8wbuaO2UK8eHxx0G9/8a2IsWHzJS9RPrVURJ9lldjcm2QVwleOmEgrCOyRACEpbUL3KcnBJII3vc+If1SFZo8KhuBeE5SVONr0JCa40FaI= Received: by 10.100.253.12 with SMTP id a12mr736788ani.1193656156548; Mon, 29 Oct 2007 04:09:16 -0700 (PDT) Received: by 10.100.215.8 with HTTP; Mon, 29 Oct 2007 04:09:16 -0700 (PDT) Message-ID: <965aa3ed0710290409w1d7680fke7858fd8667d664c@mail.gmail.com> Date: Mon, 29 Oct 2007 16:39:16 +0530 From: "Nimesh Satam" To: "Heikki Linnakangas" Subject: Re: Append Cost in query planners Cc: pgsql-performance@postgresql.org, nimesh@zedo.com In-Reply-To: <4724D34C.1000606@enterprisedb.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_1087_29324235.1193656156545" References: <965aa3ed0710280822x4d1ffc9dh74f67740fdcff5cb@mail.gmail.com> <4724ACE7.4090401@enterprisedb.com> <965aa3ed0710280950p7b931873w213e78f2b170176a@mail.gmail.com> <4724D34C.1000606@enterprisedb.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/340 X-Sequence-Number: 27362 ------=_Part_1087_29324235.1193656156545 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Heikki, Thanks for your reply. Will try to do the changes and rivert back. I had one more requirement for partitioning. I wanted to inherit two different tables for partition. Below is the query used to create the table, an crete the inheritance. CREATE TABLE metrics_d20070601 (CHECK (sqldate = '20070601')) INHERITS (metrics, date); Further more we are using the below mentioned query: SELECT rs.id AS sid, rs.name AS sname, rc.id AS cid, rc.name AS cname, rc.type AS rtype, rc.act_type AS acttype, ra.id AS adid, ra.name AS avname, rch.id AS chid, rch.name AS chname, rcr.dim AS dim, SUM(metrics.imp_del) AS imp, SUM(metrics.clidel) AS cli, date.sqldate AS date, rg.id AS gid FROM metrics, rn CROSS JOIN date, ra, rs, rc, rch, rcr, rg WHERE metrics.netkey = rn.key AND rn.id = 10 AND metrics.advkey = ra.key AND metrics.campkey = rc.key AND metrics.skey = rs.key AND metrics.chkey = rch.key AND metrics.cr_key = rcr.key AND date.sqldate BETWEEN '6/01/2007' AND '6/01/2007' AND metrics.gkey = rg.key GROUP BY date.sqldate, rs.id, rs.name, ra.id, ra.name, rc.id, rc.name, rc.rev_type, rc.act_type, rch.id, rch.name, rcr.dim, rg.id; And the query execution plan is as below QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ GroupAggregate (cost=589766.28..651315.41 rows=1119075 width=127) -> Sort (cost=589766.28..592563.97 rows=1119075 width=127) Sort Key: public.date.sqldate, rs.id, rs.name, ra.id, ra.name, rc.id, rc.name, rc.rtype, rc.act_type, rch.id, rch.name, rcr.dim, rg.id -> Hash Join (cost=64914.87..433619.51 rows=1119075 width=127) Hash Cond: ("outer".adv_key = "inner"."key") -> Hash Join (cost=64419.08..402349.16 rows=1119075 width=111) Hash Cond: ("outer".s_key = "inner"."key") -> Hash Join (cost=63827.54..368185.38 rows=1119075 width=96) Hash Cond: ("outer".campkey = "inner"."key") -> Hash Join (cost=61339.00..323731.53rows=1119075 width=66) Hash Cond: ("outer".chkey = "inner"."key") -> Hash Join (cost=59480.62..293896.26rows=1119075 width=46) Hash Cond: ("outer".cr_key = "inner"."key") -> Hash Join (cost= 51298.73..243749.06 rows=1119075 width=48) Hash Cond: ("outer".gkey = "inner"."key") -> Hash Join (cost= 51051.50..204334.21 rows=1119075 width=48) Hash Cond: (("outer".netkey = "inner"."key") AND ("outer".date_key = "inner"."key")) -> Append (cost= 0.00..51795.56 rows=1901256 width=48) -> Seq Scan on metrics (cost=0.00..25614.71 rows=940271 width=48) -> Seq Scan on metrics_d20070601 metrics (cost=0.00..26180.85 rows=960985 width=48) -> Hash (cost= 40615.57..40615.57 rows=960986 width=16) -> Nested Loop (cost=0.00..40615.57 rows=960986 width=16) -> Index Scan using rpt_netw_key_idx on rn (cost=0.00..16.92 rows=1 width=4) Filter: (id = 10) -> Append (cost=0.00..30988.79 rows=960986 width=12) -> Index Scan using rpt_dt_sqldt_idx on date (cost=0.00..3.02 rows=1 width=12) Index Cond: ((sqldate >= '2007-06-01 00:00:00'::timestamp without time zone) AND (sqldate <= '2007-06-01 00:00:00'::timestamp without time zone)) -> Seq Scan on metrics_d20070601 rpt_date (cost=0.00..30985.78 rows=960985 width=12) Filter: ((sqldate >= '2007-06-01 00:00:00'::timestamp without time zone) AND (sqldate <= '2007-06-01 00:00:00'::timestamp without time zone)) -> Hash (cost=223.18..223.18rows=9618 width=8) -> Seq Scan on rg (cost=0.00..223.18 rows=9618 width=8) -> Hash (cost=7367.71..7367.71rows=325671 width=6) -> Seq Scan on rc (cost= 0.00..7367.71 rows=325671 width=6) -> Hash (cost=1652.51..1652.51 rows=82351 width=28) -> Seq Scan on rch (cost= 0.00..1652.51 rows=82351 width=28) -> Hash (cost=2283.83..2283.83 rows=81883 width=38) -> Seq Scan on rc (cost=0.00..2283.83rows=81883 width=38) -> Hash (cost=520.63..520.63 rows=28363 width=23) -> Seq Scan on rs (cost=0.00..520.63 rows=28363 width=23) -> Hash (cost=435.63..435.63 rows=24063 width=24) -> Seq Scan on radv (cost=0.00..435.63 rows=24063 width=24) (41 rows) Can you let me know how we can avoid the double looping on the metrics table. This been a big table causes the queries to slowdown. Regards & Thanks, Nimesh. On 10/28/07, Heikki Linnakangas wrote: > > Nimesh Satam wrote: > > Following is the full plan of the query using partition. Let me know if > you > > need any further information. > > What indexes are there on the table partitions? You didn't post the > query, but it looks like your doing a join between rpt_network and the > partitioned table. An index on the join key might help... > > -- > Heikki Linnakangas > EnterpriseDB http://www.enterprisedb.com > ------=_Part_1087_29324235.1193656156545 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Heikki,


Thanks for your reply. Will try to do the changes and ri= vert back. I had one more requirement for partitioning.

I wanted to= inherit two different tables for partition. Below is the query used to cre= ate the table, an crete the inheritance.


CREATE TABLE metrics_d20070601 (CHECK (sqldate =3D '2007060= 1')) INHERITS (metrics, date);

Further more we are using the bel= ow mentioned query:

SELECT rs.id AS sid= ,=20 rs.name AS sname, r= c.id AS cid, rc.name AS cname, rc.type A= S rtype, rc.act_type AS acttype, ra.id AS adid= , ra.name AS avname, rch.id AS chid, rch.name AS chname, rcr.dim AS dim, SUM(metrics.i= mp_del) AS imp, SUM(metrics.clidel) AS cli, date.sqldate AS date, rg.id AS gid 
FROM metrics, rn CROSS JOIN date, ra, rs, rc, rc= h, rcr, rg
WHERE metrics.netkey =3D rn.key
AND rn.id =3D 10
AND metrics.advkey =3D ra.key
AND metrics.camp= key =3D rc.key
AND metrics.skey =3D rs.key
AND metrics.chkey =3D rch.key
AND = metrics.cr_key =3D rcr.key 
AND date.sqldate BETWEEN '6/01/200= 7' AND '6/01/2007'
AND metrics.gkey =3D rg.key 
GR= OUP BY date.sqldate , rs.id, rs.name, ra.id, ra.name, rc.id, rc.name<= /a>, rc.rev_type , rc.act_type, rch.id, rch.name, rcr.dim, rg.id;

And= the query execution plan is as below

     =             &nb= sp;            =             &nb= sp;            =             &nb= sp;            =             &nb= sp;            =             &nb= sp;            QUERY= PLAN           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;         =20
-----------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -------------------------------------------------
 GroupAggregate  (cost=3D589766.28..651315.41 rows=3D1119075 = width=3D127)
   ->  Sort  (cost=3D589766.28..5925= 63.97 rows=3D1119075 width=3D127)
      &n= bsp;  Sort Key: public.date.sqldate, rs.id, rs.name, ra.id, ra.name, rc.id, rc.name, rc.rtype, rc.act_type, rch.id,= rch.name, rcr.dim, rg.id
  &n= bsp;      ->  Hash Join  (cost=3D6491= 4.87..433619.51 rows=3D1119075 width=3D127)
    &nbs= p;          Hash Cond: ("= outer".adv_key =3D "inner"."key")
            = ;   ->  Hash Join  (cost=3D64419.08..402349.16 rows= =3D1119075 width=3D111)
        =              Ha= sh Cond: ("outer".s_key =3D "inner"."key")            &n= bsp;        ->  Hash Join  = (cost=3D 63827.54..368185.38 rows=3D1119075 width=3D96)
    &= nbsp;           &nbs= p;          Hash Cond: ("= outer".campkey =3D "inner"."key")
  &= nbsp;           &nbs= p;            ->&= nbsp; Hash Join  (cost=3D61339.00..323731.53 rows=3D1119075 width=3D66)
       &n= bsp;            = ;             H= ash Cond: ("outer".chkey =3D "inner"."key")            &= nbsp;           &nbs= p;        ->  Hash Join  (c= ost=3D59480.62..293896.26 rows=3D1119075 width=3D46)
            = ;            &n= bsp;            = ;  Hash Cond: ("outer".cr_key =3D "inner"."ke= y")
          &nb= sp;            =             &nb= sp;   ->  Hash Join  (cost=3D51298.73..243749.06 row= s=3D1119075 width=3D48)
            = ;            &n= bsp;            = ;        Hash Cond: ("outer".g= key =3D "inner"."key")
     = ;            &n= bsp;            = ;            &n= bsp;  ->  Hash Join  (cost=3D51051.50..204334.21 rows=3D1= 119075 width=3D48)
            = ;            &n= bsp;            = ;            &n= bsp; Hash Cond: (("outer".netkey =3D "inner"."key&= quot;) AND ("outer".date_key =3D "inner"."key"= ;))
           &n= bsp;            = ;            &n= bsp;            = ;  ->  Append  (cost=3D0.00..= 51795.56 rows=3D1901256 width=3D48)
=       &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;            -&g= t;  Seq Scan on metrics  (cost=3D 0.00..25614.71 rows=3D940271 width=3D48)
      = ;            &n= bsp;            = ;            &n= bsp;            = ; ->  Seq Scan on metrics_d20070601 metrics  (cost=3D0.00..261= 80.85 rows=3D960985 width=3D48)
        &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;     ->  Hash  (cost=3D40615.57..40615.5= 7 rows=3D960986 width=3D16)
      &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;            -&g= t;  Nested Loop  (cost=3D0.00..40615.57 rows=3D960986 width=3D16)=
            &nb= sp;            =             &nb= sp;            =              -&= gt;  Index Scan using rpt_netw_key_idx on rn  (cost=3D0.00..16.92= rows=3D1 width=3D4)
         &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;         Filter: (id =3D 10)
=             &nb= sp;            =             &nb= sp;            =              -&= gt;  Append  (cost=3D0.00..30988.79 rows=3D960986 width=3D12)
      &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            ->&= nbsp; Index Scan using rpt_dt_sqldt_idx on date  (cost=3D0.00..3.02 ro= ws=3D1 width=3D12)
      &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;     Index Cond: ((sqldate >=3D '2007-06-01= 00:00:00'::timestamp without time zone) AND (sqldate <=3D '2007= -06-01 00:00:00'::timestamp without time zone))
=             &nb= sp;            =             &nb= sp;            =             &nb= sp;      ->  Seq Scan on metrics_d20070601= rpt_date  (cost=3D0.00..30985.78 rows=3D960985 width=3D12)
=             &nb= sp;            =             &nb= sp;            =             &nb= sp;            Filte= r: ((sqldate >=3D '2007-06-01 00:00:00'::timestamp without time = zone) AND (sqldate <=3D '2007-06-01 00:00:00'::timestamp without= time zone))
          &nbs= p;            &= nbsp;           &nbs= p;         ->  Hash  (= cost=3D223.18..223.18 rows=3D9618 width=3D8)
    &nb= sp;            =             &nb= sp;            =          ->  Seq Scan on rg=   (cost=3D0.00..223.18 rows=3D9618 width=3D8)
            = ;            &n= bsp;            = ;  ->  Hash  (cost=3D7367.71..7367.71 rows=3D325671 width= =3D6)
           =             &nb= sp;            =          ->  Seq Scan on rc=   (cost=3D0.00..7367.71 rows=3D325671 width=3D6)
   =             &nb= sp;            =      ->  Hash  (cost=3D 1652.51..1652.51 rows=3D82351 width=3D28)
     =             &nb= sp;            =          ->  Seq Scan on rc= h  (cost=3D0.00..1652.51 rows=3D82351 width=3D28)
   = ;            &n= bsp;           -> = ; Hash  (cost=3D2283.83..2283.83 rows=3D81883 width=3D38)
            = ;            &n= bsp;        ->  Seq Scan on rc&n= bsp; (cost=3D0.00..2283.83 rows=3D81883 width=3D38)
   &n= bsp;            = ;     ->  Hash  (cost=3D520.63..520.63 row= s=3D28363 width=3D23)
        &n= bsp;            = ;      ->  Seq Scan on rs  (cost=3D 0.00..520.63 rows=3D28363 width=3D23)
     &nbs= p;         ->  Hash  (= cost=3D435.63..435.63 rows=3D24063 width=3D24)
    &= nbsp;           &nbs= p;    ->  Seq Scan on radv  (cost=3D0.00..435.6= 3 rows=3D24063 width=3D24)
(41 rows)

Can you let me know how we c= an avoid the double looping on the metrics table. This been a big table cau= ses the queries to slowdown.


Regards & Thanks,
Nimesh.


On 10/28/07, Heikki Linnakangas= <heikki@enterprisedb.com= > wrote:
Nimesh Satam wrote:
> Following is the full plan of the query using = partition. Let me know if you
> need any further information.

What indexes are there on the= table partitions? You didn't post the
query, but it looks like your= doing a join between rpt_network and the
partitioned table. An index on= the join key might help...

--
  Heikki Linnakangas
  EnterpriseDB&nb= sp;  http://www.enterprisedb.c= om

------=_Part_1087_29324235.1193656156545--