Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 01F742E00AD for ; Thu, 14 Feb 2008 20:02:44 -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 86246-04 for ; Thu, 14 Feb 2008 20:02:29 -0400 (AST) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 5D12A2E009F for ; Thu, 14 Feb 2008 20:02:35 -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 m1F02WJ9021718; Thu, 14 Feb 2008 19:02:32 -0500 (EST) To: Greg Smith cc: Michael Lorenz , pgsql-performance@postgresql.org Subject: Re: Query slows after offset of 100K In-reply-to: References: Comments: In-reply-to Greg Smith message dated "Thu, 14 Feb 2008 18:54:34 -0500" Date: Thu, 14 Feb 2008 19:02:32 -0500 Message-ID: <21717.1203033752@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/171 X-Sequence-Number: 28713 Greg Smith writes: > On Thu, 14 Feb 2008, Michael Lorenz wrote: >> When offsetting up to about 90K records, the EXPLAIN ANALYZE is similar to the following: >> Limit (cost=15357.06..15387.77 rows=20 width=35) (actual time=19.235..19.276 rows=20 loops=1) >> -> Index Scan using account_objectname on "object" o (cost=0.00..1151102.10 rows=749559 width=35) (actual time=0.086..14.981 rows=10020 loops=1) > It looks like the planner thinks that index scan will have to go through > 749559 rows, but there are actually only 10020 there. No, you have to be careful about that. The estimated rowcount is for the case where the plan node is run to completion, but when there's a LIMIT over it, it may not get run to completion. In this case the limit was satisfied after pulling 10020 rows from the indexscan, but we can't tell what fraction of the underlying scan was actually completed. regards, tom lane