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.johnston@xxxxxxxxx> 于2020年12月7日周一 上午12:05写道：
On Sunday, December 6, 2020, 孙冰 <subi.the.dream.walker@xxxxxxxxx> wrote:The skipped rows by an
OFFSETclause 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.
