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.stehule@gmail.com> wrote:
>     čt 16. 3. 2023 v 9:55 odesílatel Dominique Devienne <ddevienne@gmail.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@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"