Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 49E572E0114 for ; Mon, 4 Feb 2008 17:20:56 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 34975-09 for ; Mon, 4 Feb 2008 17:20:48 -0400 (AST) Received: from easy2.in-chemnitz.de (easy2.in-chemnitz.de [88.198.17.232]) by postgresql.org (Postfix) with ESMTP id C5D992E00BA for ; Mon, 4 Feb 2008 17:20:52 -0400 (AST) Received: from localhost (localhost [127.0.0.1]) by easy2.in-chemnitz.de (Postfix) with ESMTP id AB72BB2551 for ; Mon, 4 Feb 2008 22:20:50 +0100 (CET) Received: from easy2.in-chemnitz.de ([127.0.0.1]) by localhost (easy.in-chemnitz.de [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 2k7AWyLjD4O7 for ; Mon, 4 Feb 2008 22:20:50 +0100 (CET) Received: by easy2.in-chemnitz.de (Postfix, from userid 1013) id 268C1B2893; Mon, 4 Feb 2008 22:20:50 +0100 (CET) Date: Mon, 4 Feb 2008 22:20:50 +0100 From: Tino Schwarze To: pgsql-admin@postgresql.org Subject: Re: performance on selecting a row in large tables Message-ID: <20080204212050.GA17045@easy2.in-chemnitz.de> References: <12B8F225666F99489CEFA8B3F261540CFAAE10@geneva.local.terralink> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: <12B8F225666F99489CEFA8B3F261540CFAAE10@geneva.local.terralink> User-Agent: Mutt/1.5.13 (2006-08-11) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/17 X-Sequence-Number: 28091 Hi Rainer, On Tue, Feb 05, 2008 at 09:07:26AM +1300, Rainer Spittel wrote: > When performing a 'select col01_id from table limit 1 offset 100000;', As Tom already stated, the result of this query is a bit random since there is no ordering at all. What are you actually querying for? Try tuning queries before tuning the server. > Looking forward for any comments or suggestions. Here it is, right at the bottom of your mail: > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend explain analyze select col01_id from table limit 1 offset 100000; will run the query and show you how the query plan looks and how much time is spent. It is likely to say "SeqScan" somewhere - a sequential scan of the tabe. HTH! Tino. -- www.craniosacralzentrum.de www.spiritualdesign-chemnitz.de Tino Schwarze * Lortzingstraße 21 * 09119 Chemnitz