Re: The hidden cost of limit-offset

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(dot)g(dot)johnston(at)gmail(dot)com> 于2020年12月7日周一 上午12:05写道：

> On Sunday, December 6, 2020, 孙冰 <subi(dot)the(dot)dream(dot)walker(at)gmail(dot)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.
>
>