Re: query hangs

2010/6/10 AI Rumman <rummandba(at)gmail(dot)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