Re: Shortcutting too-large offsets?

On Fri, Sep 30, 2011 at 2:56 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Josh Berkus <josh(at)agliodbs(dot)com> writes:
>>> In principle, yeah, we could make it do that, but it seems like a likely
>>> source of maintenance headaches.  This example is not exactly compelling
>>> enough to make me want to do it.  Large OFFSETs are always going to be
>>> problematic from a performance standpoint, and the fact that we could
>>> short-circuit this one corner case isn't really going to make them much
>>> more usable.
>
>> It's not that uncommon of a corner case though; it's one which happens
>> all the time in webapps which paginate.  People just have to ask for a
>> page after the end.  It's really a question of how simple the code to
>> make the optimization would be; if it would be a 5-line patch, then it's
>> worth it; if it would be a 110-line refactor, no.
>
> No, it's really a question of whether it's worth any lines at all,
> and I remain unconvinced.  If someone asks for the last page, or any
> page near the end, it'll take just about the same amount of time as
> asking for a page past the end.  So any app like this is going to have
> sucky performance, and kluging the corner case isn't going to help much.

It looks to me like it took 22.3 seconds to do the nested loop and
then 22.4 seconds to do the nested loop plus the sort. So the sort
itself only took 100 ms, which is hardly worth getting excited about.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company