From: Lewis Kapell <lkapell@setonhome.org>
To: pgsql-admin@postgresql.org
Subject: Re: Query is stuck
Date: Thu, 15 Apr 2010 10:37:27 -0400
Message-ID: <4BC724A7.5070301@setonhome.org> (raw)
In-Reply-To: <3A8C969225424C4D8E6BEE65ED8552DA0119F188@XMB-BGL-41C.cisco.com>
References: <3A8C969225424C4D8E6BEE65ED8552DA0119F188@XMB-BGL-41C.cisco.com>
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 ?
>
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: lkapell@setonhome.org
Subject: Re: Query is stuck
In-Reply-To: <4BC724A7.5070301@setonhome.org>
* 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