pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Matthew Nuzum <matt.followers@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Preventing query from hogging server
Date: Thu, 24 Mar 2005 13:07:39 -0600
Message-ID: <42431001.36c6643c.1b4b.ffffd505@mx.gmail.com> (raw)

I've got a report that is starting to take too long to run. I'm going to
create a lookup table that should speed up the results, but first I've got
to create the lookup table.

I honestly don't care how long the query takes to run, I just want to run it
without causing a major performance impact on other operations. The query
seems to take forever even when I limit the results to just 10, so I don't
know if I can get good results by splitting the query into groups of queries
(for example, for a years worth of data do 12 queries, one for each month or
maybe 365 queries, one for each day) or if there is a psql equivalent to
"nice."

I've tried `nice psql` in the past and I don't think that had much impact,
but I haven't tried it on this query.

Here is the query (BTW, there will be a corresponding "max" version of this
query as well):
INSERT INTO usage_sessions_min (accountid,atime,sessionid)
select accountid, min(atime) as atime, sessionid from usage_access 
group by accountid,sessionid;

atime is a timestamptz, accountis is varchar(12) and sessionid is int.

I've tried to do an explain analyze of this query, but its been running for
hours already and I don't know when it will finish.

-- 
Matthew Nuzum <matt@followers.net>
www.followers.net - Makers of "Elite Content Management System"
View samples of Elite CMS in action by visiting
http://www.elitecms.com/





view thread (9+ messages)  latest in thread

Message-ID: <42431001.36c6643c.1b4b.ffffd505@mx.gmail.com>
Permalink:  ../42431001.36c6643c.1b4b.ffffd505@mx.gmail.com/
Also on:    postgresql.org/message-id/42431001.36c6643c.1b4b.ffffd505@mx.gmail.com

 · 

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 using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: matt.followers@gmail.com
  Subject: Re: Preventing query from hogging server
  In-Reply-To: <42431001.36c6643c.1b4b.ffffd505@mx.gmail.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