From: Bill Moran <wmoran@potentialtech.com>
To: Szymon Guz <mabewlun@gmail.com>
Cc: Satish Burnwal (sburnwal) <sburnwal@cisco.com>
Cc: pgsql-general@postgresql.org
Cc: pgsql-admin@postgresql.org
Subject: Re: [GENERAL] Query is stuck
Date: Tue, 13 Apr 2010 09:12:48 -0400
Message-ID: <20100413091248.a295737b.wmoran@potentialtech.com> (raw)
In-Reply-To: <o2ne4edc9361004130603r9acb3257yb800890f447eb2ce@mail.gmail.com>
References: <3A8C969225424C4D8E6BEE65ED8552DA0119F188@XMB-BGL-41C.cisco.com>
<o2ne4edc9361004130603r9acb3257yb800890f447eb2ce@mail.gmail.com>
In response to Szymon Guz <mabewlun@gmail.com>:
> 2010/4/13 Satish Burnwal (sburnwal) <sburnwal@cisco.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
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.comhttp://people.collaborativefusion.com/~wmoran/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: wmoran@potentialtech.com, mabewlun@gmail.com, sburnwal@cisco.com, pgsql-general@postgresql.org
Subject: Re: [GENERAL] Query is stuck
In-Reply-To: <20100413091248.a295737b.wmoran@potentialtech.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox