Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 2415B64FCDD for ; Fri, 19 Sep 2008 12:10:14 -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 63011-03 for ; Fri, 19 Sep 2008 12:10:04 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id D82E864FCD2 for ; Fri, 19 Sep 2008 12:10:03 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id m8JF9uSg021256; Fri, 19 Sep 2008 11:09:56 -0400 (EDT) To: Matthew Wakeling cc: pgsql-performance@postgresql.org Subject: Re: RAID arrays and performance In-reply-to: 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> Comments: In-reply-to Matthew Wakeling message dated "Fri, 19 Sep 2008 15:59:12 +0100" Date: Fri, 19 Sep 2008 11:09:56 -0400 Message-ID: <21255.1221836996@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200809/190 X-Sequence-Number: 31503 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: Your example shows the IN-list as being sorted, but I wonder whether you actually are sorting the items in practice? If not, you might try that to improve locality of access to the index. Also, parsing/planning time could be part of your problem here with 1000 things to look at. Can you adjust your client code to use a prepared query? I'd try SELECT * FROM table WHERE field = ANY($1::text[]) (or whatever the field datatype actually is) and then push the list over as a single parameter value using array syntax. You might find that it scales to much larger IN-lists that way. regards, tom lane