In response to Szymon Guz <mabewlun@xxxxxxxxx>: > 2010/4/13 Satish Burnwal (sburnwal) <sburnwal@xxxxxxxxx> > > > I have a query which is not giving me the result even after 30 minutes. I > > want to know how to detect what is going and what’s wrong ? > > > > > > > > EXPLAIN query - gives me the following: > > > > controlsmartdb=# explain select report_id, dm_ip, dm_mac, dm_user, dm_os, > > report_time, sys_name, sys_user, sys_user_domain, ss_key, login_time, > > role_id, new_vlan_id from repcopy as a where report_time = (select > > max(report_time) from repcopy as b where a.dm_ip = b.dm_ip and > > a.dm_user=b.dm_user and b.ss_key != '') and report_status = 0 and dm_user = > > 'u1'; > > > > QUERY PLAN > > > > > > ---------------------------------------------------------------------------------------------------------------------------- > > > > Seq Scan on repcopy a (cost=0.00..1630178118.35 rows=35 width=133) > > > > Filter: ((report_status = 0) AND ((dm_user)::text = 'u1'::text) AND > > (report_time = (subplan))) > > > > SubPlan > > > > -> Aggregate (cost=8151.65..8151.66 rows=1 width=8) > > > > -> Seq Scan on repcopy b (cost=0.00..8151.65 rows=1 width=8) > > > > Filter: ((($0)::text = (dm_ip)::text) AND (($1)::text = > > (dm_user)::text) AND ((ss_key)::text <> ''::text)) > > > > (6 rows) > > > > > > > > But EXPLAIN ANALYSE query hangs (is not giving me any output even after 30 > > minutes). > > > > > > > > Pg_stat_activity shows this - SELECT procpid, usename, current_query, > > query_start from pg_stat_activity: > > > > 2942 | postgres | select report_id, dm_ip, dm_mac, dm_user, dm_os, > > report_time, sys_name, sys_user, sys_user_domain, ss_key, login_time, > > role_id, new_vlan_id from repcopy as a where report_time = (select > > max(report_time) from repcopy as b where a.dm_ip = b.dm_ip and > > a.dm_user=b.dm_user and b.ss_key != '') and report_status = 0 and dm_user = > > 'u1'; | 2010-04-13 18:20:02.828623+05:30 > > > > > > > > > > > > In such a case what can I do ? > > > > > > First things that came to my mind: > > 1. Check if the query waits on some lock: add the column `waiting` to the > above query from pg_stat_activity. > 2. Run vacuum analyze on the table repcopy In addition to that, indexes on report_time, report_status, and dm_user might help. And your query is not "hung", it's just taking a LOOOOONG time. Based on the explain, it could take several hours to complete. How many rows are in repcopy? What is your vacuum schedule? Do a vacuum verbose, if the number of dead rows is very high on that table, you may benefit from doing a VACUUM FULL + REINDEX or CLUSTER on the table. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
