Query is stuck

This is a multi-part message in MIME format.

------_=_NextPart_001_01CADB08.FEF7E17E
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

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=3D# 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 =3D
(select max(report_time) from repcopy as b where a.dm_ip =3D b.dm_ip and
a.dm_user=3Db.dm_user and b.ss_key !=3D '') and report_status =3D 0 and
dm_user =3D 'u1';

QUERY PLAN

------------------------------------------------------------ ------------
----------------------------------------------------

Seq Scan on repcopy a (cost=3D0.00..1630178118.35 rows=3D35 =
width=3D133)

Filter: ((report_status =3D 0) AND ((dm_user)::text =3D 'u1'::text) =
AND
(report_time =3D (subplan)))

SubPlan

-> Aggregate (cost=3D8151.65..8151.66 rows=3D1 width=3D8)

-> Seq Scan on repcopy b (cost=3D0.00..8151.65 rows=3D1
width=3D8)

Filter: ((($0)::text =3D (dm_ip)::text) AND (($1)::text =
=3D
(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 =3D (select
max(report_time) from repcopy as b where a.dm_ip =3D b.dm_ip and
a.dm_user=3Db.dm_user and b.ss_key !=3D '') and report_status =3D 0 and
dm_user =3D 'u1'; | 2010-04-13 18:20:02.828623+05:30

In such a case what can I do ?

------_=_NextPart_001_01CADB08.FEF7E17E
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" =
xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" =
xmlns=3D"http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dus-ascii">
<meta name=3DGenerator content=3D"Microsoft Word 12 (filtered medium)">
<style>

</style>

</head>

<body lang=3DEN-US link=3Dblue vlink=3Dpurple>

<div class=3DSection1>

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 ? <o:p></o:p>

<o:p> </o:p>

EXPLAIN query - gives me the =
following:<o:p></o:p>

controlsmartdb=3D# 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 =3D =
(select
max(report_time) from repcopy as b where a.dm_ip =3D b.dm_ip and
a.dm_user=3Db.dm_user and b.ss_key !=3D '') and report_status =3D 0 and =
dm_user =3D
'u1';<o:p></o:p>

 &=
nbsp; &n=
bsp; &nb=
sp; &nbs=
p;
QUERY PLAN<o:p></o:p>

------------------------------------------------ -------------=
------------------------------------------------------------ ---<o:p></o:p=
>

 Seq Scan on repcopy a =
(cost=3D0.00..1630178118.35 rows=3D35
width=3D133)<o:p></o:p>

 Filter: ((report_status =3D 0) AND =
((dm_user)::text =3D
'u1'::text) AND (report_time =3D (subplan)))<o:p></o:p>

 SubPlan<o:p></o:p>

 -> Aggregate
(cost=3D8151.65..8151.66 rows=3D1 width=3D8)<o:p></o:p>


-> Seq Scan on repcopy b (cost=3D0.00..8151.65 rows=3D1 =
width=3D8)<o:p></o:p>

 &=
nbsp;
Filter: ((($0)::text =3D (dm_ip)::text) AND (($1)::text =3D =
(dm_user)::text) AND
((ss_key)::text <> ''::text))<o:p></o:p>

(6 rows)<o:p></o:p>

<o:p> </o:p>

But EXPLAIN ANALYSE query hangs (is not giving me any output =
even
after 30 minutes).<o:p></o:p>

<o:p> </o:p>

Pg_stat_activity shows this - SELECT procpid, usename, =
current_query,
query_start from pg_stat_activity:<o:p></o:p>

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 =3D (select =
max(report_time)
from repcopy as b where a.dm_ip =3D b.dm_ip and a.dm_user=3Db.dm_user =
and b.ss_key
!=3D '') and report_status =3D 0 and dm_user =3D 'u1'; | 2010-04-13
18:20:02.828623+05:30<o:p></o:p>

<o:p> </o:p>

<o:p> </o:p>

In such a case what can I do ? <o:p></o:p>

<o:p> </o:p>

</div>

</body>

</html>

------_=_NextPart_001_01CADB08.FEF7E17E--

--_000_BD69807DAE0CE44CA00A8338D0FDD08302CF97EA2Foma00cexmbx 03_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

What do you get when you run this?

select * from pg_stat_activity where waiting=3D't';

From: pgsql-admin-owner [at] postgresql.org [mailto:pgsql-admin-owner [at] postgresql=
..org] On Behalf Of Satish Burnwal (sburnwal)
Sent: Tuesday, April 13, 2010 7:58 AM
To: pgsql-general [at] postgresql.org
Cc: pgsql-admin [at] postgresql.org
Subject: [ADMIN] Query is stuck

I have a query which is not giving me the result even after 30 minutes. I w=
ant to know how to detect what is going and what's wrong ?

EXPLAIN query - gives me the following:
controlsmartdb=3D# 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, rol=
e_id, new_vlan_id from repcopy as a where report_time =3D (select max(repor=
t_time) from repcopy as b where a.dm_ip =3D b.dm_ip and a.dm_user=3Db.dm_us=
er and b.ss_key !=3D '') and report_status =3D 0 and dm_user =3D 'u1';
QUERY PLAN
------------------------------------------------------------ ---------------=
-------------------------------------------------
Seq Scan on repcopy a (cost=3D0.00..1630178118.35 rows=3D35 width=3D133)
Filter: ((report_status =3D 0) AND ((dm_user)::text =3D 'u1'::text) AND =
(report_time =3D (subplan)))
SubPlan
-> Aggregate (cost=3D8151.65..8151.66 rows=3D1 width=3D8)
-> Seq Scan on repcopy b (cost=3D0.00..8151.65 rows=3D1 width=
=3D8)
Filter: ((($0)::text =3D (dm_ip)::text) AND (($1)::text =
=3D (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_t=
ime, sys_name, sys_user, sys_user_domain, ss_key, login_time, role_id, new=
_vlan_id from repcopy as a where report_time =3D (select max(report_time) f=
rom repcopy as b where a.dm_ip =3D b.dm_ip and a.dm_user=3Db.dm_user and b.=
ss_key !=3D '') and report_status =3D 0 and dm_user =3D 'u1'; | 2010-04-13 =
18:20:02.828623+05:30

In such a case what can I do ?

--_000_BD69807DAE0CE44CA00A8338D0FDD08302CF97EA2Foma00cexmbx 03_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-micr=
osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:x=3D"urn:schemas-microsoft-com:office:excel" xmlns:p=3D"urn:schemas-m=
icrosoft-com:office:powerpoint" xmlns:a=3D"urn:schemas-microsoft-com:office=
:access" xmlns:dt=3D"uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:s=3D"=
uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:rs=3D"urn:schemas-microsof=
t-com:rowset" xmlns:z=3D"#RowsetSchema" xmlns:b=3D"urn:schemas-microsoft-co=
m:office:publisher" xmlns:ss=3D"urn:schemas-microsoft-com:office:spreadshee=
t" xmlns:c=3D"urn:schemas-microsoft-com:office:component:spread sheet" xmlns=
:odc=3D"urn:schemas-microsoft-com:office:odc" xmlns:oa=3D"urn:schemas-micro=
soft-com:office:activation" xmlns:html=3D"http://www.w3.org/TR/REC-html40" =
xmlns:q=3D"http://schemas.xmlsoap.org/soap/envelope/" xmlns:rtc=3D"http://m=
icrosoft.com/officenet/conferencing" xmlns:D=3D"DAV:" xmlns:Repl=3D"http://=
schemas.microsoft.com/repl/" xmlns:mt=3D"http://schemas.microsoft.com/share=
point/soap/meetings/" xmlns:x2=3D"http://schemas.microsoft.com/office/excel=
/2003/xml" xmlns:ppda=3D"http://www.passport.com/NameSpace.xsd" xmlns:ois=
=3D"http://schemas.microsoft.com/sharepoint/soap/ois/" xmlns:dir=3D"http://=
schemas.microsoft.com/sharepoint/soap/directory/" xmlns:ds=3D"http://www.w3=
..org/2000/09/xmldsig#" xmlns:dsp=3D"http://schemas.microsoft.com/sharepoint=
/dsp" xmlns:udc=3D"http://schemas.microsoft.com/data/udc" xmlns:xsd=3D"http=
://www.w3.org/2001/XMLSchema" xmlns:sub=3D"http://schemas.microsoft.com/sha=
repoint/soap/2002/1/alerts/" xmlns:ec=3D"http://www.w3.org/2001/04/xmlenc#"=
xmlns:sp=3D"http://schemas.microsoft.com/sharepoint/" xmlns:sps=3D"http://=
schemas.microsoft.com/sharepoint/soap/" xmlns:xsi=3D"http://www.w3.org/2001=
/XMLSchema-instance" xmlns:udcs=3D"http://schemas.microsoft.com/data/udc/so=
ap" xmlns:udcxf=3D"http://schemas.microsoft.com/data/udc/xmlfile " xmlns:udc=
p2p=3D"http://schemas.microsoft.com/data/udc/parttopart" xmlns:wf=3D"http:/=
/schemas.microsoft.com/sharepoint/soap/workflow/" xmlns:dsss=3D"http://sche=
mas.microsoft.com/office/2006/digsig-setup" xmlns:dssi=3D"http://schemas.mi=
crosoft.com/office/2006/digsig" xmlns:mdssi=3D"http://schemas.openxmlformat=
s.org/package/2006/digital-signature" xmlns:mver=3D"http://schemas.openxmlf=
ormats.org/markup-compatibility/2006" xmlns:m=3D"http://schemas.microsoft.c=
om/office/2004/12/omml" xmlns:mrels=3D"http://schemas.openxmlformats.org/pa=
ckage/2006/relationships" xmlns:spwp=3D"http://microsoft.com/sharepoint/web=
partpages" xmlns:ex12t=3D"http://schemas.microsoft.com/exchange/service s/20=
06/types" xmlns:ex12m=3D"http://schemas.microsoft.com/exchange/service s/200=
6/messages" xmlns:pptsl=3D"http://schemas.microsoft.com/sharepoint/soap/ Sli=
deLibrary/" xmlns:spsl=3D"http://microsoft.com/webservices/SharePointPor tal=
Server/PublishedLinksService" xmlns:Z=3D"urn:schemas-microsoft-com:" xmlns:=
st=3D"" xmlns=3D"http://www.w3.org/TR/REC-html40">

<head>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; charset=3Dus-ascii"=
>
<meta name=3DGenerator content=3D"Microsoft Word 12 (filtered medium)">
<style>

</style>

</head>

<body lang=3DEN-US link=3Dblue vlink=3Dpurple>

<div class=3DSection1>

What do you get when you run this?<o:p></o:p>

<o:p> </o:p>

select * from pg_stat_activity where waiting=3D't';<o:p></o:p>=


<o:p> </o:p>

<o:p> </o:p>

<o:p> </o:p>

<div>

<div style=3D'border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in =
0in 0in'>

From:
pgsql-admin-owner [at] postgresql.org [mailto:pgsql-admin-owner [at] postgresql.org] =
On
Behalf Of Satish Burnwal (sburnwal) 
Sent: Tuesday, April 13, 2010 7:58 AM 
To: pgsql-general [at] postgresql.org 
Cc: pgsql-admin [at] postgresql.org 
Subject: [ADMIN] Query is stuck<o:p></o:p>

</div>

</div>

<o:p> </o:p>

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 =
? <o:p></o:p>

<o:p> </o:p>

EXPLAIN query - gives me the following:<o:p></o:p>

controlsmartdb=3D# 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 =3D (s=
elect
max(report_time) from repcopy as b where a.dm_ip =3D b.dm_ip and
a.dm_user=3Db.dm_user and b.ss_key !=3D '') and report_status =3D 0 and dm_=
user =3D
'u1';<o:p></o:p>

 &nb=
sp; =
 &nb=
sp; =

QUERY PLAN<o:p></o:p>

------------------------------------------------ ---------------=
------------------------------------------------------------ -<o:p></o:p></s=
pan>

 Seq Scan on repcopy a (cost=3D0.00..1630178118.35 r=
ows=3D35
width=3D133)<o:p></o:p>

 Filter: ((report_status =3D 0) AND ((dm_user)::tex=
t =3D
'u1'::text) AND (report_time =3D (subplan)))<o:p></o:p>

 SubPlan<o:p></o:p>

 -> Aggregate
(cost=3D8151.65..8151.66 rows=3D1 width=3D8)<o:p></o:p>


-> Seq Scan on repcopy b (cost=3D0.00..8151.65 rows=3D1 widt=
h=3D8)<o:p></o:p>

 &nb=
sp;
Filter: ((($0)::text =3D (dm_ip)::text) AND (($1)::text =3D (dm_user)::text=
) AND
((ss_key)::text <> ''::text))<o:p></o:p>

(6 rows)<o:p></o:p>

<o:p> </o:p>

But EXPLAIN ANALYSE query hangs (is not giving me any output ev=
en
after 30 minutes).<o:p></o:p>

<o:p> </o:p>

Pg_stat_activity shows this - SELECT procpid, usename,
current_query, query_start from pg_stat_activity:<o:p></o:p>

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 =3D (select
max(report_time) from repcopy as b where a.dm_ip =3D b.dm_ip and
a.dm_user=3Db.dm_user and b.ss_key !=3D '') and report_status =3D 0 and dm_=
user =3D
'u1'; | 2010-04-13 18:20:02.828623+05:30<o:p></o:p>

<o:p> </o:p>

<o:p> </o:p>

In such a case what can I do ? <o:p></o:p>

<o:p> </o:p>

</div>

</body>

</html>

--_000_BD69807DAE0CE44CA00A8338D0FDD08302CF97EA2Foma00cexmbx 03_--

--001485f1dc1856da2604841de257
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

2010/4/13 Satish Burnwal (sburnwal) <sburnwal [at] 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=E2=80=99s wrong ?
>
>
>
> EXPLAIN query - gives me the following:
>
> controlsmartdb=3D# explain select report_id, dm_ip, dm_mac, dm_user, dm_o=
s,
> 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 =3D (select
> max(report_time) from repcopy as b where a.dm_ip =3D b.dm_ip and
> a.dm_user=3Db.dm_user and b.ss_key !=3D '') and report_status =3D 0 and d=
m_user =3D
> 'u1';
>
> QUERY PLAN
>
>
> ------------------------------------------------------------ -------------=
---------------------------------------------------
>
> Seq Scan on repcopy a (cost=3D0.00..1630178118.35 rows=3D35 width=3D133=
)
>
> Filter: ((report_status =3D 0) AND ((dm_user)::text =3D 'u1'::text) AN=
D
> (report_time =3D (subplan)))
>
> SubPlan
>
> -> Aggregate (cost=3D8151.65..8151.66 rows=3D1 width=3D8)
>
> -> Seq Scan on repcopy b (cost=3D0.00..8151.65 rows=3D1 widt=
h=3D8)
>
> Filter: ((($0)::text =3D (dm_ip)::text) AND (($1)::text =
=3D
> (dm_user)::text) AND ((ss_key)::text <> ''::text))
>
> (6 rows)
>
>
>
> But EXPLAIN ANALYSE query hangs (is not giving me any output even after 3=
0
> 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 =3D (select
> max(report_time) from repcopy as b where a.dm_ip =3D b.dm_ip and
> a.dm_user=3Db.dm_user and b.ss_key !=3D '') and report_status =3D 0 and d=
m_user =3D
> '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

--001485f1dc1856da2604841de257
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

 <div class=3D"gmail_quote">2010/4/13 Satish Burnwal (sburnwal) <spa=
n dir=3D"ltr"><<a href=3D"mailto:sburnwal [at] cisco.com">sburnwal [at] cisco.com<=
/a>> <blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .=
8ex;border-left:1px #ccc solid;padding-left:1ex;">

<div lang=3D"EN-US" link=3D"blue" vlink=3D"purple">

<div>

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=E2=80=99s wron=
g ? 

=C2=A0</=
span>

EXPLAIN =
query - gives me the following:

controls=
martdb=3D# explain select report_id, dm_ip, dm_mac, dm_user,
dm_os, report_time, sys_name,=C2=A0 sys_user, sys_user_domain, ss_key,
login_time, role_id, new_vlan_id from repcopy as a where report_time =3D (s=
elect
max(report_time) from repcopy as b where a.dm_ip =3D b.dm_ip and
a.dm_user=3Db.dm_user and b.ss_key !=3D '') and report_status =3D 0=
and dm_user =3D
'u1';

=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2 =A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2 =A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0
QUERY PLAN

--------=
------------------------------------------------------------ ---------------=
-----------------------------------------

=C2=A0Se=
q Scan on repcopy a=C2=A0 (cost=3D0.00..1630178118.35 rows=3D35
width=3D133)

=C2=A0=
=C2=A0 Filter: ((report_status =3D 0) AND ((dm_user)::text =3D
'u1'::text) AND (report_time =3D (subplan)))

=C2=A0=
=C2=A0 SubPlan

=C2=A0=
=C2=A0=C2=A0=C2=A0 ->=C2=A0 Aggregate=C2=A0
(cost=3D8151.65..8151.66 rows=3D1 width=3D8)

=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0
->=C2=A0 Seq Scan on repcopy b=C2=A0 (cost=3D0.00..8151.65 rows=3D1 widt=
h=3D8)

=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0
Filter: ((($0)::text =3D (dm_ip)::text) AND (($1)::text =3D (dm_user)::text=
) AND
((ss_key)::text <> ''::text))

(6 rows)=


=C2=A0</=
span>

But EXPL=
AIN ANALYSE query hangs (is not giving me any output even
after 30 minutes).

=C2=A0</=
span>

Pg_stat_=
activity shows this - SELECT procpid, usename, current_query,
query_start from pg_stat_activity:

2942 | p=
ostgres | select report_id, dm_ip, dm_mac, dm_user, dm_os,
report_time, sys_name,=C2=A0 sys_user, sys_user_domain, ss_key, login_time,
role_id, new_vlan_id from repcopy as a where report_time =3D (select max(re=
port_time)
from repcopy as b where a.dm_ip =3D b.dm_ip and a.dm_user=3Db.dm_user and b=
..ss_key
!=3D '') and report_status =3D 0 and dm_user =3D 'u1'; | 20=
10-04-13
18:20:02.828623+05:30

=C2=A0</=
span>

=C2=A0</=
span>

In such =
a case what can I do ? 

<=
br></div></div></blockquote><div>First things that came to my mi=
nd:</div><div> </div><div>1. Check if the query waits on some lock: add =
the column `waiting`<span class=3D"Apple-style-span" style=3D"font-family: =
Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courie=
r, monospace; font-size: 12px; border-collapse: collapse; line-height: 13px=
; white-space: pre; -webkit-border-horizontal-spacing: 2px; -webkit-border-=
vertical-spacing: 2px; "><span class=3D"Apple-style-span" style=3D"border-c=
ollapse: separate; font-family: arial; line-height: normal; white-space: no=
rmal; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spaci=
ng: 0px; font-size: small; ">=C2=A0to the above query from pg_stat_activity=
..</div>
<div><span class=3D"Apple-style-span" style=3D"font-family: Consolas, '=
Bitstream Vera Sans Mono', 'Courier New', Courier, monospace; f=
ont-size: 12px; border-collapse: collapse; line-height: 13px; white-space: =
pre; -webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacin=
g: 2px; "><span class=3D"Apple-style-span" style=3D"border-collapse: separa=
te; font-family: arial; line-height: normal; white-space: normal; -webkit-b=
order-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; font-s=
ize: small; ">2. Run vacuum analyze on the table repcopy</div=
>
<div><span class=3D"Apple-style-span" style=3D"font-family: Consolas, '=
Bitstream Vera Sans Mono', 'Courier New', Courier, monospace; f=
ont-size: 12px; border-collapse: collapse; line-height: 13px; white-space: =
pre; -webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacin=
g: 2px; "><span class=3D"Apple-style-span" style=3D"border-collapse: separa=
te; font-family: arial; line-height: normal; white-space: normal; -webkit-b=
order-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; font-s=
ize: small; "> 
</div><div><span class=3D"Apple-style-span" style=3D"font-fam=
ily: Consolas, 'Bitstream Vera Sans Mono', 'Courier New', C=
ourier, monospace; font-size: 12px; border-collapse: collapse; line-height:=
13px; white-space: pre; -webkit-border-horizontal-spacing: 2px; -webkit-bo=
rder-vertical-spacing: 2px; "><span class=3D"Apple-style-span" style=3D"bor=
der-collapse: separate; font-family: arial; line-height: normal; white-spac=
e: normal; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-=
spacing: 0px; font-size: small; "> 
</div><div><span class=3D"Apple-style-span" style=3D"font-fam=
ily: Consolas, 'Bitstream Vera Sans Mono', 'Courier New', C=
ourier, monospace; font-size: 12px; border-collapse: collapse; line-height:=
13px; white-space: pre; -webkit-border-horizontal-spacing: 2px; -webkit-bo=
rder-vertical-spacing: 2px; "><span class=3D"Apple-style-span" style=3D"bor=
der-collapse: separate; font-family: arial; line-height: normal; white-spac=
e: normal; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-=
spacing: 0px; font-size: small; ">regards</div>
<div><span class=3D"Apple-style-span" style=3D"font-family: Consolas, '=
Bitstream Vera Sans Mono', 'Courier New', Courier, monospace; f=
ont-size: 12px; border-collapse: collapse; line-height: 13px; white-space: =
pre; -webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacin=
g: 2px; "><span class=3D"Apple-style-span" style=3D"border-collapse: separa=
te; font-family: arial; line-height: normal; white-space: normal; -webkit-b=
order-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; font-s=
ize: small; ">Szymon Guz</div>
<div><span class=3D"Apple-style-span" style=3D"font-family: Consolas, '=
Bitstream Vera Sans Mono', 'Courier New', Courier, monospace; f=
ont-size: 12px; border-collapse: collapse; line-height: 13px; white-space: =
pre; -webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacin=
g: 2px; "><span class=3D"Apple-style-span" style=3D"border-collapse: separa=
te; font-family: arial; line-height: normal; white-space: normal; -webkit-b=
order-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; font-s=
ize: small; "> 
</div><div><span class=3D"Apple-style-span" style=3D"font-fam=
ily: Consolas, 'Bitstream Vera Sans Mono', 'Courier New', C=
ourier, monospace; font-size: 12px; border-collapse: collapse; line-height:=
13px; white-space: pre; -webkit-border-horizontal-spacing: 2px; -webkit-bo=
rder-vertical-spacing: 2px; "><span class=3D"Apple-style-span" style=3D"bor=
der-collapse: separate; font-family: arial; line-height: normal; white-spac=
e: normal; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-=
spacing: 0px; font-size: small; "> 
</div></div>

--001485f1dc1856da2604841de257--

This is a multi-part message in MIME format.

------_=_NextPart_001_01CADB09.C55D6E88
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

controlsmartdb=3D# select * from pg_stat_activity where waiting=3D't';

ERROR: column "waiting" does not exist

From: Plugge, Joe R. [mailto:JRPlugge [at] west.com]
Sent: Tuesday, April 13, 2010 6:32 PM
To: Satish Burnwal (sburnwal); pgsql-general [at] postgresql.org
Cc: pgsql-admin [at] postgresql.org
Subject: RE: Query is stuck

What do you get when you run this?

select * from pg_stat_activity where waiting=3D't';

From: pgsql-admin-owner [at] postgresql.org
[mailto:pgsql-admin-owner [at] postgresql.org] On Behalf Of Satish Burnwal
(sburnwal)
Sent: Tuesday, April 13, 2010 7:58 AM
To: pgsql-general [at] postgresql.org
Cc: pgsql-admin [at] postgresql.org
Subject: [ADMIN] Query is stuck

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=3D# 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 =3D
(select max(report_time) from repcopy as b where a.dm_ip =3D b.dm_ip and
a.dm_user=3Db.dm_user and b.ss_key !=3D '') and report_status =3D 0 and
dm_user =3D 'u1';

QUERY PLAN

------------------------------------------------------------ ------------
----------------------------------------------------

Seq Scan on repcopy a (cost=3D0.00..1630178118.35 rows=3D35 =
width=3D133)

Filter: ((report_status =3D 0) AND ((dm_user)::text =3D 'u1'::text) =
AND
(report_time =3D (subplan)))

SubPlan

-> Aggregate (cost=3D8151.65..8151.66 rows=3D1 width=3D8)

-> Seq Scan on repcopy b (cost=3D0.00..8151.65 rows=3D1
width=3D8)

Filter: ((($0)::text =3D (dm_ip)::text) AND (($1)::text =
=3D
(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 =3D (select
max(report_time) from repcopy as b where a.dm_ip =3D b.dm_ip and
a.dm_user=3Db.dm_user and b.ss_key !=3D '') and report_status =3D 0 and
dm_user =3D 'u1'; | 2010-04-13 18:20:02.828623+05:30

In such a case what can I do ?

------_=_NextPart_001_01CADB09.C55D6E88
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" =
xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:x=3D"urn:schemas-microsoft-com:office:excel" =
xmlns:p=3D"urn:schemas-microsoft-com:office:powerpoint" =
xmlns:a=3D"urn:schemas-microsoft-com:office:access" =
xmlns:dt=3D"uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" =
xmlns:s=3D"uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" =
xmlns:rs=3D"urn:schemas-microsoft-com:rowset" xmlns:z=3D"#RowsetSchema" =
xmlns:b=3D"urn:schemas-microsoft-com:office:publisher" =
xmlns:ss=3D"urn:schemas-microsoft-com:office:spreadsheet" =
xmlns:c=3D"urn:schemas-microsoft-com:office:component:spread sheet" =
xmlns:odc=3D"urn:schemas-microsoft-com:office:odc" =
xmlns:oa=3D"urn:schemas-microsoft-com:office:activation" =
xmlns:html=3D"http://www.w3.org/TR/REC-html40" =
xmlns:q=3D"http://schemas.xmlsoap.org/soap/envelope/" =
xmlns:rtc=3D"http://microsoft.com/officenet/conferencing" =
xmlns:D=3D"DAV:" xmlns:Repl=3D"http://schemas.microsoft.com/repl/" =
xmlns:mt=3D"http://schemas.microsoft.com/sharepoint/soap/mee tings/" =
xmlns:x2=3D"http://schemas.microsoft.com/office/excel/2003/x ml" =
xmlns:ppda=3D"http://www.passport.com/NameSpace.xsd" =
xmlns:ois=3D"http://schemas.microsoft.com/sharepoint/soap/oi s/" =
xmlns:dir=3D"http://schemas.microsoft.com/sharepoint/soap/di rectory/" =
xmlns:ds=3D"http://www.w3.org/2000/09/xmldsig#" =
xmlns:dsp=3D"http://schemas.microsoft.com/sharepoint/dsp" =
xmlns:udc=3D"http://schemas.microsoft.com/data/udc" =
xmlns:xsd=3D"http://www.w3.org/2001/XMLSchema" =
xmlns:sub=3D"http://schemas.microsoft.com/sharepoint/soap/20 02/1/alerts/"=
xmlns:ec=3D"http://www.w3.org/2001/04/xmlenc#" =
xmlns:sp=3D"http://schemas.microsoft.com/sharepoint/" =
xmlns:sps=3D"http://schemas.microsoft.com/sharepoint/soap/" =
xmlns:xsi=3D"http://www.w3.org/2001/XMLSchema-instance" =
xmlns:udcs=3D"http://schemas.microsoft.com/data/udc/soap" =
xmlns:udcxf=3D"http://schemas.microsoft.com/data/udc/xmlfile " =
xmlns:udcp2p=3D"http://schemas.microsoft.com/data/udc/partto part" =
xmlns:wf=3D"http://schemas.microsoft.com/sharepoint/soap/wor kflow/" =
xmlns:dsss=3D"http://schemas.microsoft.com/office/2006/digsi g-setup" =
xmlns:dssi=3D"http://schemas.microsoft.com/office/2006/digsi g" =
xmlns:mdssi=3D"http://schemas.openxmlformats.org/package/200 6/digital-sig=
nature" =
xmlns:mver=3D"http://schemas.openxmlformats.org/markup-compa tibility/2006=
" xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" =
xmlns:mrels=3D"http://schemas.openxmlformats.org/package/200 6/relationshi=
ps" xmlns:spwp=3D"http://microsoft.com/sharepoint/webpartpages" =
xmlns:ex12t=3D"http://schemas.microsoft.com/exchange/service s/2006/types"=
=
xmlns:ex12m=3D"http://schemas.microsoft.com/exchange/service s/2006/messag=
es" =
xmlns:pptsl=3D"http://schemas.microsoft.com/sharepoint/soap/ SlideLibrary/=
" =
xmlns:spsl=3D"http://microsoft.com/webservices/SharePointPor talServer/Pub=
lishedLinksService" xmlns:Z=3D"urn:schemas-microsoft-com:" =
xmlns:st=3D"" xmlns=3D"http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dus-ascii">
<meta name=3DGenerator content=3D"Microsoft Word 12 (filtered medium)">
<style>

</style>

</head>

<body lang=3DEN-US link=3Dblue vlink=3Dpurple>

<div class=3DSection1>

controlsmartdb=3D# select * from pg_stat_activity where =
waiting=3D't';<o:p></o:p>

ERROR: column "waiting" does not =
exist<o:p></o:p>

<o:p> </o:p>

<div>

<div style=3D'border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt =
0in 0in 0in'>

From:=
 Plugge, =
Joe R.
[mailto:JRPlugge [at] west.com] 
Sent: Tuesday, April 13, 2010 6:32 PM 
To: Satish Burnwal (sburnwal); pgsql-general [at] postgresql.org 
Cc: pgsql-admin [at] postgresql.org 
Subject: RE: Query is stuck<o:p></o:p>

</div>

</div>

<o:p> </o:p>

What do you get when you run this?<o:p></o:p>

<o:p> </o:p>

select * from pg_stat_activity where =
waiting=3D't';<o:p></o:p>

<o:p> </o:p>

<o:p> </o:p>

<o:p> </o:p>

<div>

<div style=3D'border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt =
0in 0in 0in'>

From:=

pgsql-admin-owner [at] postgresql.org =
[mailto:pgsql-admin-owner [at] postgresql.org] On
Behalf Of Satish Burnwal (sburnwal) 
Sent: Tuesday, April 13, 2010 7:58 AM 
To: pgsql-general [at] postgresql.org 
Cc: pgsql-admin [at] postgresql.org 
Subject: [ADMIN] Query is stuck<o:p></o:p>

</div>

</div>

<o:p> </o:p>

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 ? <o:p></o:p>

<o:p> </o:p>

EXPLAIN query - gives me the =
following:<o:p></o:p>

controlsmartdb=3D# 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 =3D (select
max(report_time) from repcopy as b where a.dm_ip =3D b.dm_ip and
a.dm_user=3Db.dm_user and b.ss_key !=3D '') and report_status =3D 0 and =
dm_user =3D
'u1';<o:p></o:p>

 &=
nbsp; &n=
bsp; &nb=
sp; &nbs=
p;
QUERY PLAN<o:p></o:p>

------------------------------------------------ -------------=
------------------------------------------------------------ ---<o:p></o:p=
>

 Seq Scan on repcopy a =
(cost=3D0.00..1630178118.35 rows=3D35
width=3D133)<o:p></o:p>

 Filter: ((report_status =3D 0) AND =
((dm_user)::text =3D
'u1'::text) AND (report_time =3D (subplan)))<o:p></o:p>

 SubPlan<o:p></o:p>

 -> Aggregate
(cost=3D8151.65..8151.66 rows=3D1 width=3D8)<o:p></o:p>


-> Seq Scan on repcopy b (cost=3D0.00..8151.65 rows=3D1 =
width=3D8)<o:p></o:p>

 &=
nbsp;
Filter: ((($0)::text =3D (dm_ip)::text) AND (($1)::text =3D =
(dm_user)::text) AND
((ss_key)::text <> ''::text))<o:p></o:p>

(6 rows)<o:p></o:p>

<o:p> </o:p>

But EXPLAIN ANALYSE query hangs (is not giving me any output =
even
after 30 minutes).<o:p></o:p>

<o:p> </o:p>

Pg_stat_activity shows this - SELECT procpid, usename,
current_query, query_start from pg_stat_activity:<o:p></o:p>

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 =3D (select
max(report_time) from repcopy as b where a.dm_ip =3D b.dm_ip and
a.dm_user=3Db.dm_user and b.ss_key !=3D '') and report_status =3D 0 and =
dm_user =3D
'u1'; | 2010-04-13 18:20:02.828623+05:30<o:p></o:p>

<o:p> </o:p>

<o:p> </o:p>

In such a case what can I do ? <o:p></o:p>

<o:p> </o:p>

</div>

</body>

</html>

------_=_NextPart_001_01CADB09.C55D6E88--

--_000_BD69807DAE0CE44CA00A8338D0FDD08302CF97EA35oma00cexmbx 03_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

What version of postgres are you on?

From: Satish Burnwal (sburnwal) [mailto:sburnwal [at] cisco.com]
Sent: Tuesday, April 13, 2010 8:04 AM
To: Plugge, Joe R.; pgsql-general [at] postgresql.org
Cc: pgsql-admin [at] postgresql.org
Subject: RE: Query is stuck

controlsmartdb=3D# select * from pg_stat_activity where waiting=3D't';
ERROR: column "waiting" does not exist

From: Plugge, Joe R. [mailto:JRPlugge [at] west.com]
Sent: Tuesday, April 13, 2010 6:32 PM
To: Satish Burnwal (sburnwal); pgsql-general [at] postgresql.org
Cc: pgsql-admin [at] postgresql.org
Subject: RE: Query is stuck

What do you get when you run this?

select * from pg_stat_activity where waiting=3D't';

From: pgsql-admin-owner [at] postgresql.org [mailto:pgsql-admin-owner [at] postgresql=
..org] On Behalf Of Satish Burnwal (sburnwal)
Sent: Tuesday, April 13, 2010 7:58 AM
To: pgsql-general [at] postgresql.org
Cc: pgsql-admin [at] postgresql.org
Subject: [ADMIN] Query is stuck

I have a query which is not giving me the result even after 30 minutes. I w=
ant to know how to detect what is going and what's wrong ?

EXPLAIN query - gives me the following:
controlsmartdb=3D# 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, rol=
e_id, new_vlan_id from repcopy as a where report_time =3D (select max(repor=
t_time) from repcopy as b where a.dm_ip =3D b.dm_ip and a.dm_user=3Db.dm_us=
er and b.ss_key !=3D '') and report_status =3D 0 and dm_user =3D 'u1';
QUERY PLAN
------------------------------------------------------------ ---------------=
-------------------------------------------------
Seq Scan on repcopy a (cost=3D0.00..1630178118.35 rows=3D35 width=3D133)
Filter: ((report_status =3D 0) AND ((dm_user)::text =3D 'u1'::text) AND =
(report_time =3D (subplan)))
SubPlan
-> Aggregate (cost=3D8151.65..8151.66 rows=3D1 width=3D8)
-> Seq Scan on repcopy b (cost=3D0.00..8151.65 rows=3D1 width=
=3D8)
Filter: ((($0)::text =3D (dm_ip)::text) AND (($1)::text =
=3D (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_t=
ime, sys_name, sys_user, sys_user_domain, ss_key, login_time, role_id, new=
_vlan_id from repcopy as a where report_time =3D (select max(report_time) f=
rom repcopy as b where a.dm_ip =3D b.dm_ip and a.dm_user=3Db.dm_user and b.=
ss_key !=3D '') and report_status =3D 0 and dm_user =3D 'u1'; | 2010-04-13 =
18:20:02.828623+05:30

In such a case what can I do ?

--_000_BD69807DAE0CE44CA00A8338D0FDD08302CF97EA35oma00cexmbx 03_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-micr=
osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:x=3D"urn:schemas-microsoft-com:office:excel" xmlns:p=3D"urn:schemas-m=
icrosoft-com:office:powerpoint" xmlns:a=3D"urn:schemas-microsoft-com:office=
:access" xmlns:dt=3D"uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:s=3D"=
uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:rs=3D"urn:schemas-microsof=
t-com:rowset" xmlns:z=3D"#RowsetSchema" xmlns:b=3D"urn:schemas-microsoft-co=
m:office:publisher" xmlns:ss=3D"urn:schemas-microsoft-com:office:spreadshee=
t" xmlns:c=3D"urn:schemas-microsoft-com:office:component:spread sheet" xmlns=
:odc=3D"urn:schemas-microsoft-com:office:odc" xmlns:oa=3D"urn:schemas-micro=
soft-com:office:activation" xmlns:html=3D"http://www.w3.org/TR/REC-html40" =
xmlns:q=3D"http://schemas.xmlsoap.org/soap/envelope/" xmlns:rtc=3D"http://m=
icrosoft.com/officenet/conferencing" xmlns:D=3D"DAV:" xmlns:Repl=3D"http://=
schemas.microsoft.com/repl/" xmlns:mt=3D"http://schemas.microsoft.com/share=
point/soap/meetings/" xmlns:x2=3D"http://schemas.microsoft.com/office/excel=
/2003/xml" xmlns:ppda=3D"http://www.passport.com/NameSpace.xsd" xmlns:ois=
=3D"http://schemas.microsoft.com/sharepoint/soap/ois/" xmlns:dir=3D"http://=
schemas.microsoft.com/sharepoint/soap/directory/" xmlns:ds=3D"http://www.w3=
..org/2000/09/xmldsig#" xmlns:dsp=3D"http://schemas.microsoft.com/sharepoint=
/dsp" xmlns:udc=3D"http://schemas.microsoft.com/data/udc" xmlns:xsd=3D"http=
://www.w3.org/2001/XMLSchema" xmlns:sub=3D"http://schemas.microsoft.com/sha=
repoint/soap/2002/1/alerts/" xmlns:ec=3D"http://www.w3.org/2001/04/xmlenc#"=
xmlns:sp=3D"http://schemas.microsoft.com/sharepoint/" xmlns:sps=3D"http://=
schemas.microsoft.com/sharepoint/soap/" xmlns:xsi=3D"http://www.w3.org/2001=
/XMLSchema-instance" xmlns:udcs=3D"http://schemas.microsoft.com/data/udc/so=
ap" xmlns:udcxf=3D"http://schemas.microsoft.com/data/udc/xmlfile " xmlns:udc=
p2p=3D"http://schemas.microsoft.com/data/udc/parttopart" xmlns:wf=3D"http:/=
/schemas.microsoft.com/sharepoint/soap/workflow/" xmlns:dsss=3D"http://sche=
mas.microsoft.com/office/2006/digsig-setup" xmlns:dssi=3D"http://schemas.mi=
crosoft.com/office/2006/digsig" xmlns:mdssi=3D"http://schemas.openxmlformat=
s.org/package/2006/digital-signature" xmlns:mver=3D"http://schemas.openxmlf=
ormats.org/markup-compatibility/2006" xmlns:m=3D"http://schemas.microsoft.c=
om/office/2004/12/omml" xmlns:mrels=3D"http://schemas.openxmlformats.org/pa=
ckage/2006/relationships" xmlns:spwp=3D"http://microsoft.com/sharepoint/web=
partpages" xmlns:ex12t=3D"http://schemas.microsoft.com/exchange/service s/20=
06/types" xmlns:ex12m=3D"http://schemas.microsoft.com/exchange/service s/200=
6/messages" xmlns:pptsl=3D"http://schemas.microsoft.com/sharepoint/soap/ Sli=
deLibrary/" xmlns:spsl=3D"http://microsoft.com/webservices/SharePointPor tal=
Server/PublishedLinksService" xmlns:Z=3D"urn:schemas-microsoft-com:" xmlns:=
st=3D"" xmlns=3D"http://www.w3.org/TR/REC-html40">

<head>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; charset=3Dus-ascii"=
>
<meta name=3DGenerator content=3D"Microsoft Word 12 (filtered medium)">
<style>

</style>

</head>

<body lang=3DEN-US link=3Dblue vlink=3Dpurple>

<div class=3DSection1>

What version of postgres are you on?<o:p></o:p>

<o:p> </o:p>

<div>

<div style=3D'border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in =
0in 0in'>

From: Satish Burnwa=
l
(sburnwal) [mailto:sburnwal [at] cisco.com] 
Sent: Tuesday, April 13, 2010 8:04 AM 
To: Plugge, Joe R.; pgsql-general [at] postgresql.org 
Cc: pgsql-admin [at] postgresql.org 
Subject: RE: Query is stuck<o:p></o:p>

</div>

</div>

<o:p> </o:p>

controlsmartdb=3D# select * from pg_stat_activity where waiting=
=3D't';<o:p></o:p>

ERROR: column "waiting" does not exist<o:p></o:=
p>

<o:p> </o:p>

<div>

<div style=3D'border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in =
0in 0in'>

From: Plugge, Joe R=
..
[mailto:JRPlugge [at] west.com] 
Sent: Tuesday, April 13, 2010 6:32 PM 
To: Satish Burnwal (sburnwal); pgsql-general [at] postgresql.org 
Cc: pgsql-admin [at] postgresql.org 
Subject: RE: Query is stuck<o:p></o:p>

</div>

</div>

<o:p> </o:p>

What do you get when you run this?<o:p></o:p>

<o:p> </o:p>

select * from pg_stat_activity where waiting=3D't';<o:p></o:p>=


<o:p> </o:p>

<o:p> </o:p>

<o:p> </o:p>

<div>

<div style=3D'border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in =
0in 0in'>

From:
pgsql-admin-owner [at] postgresql.org [mailto:pgsql-admin-owner [at] postgresql.org] =
On
Behalf Of Satish Burnwal (sburnwal) 
Sent: Tuesday, April 13, 2010 7:58 AM 
To: pgsql-general [at] postgresql.org 
Cc: pgsql-admin [at] postgresql.org 
Subject: [ADMIN] Query is stuck<o:p></o:p>

</div>

</div>

<o:p> </o:p>

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 =
? <o:p></o:p>

<o:p> </o:p>

EXPLAIN query - gives me the following:<o:p></o:p>

controlsmartdb=3D# 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 =3D (s=
elect
max(report_time) from repcopy as b where a.dm_ip =3D b.dm_ip and a.dm_user=
=3Db.dm_user
and b.ss_key !=3D '') and report_status =3D 0 and dm_user =3D 'u1';<o:p></o=
:p>

 &nb=
sp; =
 &nb=
sp; =

QUERY PLAN<o:p></o:p>

------------------------------------------------ ---------------=
------------------------------------------------------------ -<o:p></o:p></s=
pan>

 Seq Scan on repcopy a (cost=3D0.00..1630178118.35 r=
ows=3D35
width=3D133)<o:p></o:p>

 Filter: ((report_status =3D 0) AND ((dm_user)::tex=
t =3D
'u1'::text) AND (report_time =3D (subplan)))<o:p></o:p>

 SubPlan<o:p></o:p>

 -> Aggregate
(cost=3D8151.65..8151.66 rows=3D1 width=3D8)<o:p></o:p>


-> Seq Scan on repcopy b (cost=3D0.00..8151.65 rows=3D1 widt=
h=3D8)<o:p></o:p>

 &nb=
sp;
Filter: ((($0)::text =3D (dm_ip)::text) AND (($1)::text =3D (dm_user)::text=
) AND
((ss_key)::text <> ''::text))<o:p></o:p>

(6 rows)<o:p></o:p>

<o:p> </o:p>

But EXPLAIN ANALYSE query hangs (is not giving me any output ev=
en
after 30 minutes).<o:p></o:p>

<o:p> </o:p>

Pg_stat_activity shows this - SELECT procpid, usename,
current_query, query_start from pg_stat_activity:<o:p></o:p>

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 =3D (select
max(report_time) from repcopy as b where a.dm_ip =3D b.dm_ip and
a.dm_user=3Db.dm_user and b.ss_key !=3D '') and report_status =3D 0 and dm_=
user =3D
'u1'; | 2010-04-13 18:20:02.828623+05:30<o:p></o:p>

<o:p> </o:p>

<o:p> </o:p>

In such a case what can I do ? <o:p></o:p>

<o:p> </o:p>

</div>

</body>

</html>

--_000_BD69807DAE0CE44CA00A8338D0FDD08302CF97EA35oma00cexmbx 03_--

In response to Szymon Guz <mabewlun [at] gmail.com>:

> 2010/4/13 Satish Burnwal (sburnwal) <sburnwal [at] cisco.com>
>
> > I have a query which is not giving me the result even after 30 minut=
es. I
> > want to know how to detect what is going and what=E2=80=99s wrong ?
> >
> >
> >
> > EXPLAIN query - gives me the following:
> >
> > controlsmartdb=3D# 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 =3D (select
> > max(report_time) from repcopy as b where a.dm_ip =3D b.dm_ip and
> > a.dm_user=3Db.dm_user and b.ss_key !=3D '') and report_status =3D 0 a=
nd dm_user =3D
> > 'u1';
> >
> > QUERY PLAN
> >
> >
> > ------------------------------------------------------------ ---------=
-------------------------------------------------------
> >
> > Seq Scan on repcopy a (cost=3D0.00..1630178118.35 rows=3D35 width=3D=
133)
> >
> > Filter: ((report_status =3D 0) AND ((dm_user)::text =3D 'u1'::text=
) AND
> > (report_time =3D (subplan)))
> >
> > SubPlan
> >
> > -> Aggregate (cost=3D8151.65..8151.66 rows=3D1 width=3D8)
> >
> > -> Seq Scan on repcopy b (cost=3D0.00..8151.65 rows=3D1 =
width=3D8)
> >
> > Filter: ((($0)::text =3D (dm_ip)::text) AND (($1)::t=
ext =3D
> > (dm_user)::text) AND ((ss_key)::text <> ''::text))
> >
> > (6 rows)
> >
> >
> >
> > But EXPLAIN ANALYSE query hangs (is not giving me any output even aft=
er 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 =3D (select
> > max(report_time) from repcopy as b where a.dm_ip =3D b.dm_ip and
> > a.dm_user=3Db.dm_user and b.ss_key !=3D '') and report_status =3D 0 a=
nd dm_user =3D
> > '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 t=
he
> 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-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

On Tuesday 13 April 2010 6:03:43 am Satish Burnwal (sburnwal) wrote:
> controlsmartdb=3D# select * from pg_stat_activity where waiting=3D't';
>
> ERROR: column "waiting" does not exist
>
>

=46rom here:
http://www.postgresql.org/docs/8.4/interactive/monitoring-st ats.html#MONITO=
RING-STATS-VIEWS

My guess is you are being caught by this;

pg_stat_activity

"Furthermore, these columns are only visible if the user examining the view=
is a
superuser or the same as the user owning the process being reported on. '

--
Adrian Klaver
adrian.klaver [at] gmail.com

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

This is a multi-part message in MIME format.

------_=_NextPart_001_01CADB0A.C9034F66
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

select procpid, current_query,query_start - now(), backend_start

from pg_stat_activity

where current_query not like '%IDLE%' and waiting =3D 't';

--

Thanks

Dhaval

From: pgsql-general-owner [at] postgresql.org
[mailto:pgsql-general-owner [at] postgresql.org] On Behalf Of Satish Burnwal
(sburnwal)
Sent: Tuesday, April 13, 2010 2:04 PM
To: Plugge, Joe R.; pgsql-general [at] postgresql.org
Cc: pgsql-admin [at] postgresql.org
Subject: Re: [GENERAL] Query is stuck

controlsmartdb=3D# select * from pg_stat_activity where waiting=3D't';

ERROR: column "waiting" does not exist

From: Plugge, Joe R. [mailto:JRPlugge [at] west.com]
Sent: Tuesday, April 13, 2010 6:32 PM
To: Satish Burnwal (sburnwal); pgsql-general [at] postgresql.org
Cc: pgsql-admin [at] postgresql.org
Subject: RE: Query is stuck

What do you get when you run this?

select * from pg_stat_activity where waiting=3D't';

From: pgsql-admin-owner [at] postgresql.org
[mailto:pgsql-admin-owner [at] postgresql.org] On Behalf Of Satish Burnwal
(sburnwal)
Sent: Tuesday, April 13, 2010 7:58 AM
To: pgsql-general [at] postgresql.org
Cc: pgsql-admin [at] postgresql.org
Subject: [ADMIN] Query is stuck

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=3D# 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 =3D
(select max(report_time) from repcopy as b where a.dm_ip =3D b.dm_ip and
a.dm_user=3Db.dm_user and b.ss_key !=3D '') and report_status =3D 0 and
dm_user =3D 'u1';

QUERY PLAN

------------------------------------------------------------ ------------
----------------------------------------------------

Seq Scan on repcopy a (cost=3D0.00..1630178118.35 rows=3D35 width=3D133)

Filter: ((report_status =3D 0) AND ((dm_user)::text =3D 'u1'::text) AND
(report_time =3D (subplan)))

SubPlan

-> Aggregate (cost=3D8151.65..8151.66 rows=3D1 width=3D8)

-> Seq Scan on repcopy b (cost=3D0.00..8151.65 rows=3D1
width=3D8)

Filter: ((($0)::text =3D (dm_ip)::text) AND (($1)::text =
=3D
(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 =3D (select
max(report_time) from repcopy as b where a.dm_ip =3D b.dm_ip and
a.dm_user=3Db.dm_user and b.ss_key !=3D '') and report_status =3D 0 and
dm_user =3D 'u1'; | 2010-04-13 18:20:02.828623+05:30

In such a case what can I do ?

The information transmitted is intended only for the person or entity to wh=
ich it is addressed and may contain confidential and/or privileged material=
..
Any review, re-transmission, dissemination or other use of or taking of any=
action in reliance upon,this information by persons or entities other than=
the intended recipient is prohibited.
If you received this in error, please contact the sender and delete the mat=
erial from your computer.
Microland takes all reasonable steps to ensure that its electronic communic=
ations are free from viruses.
However, given Internet accessibility, the Company cannot accept liability =
for any virus introduced by this e-mail or any attachment and you are advis=
ed to use up-to-date virus checking software.

------_=_NextPart_001_01CADB0A.C9034F66
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-micr=
osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:x=3D"urn:schemas-microsoft-com:office:excel" xmlns:p=3D"urn:schemas-m=
icrosoft-com:office:powerpoint" xmlns:a=3D"urn:schemas-microsoft-com:office=
:access" xmlns:dt=3D"uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:s=3D"=
uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:rs=3D"urn:schemas-microsof=
t-com:rowset" xmlns:z=3D"#RowsetSchema" xmlns:b=3D"urn:schemas-microsoft-co=
m:office:publisher" xmlns:ss=3D"urn:schemas-microsoft-com:office:spreadshee=
t" xmlns:c=3D"urn:schemas-microsoft-com:office:component:spread sheet" xmlns=
:odc=3D"urn:schemas-microsoft-com:office:odc" xmlns:oa=3D"urn:schemas-micro=
soft-com:office:activation" xmlns:html=3D"http://www.w3.org/TR/REC-html40" =
xmlns:q=3D"http://schemas.xmlsoap.org/soap/envelope/" xmlns:rtc=3D"http://m=
icrosoft.com/officenet/conferencing" xmlns:D=3D"DAV:" xmlns:Repl=3D"http://=
schemas.microsoft.com/repl/" xmlns:mt=3D"http://schemas.microsoft.com/share=
point/soap/meetings/" xmlns:x2=3D"http://schemas.microsoft.com/office/excel=
/2003/xml" xmlns:ppda=3D"http://www.passport.com/NameSpace.xsd" xmlns:ois=
=3D"http://schemas.microsoft.com/sharepoint/soap/ois/" xmlns:dir=3D"http://=
schemas.microsoft.com/sharepoint/soap/directory/" xmlns:ds=3D"http://www.w3=
..org/2000/09/xmldsig#" xmlns:dsp=3D"http://schemas.microsoft.com/sharepoint=
/dsp" xmlns:udc=3D"http://schemas.microsoft.com/data/udc" xmlns:xsd=3D"http=
://www.w3.org/2001/XMLSchema" xmlns:sub=3D"http://schemas.microsoft.com/sha=
repoint/soap/2002/1/alerts/" xmlns:ec=3D"http://www.w3.org/2001/04/xmlenc#"=
xmlns:sp=3D"http://schemas.microsoft.com/sharepoint/" xmlns:sps=3D"http://=
schemas.microsoft.com/sharepoint/soap/" xmlns:xsi=3D"http://www.w3.org/2001=
/XMLSchema-instance" xmlns:udcs=3D"http://schemas.microsoft.com/data/udc/so=
ap" xmlns:udcxf=3D"http://schemas.microsoft.com/data/udc/xmlfile " xmlns:udc=
p2p=3D"http://schemas.microsoft.com/data/udc/parttopart" xmlns:wf=3D"http:/=
/schemas.microsoft.com/sharepoint/soap/workflow/" xmlns:dsss=3D"http://sche=
mas.microsoft.com/office/2006/digsig-setup" xmlns:dssi=3D"http://schemas.mi=
crosoft.com/office/2006/digsig" xmlns:mdssi=3D"http://schemas.openxmlformat=
s.org/package/2006/digital-signature" xmlns:mver=3D"http://schemas.openxmlf=
ormats.org/markup-compatibility/2006" xmlns:m=3D"http://schemas.microsoft.c=
om/office/2004/12/omml" xmlns:mrels=3D"http://schemas.openxmlformats.org/pa=
ckage/2006/relationships" xmlns:spwp=3D"http://microsoft.com/sharepoint/web=
partpages" xmlns:ex12t=3D"http://schemas.microsoft.com/exchange/service s/20=
06/types" xmlns:ex12m=3D"http://schemas.microsoft.com/exchange/service s/200=
6/messages" xmlns:pptsl=3D"http://schemas.microsoft.com/sharepoint/soap/ Sli=
deLibrary/" xmlns:spsl=3D"http://microsoft.com/webservices/SharePointPor tal=
Server/PublishedLinksService" xmlns:Z=3D"urn:schemas-microsoft-com:" xmlns:=
st=3D"" xmlns=3D"http://www.w3.org/TR/REC-html40">

<head>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; charset=3Dus-ascii">
<meta name=3DGenerator content=3D"Microsoft Word 12 (filtered medium)">
<style>

</style>

</head>

<body lang=3DEN-US link=3Dblue vlink=3Dpurple>

<div class=3DSection1>

select procpid, current_query,query_start - now(=
),
backend_start<o:p></o:p>

from pg_stat_activity<o:p></o:p>

where
current_query not like '%IDLE%' and waiting =3D 't';<o:p></o:p>

<o:p> </o:p>

--<o:p></o:p>

Thanks
<o:p></o:p>

Dhaval<o:p></o:p>

<o:p> </o:p>=


<div>

<div style=3D'border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in =
0in 0in'>

From:
pgsql-general-owner [at] postgresql.org [mailto:pgsql-general-owner [at] postgresql.o=
rg] On
Behalf Of Satish Burnwal (sburnwal) 
Sent: Tuesday, April 13, 2010 2:04 PM 
To: Plugge, Joe R.; pgsql-general [at] postgresql.org 
Cc: pgsql-admin [at] postgresql.org 
Subject: Re: [GENERAL] Query is stuck<o:p></o:p>

</div>

</div>

<o:p> </o:p>

controlsmartdb=3D# select * from pg_stat_activity where waiting=
=3D't';<o:p></o:p>

ERROR: column "waiting" does not exist<o:p></o:=
p>

<o:p> </o:p>

<div>

<div style=3D'border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in =
0in 0in'>

From: Plugge, Joe R.
[mailto:JRPlugge [at] west.com] 
Sent: Tuesday, April 13, 2010 6:32 PM 
To: Satish Burnwal (sburnwal); pgsql-general [at] postgresql.org 
Cc: pgsql-admin [at] postgresql.org 
Subject: RE: Query is stuck<o:p></o:p>

</div>

</div>

<o:p> </o:p>

What do you get when you run this?<o:p></o:p>

<o:p> </o:p>

select * from pg_stat_activity where waiting=3D't';<o:p></o:p>=


<o:p> </o:p>

<o:p> </o:p>

<o:p> </o:p>

<div>

<div style=3D'border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in =
0in 0in'>

From:
pgsql-admin-owner [at] postgresql.org [mailto:pgsql-admin-owner [at] postgresql.org] =
On
Behalf Of Satish Burnwal (sburnwal) 
Sent: Tuesday, April 13, 2010 7:58 AM 
To: pgsql-general [at] postgresql.org 
Cc: pgsql-admin [at] postgresql.org 
Subject: [ADMIN] Query is stuck<o:p></o:p>

</div>

</div>

<o:p> </o:p>

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 =
? <o:p></o:p>

<o:p> </o:p>

EXPLAIN query - gives me the following:<o:p></o:p>

controlsmartdb=3D# 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 =3D (s=
elect
max(report_time) from repcopy as b where a.dm_ip =3D b.dm_ip and
a.dm_user=3Db.dm_user and b.ss_key !=3D '') and report_status =3D 0 and dm_=
user =3D
'u1';<o:p></o:p>

 &nb=
sp; =
 &nb=
sp; =

QUERY PLAN<o:p></o:p>

------------------------------------------------ ---------------=
------------------------------------------------------------ -<o:p></o:p></s=
pan>

 Seq Scan on repcopy a (cost=3D0.00..1630178118.35 r=
ows=3D35
width=3D133)<o:p></o:p>

 Filter: ((report_status =3D 0) AND ((dm_user)::tex=
t =3D
'u1'::text) AND (report_time =3D (subplan)))<o:p></o:p>

 SubPlan<o:p></o:p>

 -> Aggregate
(cost=3D8151.65..8151.66 rows=3D1 width=3D8)<o:p></o:p>


-> Seq Scan on repcopy b (cost=3D0.00..8151.65 rows=3D1 widt=
h=3D8)<o:p></o:p>

 &nb=
sp;
Filter: ((($0)::text =3D (dm_ip)::text) AND (($1)::text =3D (dm_user)::text=
) AND
((ss_key)::text <> ''::text))<o:p></o:p>

(6 rows)<o:p></o:p>

<o:p> </o:p>

But EXPLAIN ANALYSE query hangs (is not giving me any output ev=
en
after 30 minutes).<o:p></o:p>

<o:p> </o:p>

Pg_stat_activity shows this - SELECT procpid, usename,
current_query, query_start from pg_stat_activity:<o:p></o:p>

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 =3D (select
max(report_time) from repcopy as b where a.dm_ip =3D b.dm_ip and
a.dm_user=3Db.dm_user and b.ss_key !=3D '') and report_status =3D 0 and dm_=
user =3D
'u1'; | 2010-04-13 18:20:02.828623+05:30<o:p></o:p>

<o:p> </o:p>

<o:p> </o:p>

In such a case what can I do ? <o:p></o:p>

<o:p> </o:p>

</div>

<pre>The information transmitted is intended only for the person or entity =
to which it is addressed and may contain confidential and/or privileged mat=
erial.
Any review, re-transmission, dissemination or other use of or taking of any=
action in reliance upon,this information by persons or entities other than=
the intended recipient is prohibited.
If you received this in error, please contact the sender and delete the mat=
erial from your computer.
Microland takes all reasonable steps to ensure that its electronic communic=
ations are free from viruses.
However, given Internet accessibility, the Company cannot accept liability =
for any virus introduced by this e-mail or any attachment and you are advis=
ed to use up-to-date virus checking software.
</pre></body>

</html>

------_=_NextPart_001_01CADB0A.C9034F66--

We can see from the result of EXPLAIN that your query is very costly to
execute (the important bit is "cost=3D0.00..1630178118.35"). The fact
that it is doing a sequential scan ("Seq Scan") tells us why it takes so
long.

Without being able to see your data, it is hard to offer suggestions
about how you could improve your query. But one thing that jumps out at
me is that you have a call to the max() function in your WHERE clause.
In my experience, having a function call in a WHERE clause is very
expensive to execute. I think you would do best if you can find a
different way to build your query that avoids this handicap. Maybe a
new index on your table would help too, perhaps a partial index; but
again, this is just guessing without knowing the nature of your data.

Thank you,

Lewis Kapell
Computer Operations
Seton Home Study School

On 4/13/2010 8:58 AM, Satish Burnwal (sburnwal) wrote:
> 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=92s wrong ?
>
> EXPLAIN query - gives me the following:
>
> controlsmartdb=3D# 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 =3D
> (select max(report_time) from repcopy as b where a.dm_ip =3D b.dm_ip an=
d
> a.dm_user=3Db.dm_user and b.ss_key !=3D '') and report_status =3D 0 and
> dm_user =3D 'u1';
>
> QUERY PLAN
>
> ------------------------------------------------------------ -----------=
-----------------------------------------------------
>
> Seq Scan on repcopy a (cost=3D0.00..1630178118.35 rows=3D35 width=3D133=
)
>
> Filter: ((report_status =3D 0) AND ((dm_user)::text =3D 'u1'::text) AND
> (report_time =3D (subplan)))
>
> SubPlan
>
> -> Aggregate (cost=3D8151.65..8151.66 rows=3D1 width=3D8)
>
> -> Seq Scan on repcopy b (cost=3D0.00..8151.65 rows=3D1 width=3D8)
>
> Filter: ((($0)::text =3D (dm_ip)::text) AND (($1)::text =3D (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 =3D (select
> max(report_time) from repcopy as b where a.dm_ip =3D b.dm_ip and
> a.dm_user=3Db.dm_user and b.ss_key !=3D '') and report_status =3D 0 and
> dm_user =3D 'u1'; | 2010-04-13 18:20:02.828623+05:30
>
> In such a case what can I do ?
>

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Lewis Kapell <lkapell [at] setonhome.org> wrote:

> The fact that it is doing a sequential scan ("Seq Scan") tells us
> why it takes so long.

Well, that and the fact that for each row in one scan of the table,
it scans the entire table again. :-(

>> 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';

I *think* that's equivalent to the following, which might be faster:

select
report_id, a.dm_ip, dm_mac, dm_user, dm_os, a.report_time,
sys_name, sys_user, sys_user_domain, ss_key, login_time,
role_id, new_vlan_id
from repcopy a
join (
select dm_ip, max(report_time) as report_time
from repcopy
where b.ss_key != '' and b.dm_user = 'u1'
group by dm_ip
) b
on (b.dm_ip = a.dm_ip and b.report_time = a.report_time)
where a.report_status = 0 and a.dm_user = 'u1'
;

-Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Query is stuck

Re: Query is stuck

Re: Query is stuck

Re: Query is stuck

Re: Query is stuck

Re: [GENERAL] Query is stuck

Re: [GENERAL] Query is stuck

Re: [GENERAL] Query is stuck

Re: Query is stuck

Re: Query is stuck