Обсуждение: 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.

thanks,

but isn't copy use the same plan ???

any way this is the query play

"Sort  (cost=15402.76..15511.77 rows=43602 width=184)"
"  Output: "Sessions"."SesUser", "Vouchers"."VouID",
"Journals"."JurMuniment", "Journals"."JurRefID", "Journals"."JurDate",
"Vouchers"."VouJournal", "Vouchers"."VouMunNumber", "Vouchers"."VouDate",
"Vouchers"."VouNote", "Vouchers"."VouDoc", "Journals"."JurM (...)"
"  Sort Key: "VouItems"."ItmDate", "Vouchers"."VouID",
"VouItems"."ItmNumber""
"  ->  Hash Join  (cost=4665.21..8164.77 rows=43602 width=184)"
"        Output: "Sessions"."SesUser", "Vouchers"."VouID",
"Journals"."JurMuniment", "Journals"."JurRefID", "Journals"."JurDate",
"Vouchers"."VouJournal", "Vouchers"."VouMunNumber", "Vouchers"."VouDate",
"Vouchers"."VouNote", "Vouchers"."VouDoc", "Journals" (...)"
"        Hash Cond: ("VouItems"."ItmMaster" = "Vouchers"."VouID")"
"        ->  Seq Scan on public."VouItems"  (cost=0.00..1103.02 rows=43602
width=89)"
"              Output: "VouItems"."ItmMaster", "VouItems"."ItmNumber",
"VouItems"."ItmCurDebit", "VouItems"."ItmCurCredit",
"VouItems"."ItmAccount", "VouItems"."ItmBranch", "VouItems"."ItmSubAccount",
"VouItems"."ItmMuniment", "VouItems"."ItmDate", "VouItem (...)"
"        ->  Hash  (cost=4107.41..4107.41 rows=20544 width=95)"
"              Output: "Vouchers"."VouID", "Vouchers"."VouJournal",
"Vouchers"."VouMunNumber", "Vouchers"."VouDate", "Vouchers"."VouNote",
"Vouchers"."VouDoc", "Vouchers"."VouIsHold", "Vouchers"."VouCreateDate",
"Vouchers"."VouDebit", "Vouchers"."VouCredit" (...)"
"              ->  Hash Join  (cost=1793.25..4107.41 rows=20544 width=95)"
"                    Output: "Vouchers"."VouID", "Vouchers"."VouJournal",
"Vouchers"."VouMunNumber", "Vouchers"."VouDate", "Vouchers"."VouNote",
"Vouchers"."VouDoc", "Vouchers"."VouIsHold", "Vouchers"."VouCreateDate",
"Vouchers"."VouDebit", "Vouchers"."VouC (...)"
"                    Hash Cond: ("Vouchers"."VouJournal" =
"Journals"."JurID")"
"                    ->  Hash Join  (cost=1236.16..3165.12 rows=20544
width=74)"
"                          Output: "Vouchers"."VouID",
"Vouchers"."VouJournal", "Vouchers"."VouMunNumber", "Vouchers"."VouDate",
"Vouchers"."VouNote", "Vouchers"."VouDoc", "Vouchers"."VouIsHold",
"Vouchers"."VouCreateDate", "Vouchers"."VouDebit", "Vouchers" (...)"
"                          Hash Cond: ("Vouchers"."VouSession" =
"Sessions"."SesID")"
"                          ->  Seq Scan on public."Vouchers"
(cost=0.00..883.44 rows=20544 width=78)"
"                                Output: "Vouchers"."VouID",
"Vouchers"."VouJournal", "Vouchers"."VouMunNumber", "Vouchers"."VouDate",
"Vouchers"."VouNote", "Vouchers"."VouDoc", "Vouchers"."VouIsHold",
"Vouchers"."VouCreateDate", "Vouchers"."VouDebit", "Vou (...)"
"                          ->  Hash  (cost=654.85..654.85 rows=33385
width=12)"
"                                Output: "Sessions"."SesUser",
"Sessions"."SesID""
"                                ->  Seq Scan on public."Sessions"
(cost=0.00..654.85 rows=33385 width=12)"
"                                      Output: "Sessions"."SesUser",
"Sessions"."SesID""
"                    ->  Hash  (cost=417.04..417.04 rows=11204 width=29)"
"                          Output: "Journals"."JurMuniment",
"Journals"."JurRefID", "Journals"."JurDate", "Journals"."JurMunNumber",
"Journals"."JurID""
"                          ->  Seq Scan on public."Journals"
(cost=0.00..417.04 rows=11204 width=29)"
"                                Output: "Journals"."JurMuniment",
"Journals"."JurRefID", "Journals"."JurDate", "Journals"."JurMunNumber",
"Journals"."JurID""




--
View this message in context:
http://postgresql.nabble.com/Copy-command-Faster-than-original-select-tp5836886p5836890.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.

"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.

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.

"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.

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.

I think, it is the difference between writing 43602 records into the file and displaying 43602 records on screen.
If you wrap up your select into select count(a.*) from your select, e.g.:

Select count(a.*) from (select ... from mytable join .. join ... order by ....) as a;

This will exclude time to display all these rows, so you'll get the same (or better) performance as with "copy" into
textfile, which will prove this theory. 

Regards,
Igor Neyman

-----Original Message-----
From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of belal
Sent: Friday, February 06, 2015 3:31 AM
To: pgsql-performance@postgresql.org
Subject: [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

belal <belalhamed@gmail.com> writes:
> 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?

It's the time needed for PGAdmin to receive and display 43602 data rows,
likely.  PGAdmin has a reputation of not being too speedy at that.

You could check this by trying some other client such as psql.  Even
in psql, the formatting options you use can make a very large difference
in how fast it is.  However, I think psql's \timing option measures just
the server roundtrip time and not the time taken after that to format and
display the query result.  PGAdmin is probably measuring the query time
differently.

            regards, tom lane

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.