Message-ID: <4BC724A7.5070301@setonhome.org>
Date: Thu, 15 Apr 2010 10:37:27 -0400
From: Lewis Kapell <lkapell@setonhome.org>
Organization: Seton Home Study School
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
	rv:1.9.1.9) Gecko/20100317 Lightning/1.0b1 Thunderbird/3.0.4
MIME-Version: 1.0
To: pgsql-admin@postgresql.org
Subject: Re: Query is stuck
References: <3A8C969225424C4D8E6BEE65ED8552DA0119F188@XMB-BGL-41C.cisco.com>
In-Reply-To: <3A8C969225424C4D8E6BEE65ED8552DA0119F188@XMB-BGL-41C.cisco.com>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 8bit

We can see from the result of EXPLAIN that your query is very costly to 
execute (the important bit is "cost=0.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’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 ?
>