Re: query hangs

2010/6/10 AI Rumman <rummandba(at)gmail(dot)com>

> 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
>>
>>
>
1. Make vacuum analyze on used tables.
2. Check how long it would take if you limit the number of returned rows
just to 100
3. Do you have indexes on used columns?

regards
Szymon Guz