Lewis Kapell <lkapell(at)setonhome(dot)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