Re: performance of SELECT * much faster than SELECT with large offset

On Fri, Oct 3, 2014 at 5:39 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Marc Slemko <marcs@znep.com> writes:
>> I ran into this oddity lately that goes against everything I thought I
>> understood and was wondering if anyone had any insight.
>
> SELECT * avoids a projection step ... see ExecAssignScanProjectionInfo.

It would be cool if OFFSET could somehow signal the child nodes "don't
bother constructing the actual tuple". Not sure if that could work in
more complex queries. But this is just one of many performance
problems with large OFFSETs.

Of course you can always work around this using a subquery...
select description from (
  select * from ccrimes offset 5140000 limit 1
) subq;

But most of the time it's better to use scalable paging techniques:
http://use-the-index-luke.com/sql/partial-results/fetch-next-page

Regards,
Marti