Re: limit clause produces wrong query plan

From: Chris <dmagick(at)gmail(dot)com>
To: Andrus <kobruleht2(at)hot(dot)ee>
Cc: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: limit clause produces wrong query plan
Date: 2008-11-24 22:19:55
Message-ID: 492B288B.9070308@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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/

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Scott Carey 2008-11-24 22:50:56 Re: Increasing pattern index query speed
Previous Message Scott Marlowe 2008-11-24 21:24:47 Re: Monitoring buffercache...