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
On Sunday, December 6, 2020, 孙冰 <subi.the.dream.walker@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.