Search Postgresql Archives

Re: Query is stuck

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

 



In response to "Satish Burnwal (sburnwal)" <sburnwal@xxxxxxxxx>:
> 

<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.440 rows=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.779 rows=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/

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux