From: PFC <lists@peufeu.com>
To: Andrus <kobruleht2@hot.ee>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: limit clause produces wrong query plan
Date: Mon, 24 Nov 2008 23:58:50 +0100
Message-ID: <op.uk5bgcmqcigqcu@soyouz> (raw)
In-Reply-To: <372C245439584A6CBF49F996503BF162@andrusnotebook>
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>
> 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...
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: lists@peufeu.com, kobruleht2@hot.ee, scott.marlowe@gmail.com
Subject: Re: limit clause produces wrong query plan
In-Reply-To: <op.uk5bgcmqcigqcu@soyouz>
* 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