From: Tom Lane <tgl@sss.pgh.pa.us>
To: Greg Smith <gsmith@gregsmith.com>
Cc: Michael Lorenz <mlorenz1@hotmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query slows after offset of 100K
Date: Thu, 14 Feb 2008 19:02:32 -0500
Message-ID: <21717.1203033752@sss.pgh.pa.us> (raw)
In-Reply-To: <Pine.GSO.4.64.0802141836390.13776@westnet.com>
References: <BAY111-W56DE7AD29405FE5C2BF1A93250@phx.gbl>
<Pine.GSO.4.64.0802141836390.13776@westnet.com>
Greg Smith <gsmith@gregsmith.com> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, gsmith@gregsmith.com, mlorenz1@hotmail.com
Subject: Re: Query slows after offset of 100K
In-Reply-To: <21717.1203033752@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox