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