Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id F0AAD64FCDD for ; Fri, 19 Sep 2008 13:23:03 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 83798-03 for ; Fri, 19 Sep 2008 13:22:55 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from smtp128.rog.mail.re2.yahoo.com (smtp128.rog.mail.re2.yahoo.com [206.190.53.33]) by postgresql.org (Postfix) with SMTP id 4FC8064FCCA for ; Fri, 19 Sep 2008 13:22:54 -0300 (ADT) Received: (qmail 24394 invoked from network); 19 Sep 2008 16:22:52 -0000 Received: from unknown (HELO ?99.246.51.72?) (markmielke@rogers.com@99.246.51.72 with plain) by smtp128.rog.mail.re2.yahoo.com with SMTP; 19 Sep 2008 16:22:52 -0000 X-YMail-OSG: T.Cs1CsVM1lo1bjSTRoKVOa4yVavbFj2QIJR3TY3.8eUOimSV6o6zpjU1xVJ1skHPw-- X-Yahoo-Newman-Property: ymail-3 Message-ID: <48D3D1DB.5010803@mark.mielke.cc> Date: Fri, 19 Sep 2008 12:22:51 -0400 From: Mark Mielke User-Agent: Thunderbird 2.0.0.16 (X11/20080723) MIME-Version: 1.0 CC: Matthew Wakeling , pgsql-performance@postgresql.org Subject: Re: RAID arrays and performance References: <87lk8aiqf3.fsf@oxford.xeocode.com> <475559F4.7060104@mark.mielke.cc> <4755648C.90905@mark.mielke.cc> <87bq96ii3b.fsf@oxford.xeocode.com> <4755810E.4000707@mark.mielke.cc> <87zlwq6yz9.fsf@oxford.xeocode.com> <87bq74vfys.fsf@oxford.xeocode.com> <21255.1221836996@sss.pgh.pa.us> In-Reply-To: <21255.1221836996@sss.pgh.pa.us> Content-Type: multipart/alternative; boundary="------------000501000900090100020005" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.582 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001, MISSING_HEADERS=1.581 X-Spam-Level: * X-Archive-Number: 200809/192 X-Sequence-Number: 31505 This is a multi-part message in MIME format. --------------000501000900090100020005 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Matthew Wakeling writes: >> In order to improve the performance, I made the system look ahead in the >> source, in groups of a thousand entries, so instead of running: >> SELECT * FROM table WHERE field = 'something'; >> a thousand times, we now run: >> SELECT * FROM table WHERE field IN ('something', 'something else'...); >> with a thousand things in the IN. Very simple query. It does run faster >> than the individual queries, but it still takes quite a while. Here is an >> example query: >> Have you considered temporary tables? Use COPY to put everything you want to query into a temporary table, then SELECT to join the results, and pull all of the results, doing additional processing (UPDATE) as you pull? Cheers, mark -- Mark Mielke --------------000501000900090100020005 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Matthew Wakeling <matthew@flymine.org> writes:
In order to improve the performance, I made the system look ahead in the 
source, in groups of a thousand entries, so instead of running:
SELECT * FROM table WHERE field = 'something';
a thousand times, we now run:
SELECT * FROM table WHERE field IN ('something', 'something else'...);
with a thousand things in the IN. Very simple query. It does run faster 
than the individual queries, but it still takes quite a while. Here is an 
example query:

Have you considered temporary tables? Use COPY to put everything you want to query into a temporary table, then SELECT to join the results, and pull all of the results, doing additional processing (UPDATE) as you pull?

Cheers,
mark
-- 
Mark Mielke <mark@mielke.cc>
--------------000501000900090100020005--