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/
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... |