From: Chris <dmagick@gmail.com>
To: Andrus <kobruleht2@hot.ee>
Cc: Scott Marlowe <scott.marlowe@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: limit clause produces wrong query plan
Date: Tue, 25 Nov 2008 09:19:55 +1100
Message-ID: <492B288B.9070308@gmail.com> (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>
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/
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: dmagick@gmail.com, kobruleht2@hot.ee, scott.marlowe@gmail.com
Subject: Re: limit clause produces wrong query plan
In-Reply-To: <492B288B.9070308@gmail.com>
* 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