X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id C3466D1C7F0 for ; Thu, 5 Feb 2004 15:54:40 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 44390-08 for ; Thu, 5 Feb 2004 11:54:45 -0400 (AST) Received: from anchor-post-37.mail.demon.net (anchor-post-36.mail.demon.net [194.217.242.86]) by svr1.postgresql.org (Postfix) with ESMTP id A6A92D1B917 for ; Thu, 5 Feb 2004 11:54:35 -0400 (AST) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-37.mail.demon.net with esmtp (Exim 3.35 #1) id 1Aolpp-0003HB-0b; Thu, 05 Feb 2004 15:54:33 +0000 Received: by mainbox.archonet.com (Postfix, from userid 529) id 0622016C47; Thu, 5 Feb 2004 15:54:31 +0000 (GMT) Received: from client17.archonet.com (client17.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id 6287E16AB3; Thu, 5 Feb 2004 15:54:28 +0000 (GMT) From: Richard Huxton To: "Damien Dougan" , Subject: Re: Index Performance Help Date: Thu, 5 Feb 2004 15:54:27 +0000 User-Agent: KMail/1.5 References: <004b01c3ebe1$7e0c9a70$7701a8c0@pestilence> In-Reply-To: <004b01c3ebe1$7e0c9a70$7701a8c0@pestilence> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200402051554.27672.dev@archonet.com> X-Bogosity: No, tests=bogofilter, spamicity=0.000000, version=0.15.3 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/75 X-Sequence-Number: 5599 On Thursday 05 February 2004 12:13, Damien Dougan wrote: > Hi All, > > I've been seeing very slow read performance on a database of 1 million > indexed subscribers, which I believe is nothing to do with the data > itself, but delays on processing the index. > > If I make a random jump into the index (say X), it can take about 50ms > to read the subscriber. If I then make a "close by" lookup (say X+10), > it takes only about 0.5ms to read the subscriber. Making another lookup > to a "far away" (say X+1000), it again takes about 50ms to read. The first time, it has to fetch a block from disk. The second time that disk block is already in RAM so it's much faster. The third time it needs a different disk block. > Am I correct in my analysis? Is there anything I can do to improve the > performance of the index lookups? Make sure you have enough RAM to buffer your disks. Buy faster disks. > I've tried increasing the index memory and making a number of queries > around the index range, but a stray of several hundred indexes from a > cached entry always results in a major lookup delay. Yep, that'll be your disks. > I've also increased the shared memory available to Postgres to 80MB > incase this is a paging of the index, but it hasn't seemed to have any > effect. Probably the wrong thing to do (although you don't mention what hardware you've got). Read the tuning document at: http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php > Sample analyze output for an initial query: > > hydradb=# explain analyze select * from pvsubscriber where actorid = > 'b3432-asdas-232-Subscriber793500'; ... > -> Index Scan using mc_actor_key on mc_actor > (cost=0.00..4.08 rows=1 width=69) (actual time=39.497..39.499 rows=1 > loops=1) ... > Total runtime: 49.845 ms > And the analyze output for a "nearby" subscriber (10 indexes away): > > hydradb=# explain analyze select * from pvsubscriber where actorid = > 'b3432-asdas-232-Subscriber793510'; > ... > -> Index Scan using mc_actor_key on mc_actor > (cost=0.00..4.08 rows=1 width=69) (actual time=0.220..0.221 rows=1 > loops=1) > Total runtime: 0.428 ms > (15 rows) That certainly seems to be the big change - the only way to consistently get 1ms timings is going to be to make sure all your data is cached. Try the tuning guide above and see what difference that makes. If that's no good, post again with details of your config settings, hardware, number of clients etc... -- Richard Huxton Archonet Ltd