Skip site navigation (1) Skip section navigation (2)

Re: Append Cost in query planners

From: "Nimesh Satam" <nimesh(dot)zedo(at)gmail(dot)com>
To: "Heikki Linnakangas" <heikki(at)enterprisedb(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org, nimesh(at)zedo(dot)com
Subject: Re: Append Cost in query planners
Date: 2007-10-29 11:09:16
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance

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 AS sid, AS sname, AS cid, AS cname,
rc.type AS rtype, rc.act_type AS acttype, AS adid, AS avname, AS chid, AS chname, rcr.dim AS dim, SUM(metrics.imp_del) AS
imp, SUM(metrics.clidel) AS cli, date.sqldate AS date, AS gid
FROM metrics, rn CROSS JOIN date, ra, rs, rc, rch, rcr, rg
WHERE metrics.netkey = rn.key
AND = 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,,,,,,,
rc.rev_type, rc.act_type,,, rcr.dim,;

And the query execution plan is as below


 GroupAggregate  (cost=589766.28..651315.41 rows=1119075 width=127)
   ->  Sort  (cost=589766.28..592563.97 rows=1119075 width=127)
         Sort Key:,,,,,,, rc.rtype, rc.act_type,,, rcr.dim,
         ->  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
                     Hash Cond: ("outer".s_key = "inner"."key")
                     ->  Hash Join  (cost=63827.54..368185.38 rows=1119075
                           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 =
                                       ->  Hash Join  (cost=
51298.73..243749.06 rows=1119075 width=48)
                                             Hash Cond: ("outer".gkey =
                                             ->  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)
(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

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
                                       ->  Seq Scan on rch  (cost=
0.00..1652.51 rows=82351 width=28)
                           ->  Hash  (cost=2283.83..2283.83 rows=81883
                                 ->  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
               ->  Hash  (cost=435.63..435.63 rows=24063 width=24)
                     ->  Seq Scan on radv  (cost=0.00..435.63 rows=24063
(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,

On 10/28/07, Heikki Linnakangas <heikki(at)enterprisedb(dot)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

In response to


pgsql-performance by date

Next:From: Richard HuxtonDate: 2007-10-29 11:11:51
Subject: Re: Outer joins and Seq scans
Previous:From: Dimitri FontaineDate: 2007-10-29 09:48:14
Subject: Re: Outer joins and Seq scans

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group