2010/6/10 AI Rumman <rummandba@xxxxxxxxx>
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@xxxxxxxxx> wrote:
2010/6/10 AI Rumman <rummandba@xxxxxxxxx>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.regardsSzymon 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
