|
What do you get when you run this? select * from pg_stat_activity where waiting='t'; From:
pgsql-admin-owner@xxxxxxxxxxxxxx [mailto:pgsql-admin-owner@xxxxxxxxxxxxxx] On
Behalf Of Satish Burnwal (sburnwal) 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 ? |
