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/
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
