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