Re: limit clause produces wrong query plan

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/