pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: pgsql-admin@postgresql.org
To: Lewis Kapell <lkapell@setonhome.org>
Subject: Re: Query is stuck
Date: Thu, 15 Apr 2010 12:27:31 -0500
Message-ID: <4BC70633020000250003095B@gw.wicourts.gov> (raw)
In-Reply-To: <4BC724A7.5070301@setonhome.org>
References: <3A8C969225424C4D8E6BEE65ED8552DA0119F188@XMB-BGL-41C.cisco.com>
	<4BC724A7.5070301@setonhome.org>

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



view thread (22+ messages)  latest in thread

Message-ID: <4BC70633020000250003095B@gw.wicourts.gov>
Permalink:  ../4BC70633020000250003095B@gw.wicourts.gov/
Also on:    postgresql.org/message-id/4BC70633020000250003095B@gw.wicourts.gov

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: Kevin.Grittner@wicourts.gov, lkapell@setonhome.org
  Subject: Re: Query is stuck
  In-Reply-To: <4BC70633020000250003095B@gw.wicourts.gov>
  References: <3A8C969225424C4D8E6BEE65ED8552DA0119F188@XMB-BGL-41C.cisco.com> <4BC724A7.5070301@setonhome.org> <4BC70633020000250003095B@gw.wicourts.gov>

* 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