X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id AA9625343F for ; Thu, 24 Mar 2005 19:08:02 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 32082-07 for ; Thu, 24 Mar 2005 19:07:52 +0000 (GMT) Received: from rproxy.gmail.com (rproxy.gmail.com [64.233.170.202]) by svr1.postgresql.org (Postfix) with ESMTP id 61A6D5345A for ; Thu, 24 Mar 2005 19:07:51 +0000 (GMT) Received: by rproxy.gmail.com with SMTP id f1so892965rne for ; Thu, 24 Mar 2005 11:07:45 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:reply-to:from:to:subject:date:mime-version:content-type:content-transfer-encoding:x-mailer:thread-index:x-mimeole:message-id; b=FK9ylkBNp0EeKMkSZgvH8FSpUnw4MlI9tHuuE6RTc2LVvnEuHSNhMrs4sTz2+CCU8ZVhIchN8adZYvzTQbsk6QdUZg1fECXTOTBtZkgx7GwRoHGMBDLSitSRzQS09ks2KA/5qANdG9E3Gg02v37zcpPhkmk2IINZg3oVih2rZAI= Received: by 10.38.9.54 with SMTP id 54mr1847691rni; Thu, 24 Mar 2005 11:07:45 -0800 (PST) Received: from MATTSPC ([12.216.69.41]) by mx.gmail.com with ESMTP id 63sm636225rna.2005.03.24.11.07.44; Thu, 24 Mar 2005 11:07:45 -0800 (PST) Reply-To: From: "Matthew Nuzum" To: Subject: Preventing query from hogging server Date: Thu, 24 Mar 2005 13:07:39 -0600 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook, Build 11.0.6353 Thread-Index: AcUwpL85DCG5JW2MS6OX5puytpNpHA== X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 Message-ID: <42431001.36c6643c.1b4b.ffffd505@mx.gmail.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.398 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200503/513 X-Sequence-Number: 11218 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 www.followers.net - Makers of "Elite Content Management System" View samples of Elite CMS in action by visiting http://www.elitecms.com/