Re: query hangs

I found only AccessShareLock in pg_locks during the query.
And the query does not return data though I have been waiting for 10 mins.

Do you have any idea ?

On Thu, Jun 10, 2010 at 5:26 PM, Szymon Guz <mabewlun(at)gmail(dot)com> wrote:

>
>
> 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
>
>