Re: Copy command Faster than original select

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 text file, which will prove this theory.

Regards,
Igor Neyman

-----Original Message-----
From: pgsql-performance-owner(at)postgresql(dot)org [mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of belal
Sent: Friday, February 06, 2015 3:31 AM
To: pgsql-performance(at)postgresql(dot)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(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance