In response to "Satish Burnwal (sburnwal)" <sburnwal@cisco.com>:
>
<snip>
Man, it's hard to read your emails. I've reformatted, I suggest you
improve the formatting on future emails, as I was about to say "to
hell with this question" because it was just too difficult to read,
and I expect there are others on the list who did just that.
Anyway ...
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_user=b.dm_user and a.dm_ip = b.dm_ip and b.ss_key != ''
)
and report_status = 0 and dm_user = 'u3';
QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------------------------------
------
Index Scan using repcopy_index on repcopy a (cost=0.00..87824607.17 rows=28 width=142) (actual
time=11773.105..689111.440rows=1 loops=1)
Index Cond: ((dm_user)::text = 'u3'::text)
Filter: ((report_status = 0) AND (report_time = (subplan)))
SubPlan
-> Aggregate (cost=3531.30..3531.31 rows=1 width=8) (actual time=58.447..58.448 rows=1 loops=11788)
-> Index Scan using repcopy_index on repcopy b (cost=0.00..3526.30 rows=2000 width=8) (actual
time=0.017..36.779rows=25842 loops=11788)
Index Cond: ((($0)::text = (dm_user)::text) AND(($1)::text = (dm_ip)::text))
Filter: ((ss_key)::text <> ''::text)
Total runtime: 689111.511 ms
(9 rows)
OK, now that I can read it, I noticed something that I missed before.
Your subquery is being run separately for every row that matches
report_status = 0 and dm_user = 'u3'. This is equating to 11788
executions, which seems to be a significant part of the problem.
Can you rewrite the query to remove the subquery? Or at least figure
out a way to filter the results more before calling the subquery.
I tried to suggest a rewrite, but I've found that I simply can't
understand what it is you're trying to accomplish with that query.
--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/