Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id BCDBC2E3B7B for ; Mon, 4 Feb 2008 16:39:33 -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 93372-01-9 for ; Mon, 4 Feb 2008 16:39:23 -0400 (AST) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id BB9792E3C5B for ; Mon, 4 Feb 2008 16:39:26 -0400 (AST) 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 m14KdPlX007906; Mon, 4 Feb 2008 15:39:25 -0500 (EST) To: "Rainer Spittel" cc: pgsql-admin@postgresql.org Subject: Re: performance on selecting a row in large tables In-reply-to: <12B8F225666F99489CEFA8B3F261540CFAAE10@geneva.local.terralink> References: <12B8F225666F99489CEFA8B3F261540CFAAE10@geneva.local.terralink> Comments: In-reply-to "Rainer Spittel" message dated "Tue, 05 Feb 2008 09:07:26 +1300" Date: Mon, 04 Feb 2008 15:39:25 -0500 Message-ID: <7905.1202157565@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.728 tagged_above=0 required=5 tests=AWL=-2.772, BAYES_99=3.5 X-Spam-Level: X-Archive-Number: 200802/16 X-Sequence-Number: 28090 "Rainer Spittel" writes: > When performing a 'select col01_id from table limit 1 offset 100000;', > the query takes up to 20sec. Monitoring the dstats on the server, I see > that the box is reading approx. 1GB from the disks. No surprise. That query says "read 100001 rows, then throw away the first 100000". Why are you considering that this is an important performance measure? Are you aware that the results of such a query aren't even very well defined (since you omitted an ORDER BY)? regards, tom lane