From: 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 22:04:54 +0200
Message-ID: <372C245439584A6CBF49F996503BF162@andrusnotebook> (raw)
In-Reply-To: <dcc563d10811241123q5b481020w57ac6622301c1d65@mail.gmail.com>
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>
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.
>> SELECT ... FROM bigtable ORDER BY intprimarykey OFFSET 0 LIMIT 100
>
> That should be plenty fast.
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.
> A standard workaround is to use some kind of sequential, or nearly so,
> id field, and then use between on that field.
>
> select * from table where idfield between x and x+100;
Users can delete and insert any rows in table.
This appoarch requires updating x in every row in big table after each
insert, delete or order column change and is thus extremely slow.
So I do'nt understand how this can be used for large tables.
Andrus.
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: kobruleht2@hot.ee, scott.marlowe@gmail.com
Subject: Re: limit clause produces wrong query plan
In-Reply-To: <372C245439584A6CBF49F996503BF162@andrusnotebook>
* 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