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