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)
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
