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 ?
--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
