Date: Mon, 24 Nov 2008 23:58:50 +0100
To: Andrus <kobruleht2@hot.ee>, "Scott Marlowe" <scott.marlowe@gmail.com>
Subject: Re: limit clause produces wrong query plan
From: PFC <lists@peufeu.com>
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8
MIME-Version: 1.0
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>
Content-Transfer-Encoding: 7bit
Message-ID: <op.uk5bgcmqcigqcu@soyouz>
In-Reply-To: <372C245439584A6CBF49F996503BF162@andrusnotebook>
User-Agent: Opera Mail/9.62 (Linux)

> SELECT ... FROM bigtable ORDER BY intprimarykey OFFSET 100 LIMIT 100

	I think pagination is overrated.

	If the query produces, for instance, something like 100 rows or less,  
more often than not, getting all the rows will take the exact same time as  
getting a portion of the rows... in all those cases, it is much better to  
cache the results somewhere (user session, table, whatever) and paginate  
based on that, rather than perform the same query lots of times.  
Especially when some non-indexed sorting takes place in which case you are  
gonna fetch all the rows anyway. Something like row-id can be stored  
instead of the full rows, also. There are exceptions of course.

	And if the query produces 20.000 results... who is ever going to scroll  
to page 1257 ?

> The example which I  posted shows that
>
> SELECT ... FROM bigtable ORDER BY intprimarykey LIMIT 100
>
> this is extremely *slow*: seq scan is  performed over whole bigtable.

	This is wrong though. It should use an index, especially if you have a  
LIMIT...