Re: [PERFORM] Copy command Faster than original select
Executing Select count(a.*) from (select ... from mytable join .. join ... order by ) as a; Total query runtime: 454 ms. 1 row retrieved. -- View this message in context: http://postgresql.nabble.com/Copy-command-Faster-than-original-select-tp5836886p5837105.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Copy command Faster than original select
I made complex select using PGAdmin III Query Editor, Postgre server 9.3 select ... from mytable join .. join ... order by I get [Total query runtime: 8841 ms. 43602 rows retrieved.] but when I use copy ([same above select]) to '/x.txt' I get [Query returned successfully: 43602 rows affected, 683 ms execution time.] these test made on the same machine as the postgresql server. can anyone explain huge difference in executing time? best regards all -- View this message in context: http://postgresql.nabble.com/Copy-command-Faster-than-original-select-tp5836886.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Copy command Faster than original select
this slowdown can be enforced by slow client (or slow network). As I said i made the tow test on the same machine as the server using PGAdmin no network involved. pgAdmin is not terrible fast I also try the same query from my application using libpq I get same results regards -- View this message in context: http://postgresql.nabble.com/Copy-command-Faster-than-original-select-tp5836886p5836893.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Copy command Faster than original select
fast as Query returned successfully: 43602 rows affected, 1089 ms execution time. -- View this message in context: http://postgresql.nabble.com/Copy-command-Faster-than-original-select-tp5836886p5836902.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Copy command Faster than original select
Let me change my question to this perhaps it would be clearer why writing data result of select statment from PG server to file on disk using copy statement is much faster than getting same data through PGAdmin via libpg on the same PC on the same system on the same connection (localhost) ? -- View this message in context: http://postgresql.nabble.com/Copy-command-Faster-than-original-select-tp5836886p5836933.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Copy command Faster than original select
so bottle neck have to be some where between client and server that's what I need to know ! where is the bug to made this performance Can you try again but with EXPLAIN *ANALYZE* (not only EXPLAIN)? it's not a matter of plan problem I think, it's related to sending data from server to client, perhaps in allocating buffers for data or problem in libpq I don't know ... because why it's super fast exporting same select to file using copy command. again I am using the same pc of the postgresql server regards to all -- View this message in context: http://postgresql.nabble.com/Copy-command-Faster-than-original-select-tp5836886p5836917.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] PostgreSQL over internet
Due to the asymmetric communication, a bigger data output in a single packet (the result of using compression on the tunnel) will get sent without waiting. A smaller packet will delay a bit waiting for some additional data, which in your case does not come. You may want to check out this document describing some of what I believe is causing your observed behavior: Slow Fast As I said before I try a small query and big one the result same using IP Tunnel is fast. You would find that if you log in to your DB server and use libpq to it over a localhost connection that the performance is good which points to your network as the problem. when I said I connect to localhost I meant I connect to IP tunnel client witch connect me to the remote PGServer Are you sure there's not any QOS somewhere that is slowing down the packets for port 5432 or whichever you're using for PostgreSQL? Perhaps temporarily changing PostgreSQL's listening port to something else might be a good test. yes I am sure, and if there is any it must affect both test. Best regards to all.
