DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:cc:subject
	:references:in-reply-to:content-type:content-transfer-encoding;
	b=DV+Y8d0nOvpffjr+1fhrlukCnp6uUfKBdK83gAjOTWHuNjDMVdROByV1YRKKUFJ4/v
	Xi37BBOvjlogUodQ/6qFi/69d+mxsSYdS5dUOIPHrIVMffko1rLgnQzlb7adT8op9VJy
	ihVh7zo0Im8h1BBJXlGZEoPzd9QnHrSJYBNHQ=
Message-ID: <492B288B.9070308@gmail.com>
Date: Tue, 25 Nov 2008 09:19:55 +1100
From: Chris <dmagick@gmail.com>
User-Agent: Thunderbird 2.0.0.17 (X11/20080925)
MIME-Version: 1.0
To: Andrus <kobruleht2@hot.ee>
CC: Scott Marlowe <scott.marlowe@gmail.com>, pgsql-performance@postgresql.org
Subject: Re: limit clause produces wrong query plan
References: <ggciha$1v26$1@news.hub.org>
	<0e6967e0-fb33-49aa-a745-a1d11ef6a9e0@q9g2000yqc.googlegroups.com>
	<gget6m$24rv$1@news.hub.org>
	<dcc563d10811241123q5b481020w57ac6622301c1d65@mail.gmail.com>
	<372C245439584A6CBF49F996503BF162@andrusnotebook>
In-Reply-To: <372C245439584A6CBF49F996503BF162@andrusnotebook>
Content-Type: text/plain; charset=iso-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Andrus wrote:
> Scott,
> 
>> And how exactly should it be optimized?  If a query is even moderately
>> interesting, with a few joins and a where clause, postgresql HAS to
>> create the rows that come before your offset in order to assure that
>> it's giving you the right rows.
> 
> SELECT ... FROM bigtable ORDER BY intprimarykey OFFSET 100 LIMIT 100
> 
> It should scan primary key in index order for 200 first keys and 
> skipping first 100 keys.

... which if you have a lot of table joins, unions/intersects/whatever 
else, should be done on which field and how?

For a query like:

select * t1 join t2 using (id) where t1.id='x' order by t1.id limit 100;

it has to join the tables first (may involve a seq scan) to make sure 
the id's match up, reduce the number of rows to match the where clause 
(may/may not be done first, I don't know) - the limit is applied last.

it can't grab the first 100 entries from t1 - because they might not 
have a matching id in t2, let alone match the where clause.

-- 
Postgresql & php tutorials
http://www.designmagick.com/