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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Fri, Oct 3, 2014 at 5:39 AM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
> Marc Slemko <marcs@xxxxxxxx> 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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance




[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux