Re: query hangs - Mailing list pgsql-performance

2010/6/10 AI Rumman <rummandba@gmail.com>

Can anyone please tell me why the following query hangs?
This is a part of a large query.

explain
select *
from vtiger_emaildetails
inner join vtiger_vantage_email_track on vtiger_emaildetails.emailid = vtiger_vantage_email_track.mailid
left join vtiger_seactivityrel on vtiger_seactivityrel.activityid = vtiger_emaildetails.emailid

                                                       QUERY PLAN                                                       
-------------------------------------------------------------------------------------------------------------------------
 Merge Left Join  (cost=9500.30..101672.51 rows=2629549 width=506)
   Merge Cond: ("outer".emailid = "inner".activityid)
   ->  Merge Join  (cost=9500.30..11658.97 rows=88852 width=498)
         Merge Cond: ("outer".emailid = "inner".mailid)
         ->  Index Scan using vtiger_emaildetails_pkey on vtiger_emaildetails  (cost=0.00..714.40 rows=44595 width=486)
         ->  Sort  (cost=9500.30..9722.43 rows=88852 width=12)
               Sort Key: vtiger_vantage_email_track.mailid
               ->  Seq Scan on vtiger_vantage_email_track  (cost=0.00..1369.52 rows=88852 width=12)
   ->  Index Scan using seactivityrel_activityid_idx on vtiger_seactivityrel  (cost=0.00..28569.29 rows=1319776 width=8)
(9 rows)

select relname, reltuples, relpages
from pg_class
where relname in ('vtiger_emaildetails','vtiger_vantage_email_track','vtiger_seactivityrel');


          relname           |  reltuples  | relpages
----------------------------+-------------+----------
 vtiger_emaildetails        |       44595 |     1360
 vtiger_seactivityrel       | 1.31978e+06 |     6470
 vtiger_vantage_email_track |       88852 |      481
(3 rows)

Could you define what you mean by 'hangs'? Does it work or not? 

Check table pg_locks for locking issues, maybe the query is just slow but not hangs.

Notice that the query just returns 2M rows, that can be quite huge number due to your database structure, data amount and current server configuration.

regards

Szymon Guz