Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-performance by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group