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

On 2023-03-16 11:52:47 +0100, Dominique Devienne wrote:
> 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:
> That's a nice way to put it Pavel.
> And to have it both ways, use COPY in binary protocol?
[...]
> The performance benefit will be minimal ([...]).
>
> COPY matters on INSERT for sure performance-wise.
> So why wouldn't COPY matter for SELECTs too?

COPY is faster than a bunch of INSERTs because each INSERT has some
overhead: It needs to be parsed (if you PREPAREd the INSERT you need to
parse the EXECUTE command instead) and planned. But most importantly you
have a round trip time between the client and the server. With COPY you
incur that overhead only once.

(Which reminds me that I should benchmark INSERT with lots of VALUES
against COPY some time.)

With COPYing the output of a SELECT I don't see any savings. On the
contrary, it's an extra step.

hp

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"