>> 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
In response to
pgsql-performance by date
|Next:||From: Scott Carey||Date: 2008-11-24 22:50:56|
|Subject: Re: Increasing pattern index query speed|
|Previous:||From: Scott Marlowe||Date: 2008-11-24 21:24:47|
|Subject: Re: Monitoring buffercache...|