I think the subquery approach should be something like: --- select id, pg_sleep(0.1) from (select id from thing offset 90 order by tag) last_things ---
Is that right? Then what if the interface is exposed as a view? e.g., --- create view thing_interface as select id, tag, pg_sleep(0.1) from thing; --- I can't think of a subquery which can avoid the unnecessary pg_sleep calls when queries are executed against thing_interface. It's perfectly valid that the problem could be solved by a subquery for some *ad-hoc* and *oneshot* queries. But there are more often the cases that limit-offset are used in general queries (hand-crafted or programe-generated) and it is not very realistic to rewrite all of them into an offset-inside-subquery form. Bing David G. Johnston <david.g.johns...@gmail.com> 于2020年12月7日周一 上午12:05写道： > On Sunday, December 6, 2020, 孙冰 <subi.the.dream.wal...@gmail.com> wrote: > >> The skipped rows by an OFFSET clause have to be computed nevertheless. I >> am wondering if there could be any chance to improve, since the computation >> is on the *entire* rows rather than on the *criterial* columns. >> >> [...] >> >> I don't understand the postgresql internal, but I suspect such a change >> may introduce significant work on the planner and executor. From my point >> view, skipping everything (or expensive ones) except the criteria in the >> target list would greatly improve the usability of OFFSET, and it is >> definitely worth the effort. >> > > Given that one can write this with a subquery without much difficulty i’m > doubtful that effort spent in this area is going to be particularly > valuable. > > David J. > >
