Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 2A491634AB4
	for <pgsql-general-postgresql.org@mail.postgresql.org>;
	Wed, 14 Apr 2010 10:43:08 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 87780-01-3
	for <pgsql-general-postgresql.org@mail.postgresql.org>;
	Wed, 14 Apr 2010 13:42:57 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail.potentialtech.com (internet.potentialtech.com
	[66.167.251.6])
	by mail.postgresql.org (Postfix) with ESMTP id B9AAC6340F9
	for <pgsql-general@postgresql.org>;
	Wed, 14 Apr 2010 10:42:07 -0300 (ADT)
Received: from localhost (pr40.pitbpa0.pub.collaborativefusion.com
	[206.210.89.202])
	(using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits))
	(No client certificate requested)
	by mail.potentialtech.com (Postfix) with ESMTPSA id 765BFF7444;
	Wed, 14 Apr 2010 09:42:04 -0400 (EDT)
Date: Wed, 14 Apr 2010 09:42:03 -0400
From: Bill Moran <wmoran@potentialtech.com>
To: "Satish Burnwal (sburnwal)" <sburnwal@cisco.com>
Cc: <pgsql-general@postgresql.org>
Subject: Re: Query is stuck
Message-Id: <20100414094203.73aef9b2.wmoran@potentialtech.com>
In-Reply-To: <3A8C969225424C4D8E6BEE65ED8552DA0119F417@XMB-BGL-41C.cisco.com>
References: <3A8C969225424C4D8E6BEE65ED8552DA0119F188@XMB-BGL-41C.cisco.com>
	<o2ne4edc9361004130603r9acb3257yb800890f447eb2ce@mail.gmail.com>
	<20100413091248.a295737b.wmoran@potentialtech.com>
	<3A8C969225424C4D8E6BEE65ED8552DA0119F1AD@XMB-BGL-41C.cisco.com>
	<6013c776bd38b7738ff9e43815ce8adc@mail.gransy.com>
	<3A8C969225424C4D8E6BEE65ED8552DA0119F3EE@XMB-BGL-41C.cisco.com>
	<20100414083622.dcf54d70.wmoran@potentialtech.com>
	<3A8C969225424C4D8E6BEE65ED8552DA0119F417@XMB-BGL-41C.cisco.com>
Organization: Bill Moran
X-Mailer: Sylpheed 2.7.1 (GTK+ 2.18.7; i386-portbld-freebsd7.2)
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9,
	T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201004/445
X-Sequence-Number: 161643

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.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/