pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Matthew Wakeling <matthew@flymine.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: RAID arrays and performance
Date: Fri, 19 Sep 2008 11:09:56 -0400
Message-ID: <21255.1221836996@sss.pgh.pa.us> (raw)
In-Reply-To: <alpine.DEB.1.10.0809191535050.23198@aragorn.flymine.org>
References: <Pine.LNX.4.58.0712041213490.3731@aragorn.flymine.org>
	<87lk8aiqf3.fsf@oxford.xeocode.com>
	<Pine.LNX.4.58.0712041247540.3731@aragorn.flymine.org>
	<475559F4.7060104@mark.mielke.cc>
	<Pine.LNX.4.58.0712041349380.3731@aragorn.flymine.org>
	<4755648C.90905@mark.mielke.cc>
	<Pine.LNX.4.58.0712041454110.3731@aragorn.flymine.org>
	<87bq96ii3b.fsf@oxford.xeocode.com>
	<Pine.LNX.4.58.0712041603490.3731@aragorn.flymine.org>
	<4755810E.4000707@mark.mielke.cc>
	<Pine.LNX.4.58.0712041637260.3731@aragorn.flymine.org>
	<87zlwq6yz9.fsf@oxford.xeocode.com>
	<Pine.LNX.4.64.0801291417330.4642@aragorn.flymine.org>
	<87bq74vfys.fsf@oxford.xeocode.com>
	<alpine.DEB.1.10.0809181417040.23198@aragorn.flymine.org>
	<Pine.GSO.4.64.0809181515380.7355@westnet.com>
	<alpine.DEB.1.10.0809191535050.23198@aragorn.flymine.org>

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:

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



view thread (39+ messages)  latest in thread

Message-ID: <21255.1221836996@sss.pgh.pa.us>
Permalink:  ../21255.1221836996@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/21255.1221836996@sss.pgh.pa.us

 · 

reply
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: tgl@sss.pgh.pa.us, matthew@flymine.org
  Subject: Re: RAID arrays and performance
  In-Reply-To: <21255.1221836996@sss.pgh.pa.us>

* 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