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
2015-02-06 9:44 GMT+01:00 Belal Al-Hamed belalha...@gmail.com: thanks, but isn't copy use the same plan ??? aha - I was wrong, this slowdown can be enforced by slow client (or slow network). pgAdmin is not terrible fast. Try to execute your query from psql. Regards Pavel 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. -- 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
On Fri, Feb 6, 2015 at 6:44 AM, Belal Al-Hamed belalha...@gmail.com wrote: 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) Can you try again but with EXPLAIN *ANALYZE* (not only EXPLAIN)? Best regards, -- Matheus de Oliveira Analista de Banco de Dados Dextra Sistemas - MPS.Br nível F! www.dextra.com.br/postgres
Re: [PERFORM] Copy command Faster than original select
2015-02-06 10:50 GMT+01:00 Belal Al-Hamed belalha...@gmail.com: fast as Query returned successfully: 43602 rows affected, 1089 ms execution time. so bottle neck have to be some where between client and server Pavel -- 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
Hi 2015-02-06 9:30 GMT+01:00 belal belalha...@gmail.com: 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? probably terrible uneffective execution plan can you send a explain analyze of your slow query? Regards Pavel 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
2015-02-06 10:15 GMT+01:00 Belal Al-Hamed belalha...@gmail.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 what is speed of CREATE TABLE xx AS SELECT /* your query */ ? regards Pavel 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
belal belalha...@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 -- 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] Copy command Faster than original select
2015-02-06 14:39 GMT+01:00 Belal Al-Hamed belalha...@gmail.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) ? COPY to filesystem can use a more CPU, and on modern computers, a data are stored to write cache first - and real IO operation can be processed later. PgAdmin uses only one CPU and works with expensive interactive element - grid - probably there are some space for optimization - usually fill 40K rows to pgAdmin is not good idea (it is not good idea for any client). -- 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
On Fri, Feb 6, 2015 at 11:39 AM, Belal Al-Hamed belalha...@gmail.com wrote: Let me change my question to this perhaps it would be clearer Perhaps if you answer all the questions asked, we'll be able to spot where is the bottleneck you are seeing. Could be many factors. Best regards, -- Matheus de Oliveira Analista de Banco de Dados Dextra Sistemas - MPS.Br nível F! www.dextra.com.br/postgres
Re: [PERFORM] Copy command Faster than original select
On Fri, Feb 6, 2015 at 10:27 AM, Belal Al-Hamed belalha...@gmail.com wrote: 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 Did you executed it from psql? Tried with \copy also? (sorry if you answered it already and I missed). 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 I'd like to see the EXPLAIN ANALYZE to see the real query execution time only (discarding even the write of output of COPY command), and also see if the query can be improved with an index or so, I haven't say it is a plan problem, as nothing suggested that so far. Also, what OS are you on? Are you connecting through TCP or domain socket? Best regards, -- Matheus de Oliveira Analista de Banco de Dados Dextra Sistemas - MPS.Br nível F! www.dextra.com.br/postgres
Re: [PERFORM] 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-ow...@postgresql.org [mailto:pgsql-performance-ow...@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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
