X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 8088BD8746 for ; Thu, 1 Sep 2005 16:08:13 -0300 (ADT) 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 96622-01 for ; Thu, 1 Sep 2005 19:08:09 +0000 (GMT) Received: from newman.lshift.net (host226.lshift.net [195.172.218.226]) by svr1.postgresql.org (Postfix) with ESMTP id A8FD0D86FD for ; Thu, 1 Sep 2005 16:08:07 -0300 (ADT) Received: (qmail 3976 invoked by uid 1004); 1 Sep 2005 19:08:09 -0000 Received: from 10.224.189.77 by newman (envelope-from , uid 64011) with qmail-scanner-1.24 (clamdscan: 0.80/627. Clear:RC:1(10.224.189.77):. Processed in 0.013879 secs); 01 Sep 2005 19:08:09 -0000 Received: from ponder.lshift.net (HELO localhost) ([10.224.189.77]) (envelope-sender ) by newman.lshift.net (qmail-ldap-1.03) with AES256-SHA encrypted SMTP for ; 1 Sep 2005 19:08:08 -0000 Date: Thu, 1 Sep 2005 20:08:08 +0100 From: Matthew Sackman To: pgsql-performance@postgresql.org Subject: Re: Massive performance issues Message-ID: <20050901190808.GB7131@pongo.lshift.net> Mail-Followup-To: pgsql-performance@postgresql.org References: <20050901174231.GE23970@pongo.lshift.net> <5178.1125600426@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <5178.1125600426@sss.pgh.pa.us> User-Agent: Mutt/1.5.10i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.249 required=5 tests=[AWL=-0.125, DNS_FROM_RFC_ABUSE=0.374] X-Spam-Level: X-Archive-Number: 200509/12 X-Sequence-Number: 14320 On Thu, Sep 01, 2005 at 02:47:06PM -0400, Tom Lane wrote: > Matthew Sackman writes: > > Obviously, to me, this is a problem, I need these queries to be under a > > second to complete. Is this unreasonable? > > Yes. Pulling twenty thousand rows at random from a table isn't free. I appreciate that. But I'm surprised by how un-free it seems to be. And it seems others here have performance I need on similar hardware. > You were pretty vague about your disk hardware, which makes me think > you didn't spend a lot of money on it ... and on low-ball hardware, > that sort of random access speed just isn't gonna happen. Well, this is a development box. But the live box wouldn't be much more than RAID 1 on SCSI 10ks so that should only be a halving of seek time, not the 1000 times reduction I'm after! In fact, now I think about it, I have been testing on a 2.4 kernel on a dual HT 3GHz Xeon with SCSI RAID array and the performance is only marginally better. > If the queries you need are very consistent, you might be able to get > some mileage out of CLUSTERing by the relevant index ... but the number > of indexes you've created makes me think that's not so ... No, the queries, whilst in just three distinct forms, will effectively be for fairly random values. Matthew