From: Bill Moran <wmoran@potentialtech.com>
To: Satish Burnwal (sburnwal) <sburnwal@cisco.com>
Cc: pgsql-general@postgresql.org
Subject: Re: Query is stuck
Date: Wed, 14 Apr 2010 09:42:03 -0400
Message-ID: <20100414094203.73aef9b2.wmoran@potentialtech.com> (raw)
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>
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.comhttp://people.collaborativefusion.com/~wmoran/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: wmoran@potentialtech.com, sburnwal@cisco.com, pgsql-general@postgresql.org
Subject: Re: Query is stuck
In-Reply-To: <20100414094203.73aef9b2.wmoran@potentialtech.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox