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

On Thu, Mar 16, 2023 at 11:15 AM Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
wrote:

> č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?
>>
>
> COPY is a different creature - it has no execution plan, and it is not
> interpreted by the executor.
>

OK. Not sure what that means exactly. There's still a SELECT, with possibly
WHERE clauses and/or JOINs, no?
Doesn't that imply an execution plan? I'm a bit confused.

> Using COPY SELECT instead SELECT looks like premature optimization.
>

Possible. But this is not an e-commerce web-site with a PostgreSQL backend
here.
This is classical client-server with heavy weight desktop apps loading
heavy weight data
(in number and size) from PostgreSQL. So performance (throughput) does
matter a lot to us.
And I measure that performance in both rows/sec and MB/sec, not (itsy
bitsy) transactions / sec.

> The performance benefit will be minimal ([...]).
>

COPY matters on INSERT for sure performance-wise.
So why wouldn't COPY matter for SELECTs too?

> Cursors, queries can use binary protocol, if the client can support it.
>

I already do. But we need all the speed we can get.
In any case, I'll have to try and see/test for myself eventually.
We cannot afford to leave any performance gains on the table.