Re: Query is stuck

2010/4/13 Satish Burnwal (sburnwal) <sburnwal(at)cisco(dot)com>

> 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

regards
Szymon Guz