Skip site navigation (1) Skip section navigation (2)

Preventing query from hogging server

From: "Matthew Nuzum" <matt(dot)followers(at)gmail(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: Preventing query from hogging server
Date: 2005-03-24 19:07:39
Message-ID: 42431001.36c6643c.1b4b.ffffd505@mx.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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(at)followers(dot)net>
www.followers.net - Makers of "Elite Content Management System"
View samples of Elite CMS in action by visiting
http://www.elitecms.com/



Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2005-03-24 19:22:22
Subject: Re: BUG #1552: massive performance hit between 7.4 and 8.0.1
Previous:From: Otto BlomqvistDate: 2005-03-24 18:17:06
Subject: pg_autovacuum not having enough suction ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group