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

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

> 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?
>

Please, can you show some benchmarks :-) I don't believe it.

The protocol is already designed for massive reading by queries. If COPY
SELECT is significantly faster than SELECT, then some should be wrong on
some side (server or client).

Regards

Pavel

>
>> 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.
>