Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 5AE309FA143 for ; Mon, 29 Oct 2007 08:56:03 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 17647-01 for ; Mon, 29 Oct 2007 08:55:53 -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.242]) by postgresql.org (Postfix) with ESMTP id C65279FA07E for ; Mon, 29 Oct 2007 08:55:53 -0300 (ADT) Received: by an-out-0708.google.com with SMTP id b33so195742ana for ; Mon, 29 Oct 2007 04:55:52 -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=DC8hBXNaoizjY2K2ShJeOdSbgMYFhHi3PeyAVr0Q0HU=; b=kLM6Dkw0aImyYaqCEh+VEQny41HmuHwxUAhxcVaSWDuPu1CW/1ntNo971qY5w+zHoGnmnWsjgL8vG1+9J7GwudcuJqIKAwQf5ISy7PYqMXiuwfhmQcbISqXnwOL+lToj0incpxr/D2YLR09zRYmzEQK8qpGT/+Pr8cQkLQoyemw= 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=S4ZFfZfSMd4oyeJNH4JnM39RTqR/p8Ie2f6r4KxbIvahnEVkDPxeds9dSHRUYZFNgYBf+P1zdJt5pK7dg/YcBBNmlut1xW2btLAQUh/c7Self76kSrIGNoA7FXM+LU5e2qjORtTM1jHDJ1xS5ymJ5yu4spwWdlFMAPz9lIIG/cI= Received: by 10.100.241.17 with SMTP id o17mr854673anh.1193658952024; Mon, 29 Oct 2007 04:55:52 -0700 (PDT) Received: by 10.100.215.8 with HTTP; Mon, 29 Oct 2007 04:55:51 -0700 (PDT) Message-ID: <965aa3ed0710290455i4c746b83j38c7c49d1aa4b726@mail.gmail.com> Date: Mon, 29 Oct 2007 17:25:51 +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: <4725C695.6010704@enterprisedb.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_1112_4224026.1193658952017" References: <965aa3ed0710280822x4d1ffc9dh74f67740fdcff5cb@mail.gmail.com> <4724ACE7.4090401@enterprisedb.com> <965aa3ed0710280950p7b931873w213e78f2b170176a@mail.gmail.com> <4724D34C.1000606@enterprisedb.com> <965aa3ed0710290409w1d7680fke7858fd8667d664c@mail.gmail.com> <4725C695.6010704@enterprisedb.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/344 X-Sequence-Number: 27366 ------=_Part_1112_4224026.1193658952017 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Heikki, Thanks for the information. join_collapse_limit = 1 is already set before sending the query plan. Will a index scan on metrics.netkey help in improving the performance what other configuration parameters should we consider while opting for partition? Regards, Nimesh. On 10/29/07, Heikki Linnakangas wrote: > > Nimesh Satam wrote: > > 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.38rows=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.51rows=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.63rows=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. > > Well, if the index on metrics.netkey helps, it doesn't matter if it's > scanned twice. > > On a query with that many tables involved, you should try raising > join_collapse_limit from the default. That query accesses 9 tables, > which is just above the default join_collapse_limit of 8, so the planner > is not considering all possible join orders. > > -- > Heikki Linnakangas > EnterpriseDB http://www.enterprisedb.com > ------=_Part_1112_4224026.1193658952017 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Heikki,


Thanks for the information. join_collapse_limit = 1 is already set before sending the query plan.

Will a index scan on metrics.netkey help in improving the performance what other configuration parameters should we consider while opting for partition?


Regards,
Nimesh.

On 10/29/07, Heikki Linnakangas <heikki@enterprisedb.com> wrote:
Nimesh Satam wrote:
> 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.

Well, if the index on metrics.netkey helps, it doesn't matter if it's
scanned twice.

On a query with that many tables involved, you should try raising
join_collapse_limit from the default. That query accesses 9 tables,
which is just above the default join_collapse_limit of 8, so the planner
is not considering all possible join orders.

--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

------=_Part_1112_4224026.1193658952017--