Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 3347E632B19 for ; Mon, 7 Dec 2009 09:01:12 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 82326-05 for ; Mon, 7 Dec 2009 13:00:54 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from n1-vm0.bullet.mail.gq1.yahoo.com (n1-vm0.bullet.mail.gq1.yahoo.com [67.195.23.152]) by mail.postgresql.org (Postfix) with SMTP id F00786322C2 for ; Mon, 7 Dec 2009 09:01:00 -0400 (AST) Received: from [67.195.9.82] by n1.bullet.mail.gq1.yahoo.com with NNFMP; 07 Dec 2009 13:00:58 -0000 Received: from [67.195.9.105] by t2.bullet.mail.gq1.yahoo.com with NNFMP; 07 Dec 2009 13:00:58 -0000 Received: from [127.0.0.1] by omp109.mail.gq1.yahoo.com with NNFMP; 07 Dec 2009 13:00:58 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 661526.77752.bm@omp109.mail.gq1.yahoo.com Received: (qmail 41094 invoked by uid 60001); 7 Dec 2009 13:00:58 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1260190858; bh=CIusHb/Y3s4FhrxZHWeas9I8hObq54XCoX7K7QMe4AU=; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type; b=rvOrZ0o6gcNkgnpEAn/kjEZ96eNK8p2kYnC/UsNofvBhOJO8Vkh0rAkdzS2rIM3dkDUGxUagECtMubYy+QRL6KCfiBTzdekGu8FxKz1g12FqeJX+4rX7zeXOOZAC66vDYpwPSqDerSCakIZkeJhaG7HX3ca+2ZRvu0OJ7bEXGNo= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type; b=6ReSjNF5UkuwI+HUVQ/4tLVKXYHsneOD/j1vEsI9w8Bf8vkx4wP3gpi9AaU1Uutqmr3fpLg339ZG9sCFAUUzHG3DRs1Wey0jfijJcpDBS8P63mJzblYUgkMFxCEqbSLKlpsgt4apDWL/UEZqM0YMsWzCrZIloWKMF2Nme819VNE=; Message-ID: <578773.39912.qm@web114013.mail.gq1.yahoo.com> X-YMail-OSG: gaycHygVM1nGcA3W1JDU7sVALVm3x_d2a8P2P.kUtFKfJY_RuyI_0W2LfehdZueb19Ce558um7yq1q.WRYmeLvyffpX9Qy7sTtPyGKIhnAXguZV29743l08J__GZVGrElhd5ZUpE40buRgpak4m4WXd8M2bHzwzAjC885Rv_MyIkxhBPCJdYhp6Zv4t_iUIAcYD0BYKxTcFR.FW8787rEn_m_G3sfzoTmpMEJ7GHTlb3vMXc2AOQkk.u1Vv1CEwfLBLR Received: from [59.163.11.66] by web114013.mail.gq1.yahoo.com via HTTP; Mon, 07 Dec 2009 05:00:58 PST X-Mailer: YahooMailRC/240.3 YahooMailWebService/0.8.100.260964 Date: Mon, 7 Dec 2009 05:00:58 -0800 (PST) From: Postgre Novice Subject: Query Performance To: pgsql-sql@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1337625008-1260190858=:39912" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.739 tagged_above=-10 required=5 tests=BAYES_20=-0.74, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200912/13 X-Sequence-Number: 33451 --0-1337625008-1260190858=:39912 Content-Type: text/plain; charset=us-ascii Hello List, I have a query which use to run very fast now has turn into show stopper . PostgreSQL:8.2 explain analyze select user_name,A.user_id, dnd_window_start, dnd_window_stop, B.subs as subs, B.city_id as city_id, B.source_type as source_type from users A left join user_subscriptions B on (A.user_id=B.user_id) where A.user_id in (select user_id from subs_feed where feed_id=1411 and f_sms='t') ; Nested Loop Left Join (cost=986325.88..1094601.47 rows=11148 width=55) (actual time=132635.994..1590487.280 rows=609070 loops=1) -> Nested Loop (cost=986325.88..1062280.53 rows=11148 width=26) (actual time=132630.057..1398299.117 rows=609070 loops=1) -> HashAggregate (cost=986325.88..986437.36 rows=11148 width=4) (actual time=132591.648..133386.651 rows=609070 loops=1) -> Bitmap Heap Scan on subs_feed (cost=16316.71..985194.44 rows=452576 width=4) (actual time=20199.571..131566.494 rows=609070 loops=1) Recheck Cond: (feed_id = 1411) Filter: f_sms -> Bitmap Index Scan on feed_user_id (cost=0.00..16203.57 rows=681933 width=0) (actual time=19919.512..19919.512 rows=616900 loops=1) Index Cond: (feed_id = 1411) -> Index Scan using users_pkey on users a (cost=0.00..6.79 rows=1 width=26) (actual time=2.073..2.074 rows=1 loops=609070) Index Cond: (a.user_id = subs_feed.user_id) -> Index Scan using user_subscriptions_user_id_pk on user_subscriptions b (cost=0.00..2.89 rows=1 width=33) (actual time=0.312..0.313 rows=1 loops=609070) Index Cond: (a.user_id = b.user_id) Total runtime: 1590755.918 ms (13 rows) This query runs almost half an hour. It is evident that nested loop is taking most of the time (approx 27 minutes). Any tips would be very useful. Also these table have below count: select relname,reltuples from pg_class where relname in ('users','user_subscriptions','subs_feed'); relname | reltuples --------------------+------------- user_subscriptions | 3758304 users | 1.95481e+07 subs_feed | 2.96492e+07 select n_tup_ins,n_tup_upd,n_tup_del,last_vacuum,last_analyze from pg_stat_user_tables where relname='user_subscriptions'; n_tup_ins | n_tup_upd | n_tup_del | last_vacuum | last_analyze -----------+-----------+-----------+----------------------------------+---------------------------------- 86371397 | 25865942 | 0 | 2009-12-06 23:00:36.355251+05:30 | 2009-12-06 23:00:36.355251+05:30 Thanks in advance for help ... --0-1337625008-1260190858=:39912 Content-Type: text/html; charset=us-ascii
Hello List,

I have a query which use to run very fast now has turn into show stopper .

PostgreSQL:8.2

explain analyze select user_name,A.user_id, dnd_window_start, dnd_window_stop, B.subs as subs, B.city_id as city_id, B.source_type as source_type from                                                                                                                              
users A left join user_subscriptions B on (A.user_id=B.user_id)                                                                              
where A.user_id in (select user_id from subs_feed where feed_id=1411 and f_sms='t')                                                          
;           
                                                                                                                                
           Nested Loop Left Join  (cost=986325.88..1094601.47 rows=11148 width=55) (actual time=132635.994..1590487.280 rows=609070 loops=1)
   ->  Nested Loop  (cost=986325.88..1062280.53 rows=11148 width=26) (actual time=132630.057..1398299.117 rows=609070 loops=1)
         ->  HashAggregate  (cost=986325.88..986437.36 rows=11148 width=4) (actual time=132591.648..133386.651 rows=609070 loops=1)
               ->  Bitmap Heap Scan on subs_feed  (cost=16316.71..985194.44 rows=452576 width=4) (actual time=20199.571..131566.494 rows=609070 loops=1)
                     Recheck Cond: (feed_id = 1411)
                     Filter: f_sms
                     ->  Bitmap Index Scan on feed_user_id  (cost=0.00..16203.57 rows=681933 width=0) (actual time=19919.512..19919.512 rows=616900 loops=1)
                           Index Cond: (feed_id = 1411)
         ->  Index Scan using users_pkey on users a  (cost=0.00..6.79 rows=1 width=26) (actual time=2.073..2.074 rows=1 loops=609070)
               Index Cond: (a.user_id = subs_feed.user_id)
   ->  Index Scan using user_subscriptions_user_id_pk on user_subscriptions b  (cost=0.00..2.89 rows=1 width=33) (actual time=0.312..0.313 rows=1 loops=609070)
         Index Cond: (a.user_id = b.user_id)
 Total runtime: 1590755.918 ms
(13 rows)



This query runs almost half an hour. It is evident that nested loop is taking most of the time (approx 27 minutes).

Any tips would be very useful.

Also these table have below count:

select relname,reltuples from pg_class where relname in ('users','user_subscriptions','subs_feed');
      relname       |  reltuples
--------------------+-------------
 user_subscriptions |          3758304
  users              | 1.95481e+07
 subs_feed          | 2.96492e+07


select n_tup_ins,n_tup_upd,n_tup_del,last_vacuum,last_analyze from pg_stat_user_tables where relname='user_subscriptions';
 n_tup_ins | n_tup_upd | n_tup_del |           last_vacuum            |           last_analyze
-----------+-----------+-----------+----------------------------------+----------------------------------
  86371397 |  25865942 |         0 | 2009-12-06 23:00:36.355251+05:30 | 2009-12-06 23:00:36.355251+05:30



Thanks in advance for help ...

          

--0-1337625008-1260190858=:39912--