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

Slow Query

From: Pallav Kalva <pkalva(at)livedatagroup(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Slow Query
Date: 2007-12-11 15:02:26
Message-ID: 475EA682.6070903@livedatagroup.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hi,

   This below query is taking more than 3 minutes to run, as you can see 
from the explain plan it is pretty much using all the indexes still it 
is slow, nested loops are taking too long. Is there anyway I can improve 
this query performance ?

   I am using postgres8.2.4. Here are the number of records in each table.

helix_fdc=# select relname,relpages,reltuples from pg_class where 
relname in 
('activity','listingactivity','activitytype','listing','address');
     relname     | relpages |  reltuples
-----------------+----------+-------------
 listing         |   122215 | 8.56868e+06
 listingactivity |    51225 | 8.67308e+06
 address         |   244904 |  1.5182e+07
 activity        |   733896 | 6.74342e+07
 activitytype    |        2 |         120




helix_fdc=# explain analyze
helix_fdc-# select count(listingact0_.listingactivityid) as col_0_0_, 
date_trunc('day', activity3_.createdate) as col_1_0_,
helix_fdc-#        activityty1_.activitytypeid as col_2_0_, 
zipcode2_.zipcodeId as col_3_0_
helix_fdc-# from listing.listingactivity listingact0_, common.activity 
activity3_, common.activitytype activityty1_,
helix_fdc-#      postal.zipcode zipcode2_, common.activitytype 
activityty5_, listing.listing listing7_,
helix_fdc-#      listing.address listingadd8_
helix_fdc-# where listingact0_.fkactivityid=activity3_.activityId
helix_fdc-# and activity3_.fkactivitytypeid=activityty5_.activitytypeid
helix_fdc-# and listingact0_.fklistingid=listing7_.listingid
helix_fdc-# and listing7_.fkbestaddressid=listingadd8_.addressid
helix_fdc-# and (activityty5_.name in ( 'LISTING_ELEMENT_DETAIL', 
'VIRTUALCARD_DISPLAY'))
helix_fdc-# and activity3_.fkactivitytypeid=activityty1_.activitytypeid
helix_fdc-# and listingadd8_.fkzipcodeid=zipcode2_.zipcodeId
helix_fdc-# and (listingadd8_.fkzipcodeid is not null)
helix_fdc-# and activity3_.createdate>='2007-12-11 00:00:00'
helix_fdc-# group by date_trunc('day', activity3_.createdate) , 
activityty1_.activitytypeid , zipcode2_.zipcodeId;
                                                                                              
QUERY PLAN                                      
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=3587.71..3588.31 rows=40 width=20) (actual 
time=214022.231..214025.829 rows=925 loops=1)
   ->  Nested Loop  (cost=3.52..3587.31 rows=40 width=20) (actual 
time=464.743..213996.150 rows=3571 loops=1)
         ->  Nested Loop  (cost=3.52..3574.01 rows=40 width=24) (actual 
time=461.514..213891.251 rows=3571 loops=1)
               ->  Nested Loop  (cost=3.52..3469.18 rows=41 width=24) 
(actual time=421.683..208158.769 rows=3571 loops=1)
                     ->  Nested Loop  (cost=3.52..3299.05 rows=41 
width=24) (actual time=321.155..91460.769 rows=3586 loops=1)
                           ->  Nested Loop  (cost=3.52..3147.50 rows=41 
width=24) (actual time=188.756..821.893 rows=3586 loops=1)
                                 ->  Hash Join  (cost=3.52..880.59 
rows=321 width=20) (actual time=103.689..325.236 rows=4082 loops=1)
                                       Hash Cond: 
(activity3_.fkactivitytypeid = activityty5_.activitytypeid)
                                       ->  Index Scan using 
idx_activity_createdate on activity activity3_  (cost=0.00..801.68 
rows=19247 width=16) (actual time=103.495..244.987 rows=16918 loops=1)
                                             Index Cond: (createdate >= 
'2007-12-11 00:00:00'::timestamp without time zone)
                                       ->  Hash  (cost=3.50..3.50 rows=2 
width=4) (actual time=0.148..0.148 rows=2 loops=1)
                                             ->  Seq Scan on 
activitytype activityty5_  (cost=0.00..3.50 rows=2 width=4) (actual 
time=0.062..0.128 rows=2 loops=1)
                                                   Filter: (name = ANY 
('{LISTING_ELEMENT_DETAIL,VIRTUALCARD_DISPLAY}'::text[]))
                                 ->  Index Scan using 
idx_listingactivity_fkactivityid on listingactivity listingact0_  
(cost=0.00..7.05 rows=1 width=12) (actual time=0.097..0.108 rows=1 
loops=4082)
                                       Index Cond: 
(listingact0_.fkactivityid = activity3_.activityid)
                           ->  Index Scan using pk_listing_listingid on 
listing listing7_  (cost=0.00..3.68 rows=1 width=8) (actual 
time=25.216..25.260 rows=1 loops=3586)
                                 Index Cond: (listingact0_.fklistingid = 
listing7_.listingid)
                     ->  Index Scan using pk_address_addressid on 
address listingadd8_  (cost=0.00..4.14 rows=1 width=8) (actual 
time=32.508..32.527 rows=1 loops=3586)
                           Index Cond: (listing7_.fkbestaddressid = 
listingadd8_.addressid)
                           Filter: (fkzipcodeid IS NOT NULL)
               ->  Index Scan using pk_zipcode_zipcodeid on zipcode 
zipcode2_  (cost=0.00..2.54 rows=1 width=4) (actual time=1.586..1.590 
rows=1 loops=3571)
                     Index Cond: (listingadd8_.fkzipcodeid = 
zipcode2_.zipcodeid)
         ->  Index Scan using pk_activitytype_activitytypeid on 
activitytype activityty1_  (cost=0.00..0.32 rows=1 width=4) (actual 
time=0.007..0.011 rows=1 loops=3571)
               Index Cond: (activity3_.fkactivitytypeid = 
activityty1_.activitytypeid)
 Total runtime: 214029.185 ms
(25 rows)


pgsql-performance by date

Next:From: Craig JamesDate: 2007-12-11 15:05:56
Subject: Re: libgcc double-free, backend won't die
Previous:From: Manolo _Date: 2007-12-11 13:23:31
Subject: Is it spam or not?

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