Re: Is the PL/pgSQL refcursor useful in a modern three-tier app?

čt 16. 3. 2023 v 9:55 odesílatel Dominique Devienne <ddevienne(at)gmail(dot)com>
napsal:

> On Thu, Mar 16, 2023 at 9:23 AM Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
>
>> čt 16. 3. 2023 v 9:18 odesílatel Dominique Devienne <ddevienne(at)gmail(dot)com>
>> napsal:
>>
>>> [...] depends on what you value in a particular situation, latency or
>>> throughput. --DD
>>>
>>
>> cursors are optimized for minimal cost of first row, queries are
>> optimized for minimal cost of last row
>>
>
> That's a nice way to put it Pavel.
>
> And to have it both ways, use COPY in binary protocol? That way the rows
> are streamed
> to you in arbitrary chunks as soon as available (I hope), and the burden
> is on you the
> client to decode and use those rows in parallel as they are "streamed" to
> you.
>
> I've yet to test that (thus the 'i hope' above). I used COPY binary for
> INSERTs,
> and COPY text/json for SELECTs, not yet COPY binary for SELECTs. I'm hoping
> the latency of COPY will be small compared to a regular SELECT where I
> have to
> wait for LIBPQ to assemble the whole ResultSet. Are my hopes unfounded?
> --DD
>

COPY is a different creature - it has no execution plan, and it is not
interpreted by the executor.

Using COPY SELECT instead SELECT looks like premature optimization. The
performance benefit will be minimal (maybe there can be exceptions
depending on data, network properties or interface). Cursors, queries can
use binary protocol, if the client can support it.

Regards

Pavel