Re: Query is stuck

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Lewis Kapell <lkapell@xxxxxxxxxxxxx> 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

-- 
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux