pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedCopy command Faster than original select
17+ messages / 6 participants
[nested] [flat]
* Copy command Faster than original select
@ 2015-02-06 08:30 belal <belalhamed@gmail.com>
2015-02-06 08:38 ` Re: Copy command Faster than original select Pavel Stehule <pavel.stehule@gmail.com>
2015-02-06 15:24 ` Re: Copy command Faster than original select Igor Neyman <ineyman@perceptron.com>
2015-02-06 15:48 ` Re: Copy command Faster than original select Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 3 replies; 17+ messages in thread
From: belal @ 2015-02-06 08:30 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Copy command Faster than original select
2015-02-06 08:30 Copy command Faster than original select belal <belalhamed@gmail.com>
@ 2015-02-06 08:38 ` Pavel Stehule <pavel.stehule@gmail.com>
2015-02-06 08:44 ` Re: Copy command Faster than original select Belal Al-Hamed <belalhamed@gmail.com>
2 siblings, 1 reply; 17+ messages in thread
From: Pavel Stehule @ 2015-02-06 08:38 UTC (permalink / raw)
To: belal <belalhamed@gmail.com>; +Cc: pgsql-performance
Hi
2015-02-06 9:30 GMT+01:00 belal <belalhamed@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
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Copy command Faster than original select
2015-02-06 08:30 Copy command Faster than original select belal <belalhamed@gmail.com>
2015-02-06 08:38 ` Re: Copy command Faster than original select Pavel Stehule <pavel.stehule@gmail.com>
@ 2015-02-06 08:44 ` Belal Al-Hamed <belalhamed@gmail.com>
2015-02-06 08:54 ` Re: Copy command Faster than original select Pavel Stehule <pavel.stehule@gmail.com>
2015-02-06 10:36 ` Re: Copy command Faster than original select Matheus de Oliveira <matioli.matheus@gmail.com>
0 siblings, 2 replies; 17+ messages in thread
From: Belal Al-Hamed @ 2015-02-06 08:44 UTC (permalink / raw)
To: pgsql-performance
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.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Copy command Faster than original select
2015-02-06 08:30 Copy command Faster than original select belal <belalhamed@gmail.com>
2015-02-06 08:38 ` Re: Copy command Faster than original select Pavel Stehule <pavel.stehule@gmail.com>
2015-02-06 08:44 ` Re: Copy command Faster than original select Belal Al-Hamed <belalhamed@gmail.com>
@ 2015-02-06 08:54 ` Pavel Stehule <pavel.stehule@gmail.com>
2015-02-06 09:15 ` Re: Copy command Faster than original select Belal Al-Hamed <belalhamed@gmail.com>
1 sibling, 1 reply; 17+ messages in thread
From: Pavel Stehule @ 2015-02-06 08:54 UTC (permalink / raw)
To: Belal Al-Hamed <belalhamed@gmail.com>; +Cc: pgsql-performance
2015-02-06 9:44 GMT+01:00 Belal Al-Hamed <belalhamed@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
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Copy command Faster than original select
2015-02-06 08:30 Copy command Faster than original select belal <belalhamed@gmail.com>
2015-02-06 08:38 ` Re: Copy command Faster than original select Pavel Stehule <pavel.stehule@gmail.com>
2015-02-06 08:44 ` Re: Copy command Faster than original select Belal Al-Hamed <belalhamed@gmail.com>
2015-02-06 08:54 ` Re: Copy command Faster than original select Pavel Stehule <pavel.stehule@gmail.com>
@ 2015-02-06 09:15 ` Belal Al-Hamed <belalhamed@gmail.com>
2015-02-06 09:31 ` Re: Copy command Faster than original select Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Belal Al-Hamed @ 2015-02-06 09:15 UTC (permalink / raw)
To: pgsql-performance
"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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Copy command Faster than original select
2015-02-06 08:30 Copy command Faster than original select belal <belalhamed@gmail.com>
2015-02-06 08:38 ` Re: Copy command Faster than original select Pavel Stehule <pavel.stehule@gmail.com>
2015-02-06 08:44 ` Re: Copy command Faster than original select Belal Al-Hamed <belalhamed@gmail.com>
2015-02-06 08:54 ` Re: Copy command Faster than original select Pavel Stehule <pavel.stehule@gmail.com>
2015-02-06 09:15 ` Re: Copy command Faster than original select Belal Al-Hamed <belalhamed@gmail.com>
@ 2015-02-06 09:31 ` Pavel Stehule <pavel.stehule@gmail.com>
2015-02-06 09:50 ` Re: Copy command Faster than original select Belal Al-Hamed <belalhamed@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Pavel Stehule @ 2015-02-06 09:31 UTC (permalink / raw)
To: Belal Al-Hamed <belalhamed@gmail.com>; +Cc: pgsql-performance
2015-02-06 10:15 GMT+01:00 Belal Al-Hamed <belalhamed@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
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Copy command Faster than original select
2015-02-06 08:30 Copy command Faster than original select belal <belalhamed@gmail.com>
2015-02-06 08:38 ` Re: Copy command Faster than original select Pavel Stehule <pavel.stehule@gmail.com>
2015-02-06 08:44 ` Re: Copy command Faster than original select Belal Al-Hamed <belalhamed@gmail.com>
2015-02-06 08:54 ` Re: Copy command Faster than original select Pavel Stehule <pavel.stehule@gmail.com>
2015-02-06 09:15 ` Re: Copy command Faster than original select Belal Al-Hamed <belalhamed@gmail.com>
2015-02-06 09:31 ` Re: Copy command Faster than original select Pavel Stehule <pavel.stehule@gmail.com>
@ 2015-02-06 09:50 ` Belal Al-Hamed <belalhamed@gmail.com>
2015-02-06 09:55 ` Re: Copy command Faster than original select Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Belal Al-Hamed @ 2015-02-06 09:50 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Copy command Faster than original select
2015-02-06 08:30 Copy command Faster than original select belal <belalhamed@gmail.com>
2015-02-06 08:38 ` Re: Copy command Faster than original select Pavel Stehule <pavel.stehule@gmail.com>
2015-02-06 08:44 ` Re: Copy command Faster than original select Belal Al-Hamed <belalhamed@gmail.com>
2015-02-06 08:54 ` Re: Copy command Faster than original select Pavel Stehule <pavel.stehule@gmail.com>
2015-02-06 09:15 ` Re: Copy command Faster than original select Belal Al-Hamed <belalhamed@gmail.com>
2015-02-06 09:31 ` Re: Copy command Faster than original select Pavel Stehule <pavel.stehule@gmail.com>
2015-02-06 09:50 ` Re: Copy command Faster than original select Belal Al-Hamed <belalhamed@gmail.com>
@ 2015-02-06 09:55 ` Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 17+ messages in thread
From: Pavel Stehule @ 2015-02-06 09:55 UTC (permalink / raw)
To: Belal Al-Hamed <belalhamed@gmail.com>; +Cc: pgsql-performance
2015-02-06 10:50 GMT+01:00 Belal Al-Hamed <belalhamed@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
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Copy command Faster than original select
2015-02-06 08:30 Copy command Faster than original select belal <belalhamed@gmail.com>
2015-02-06 08:38 ` Re: Copy command Faster than original select Pavel Stehule <pavel.stehule@gmail.com>
2015-02-06 08:44 ` Re: Copy command Faster than original select Belal Al-Hamed <belalhamed@gmail.com>
@ 2015-02-06 10:36 ` Matheus de Oliveira <matioli.matheus@gmail.com>
2015-02-06 12:27 ` Re: Copy command Faster than original select Belal Al-Hamed <belalhamed@gmail.com>
1 sibling, 1 reply; 17+ messages in thread
From: Matheus de Oliveira @ 2015-02-06 10:36 UTC (permalink / raw)
To: Belal Al-Hamed <belalhamed@gmail.com>; +Cc: pgsql-performance
On Fri, Feb 6, 2015 at 6:44 AM, Belal Al-Hamed <belalhamed@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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Copy command Faster than original select
2015-02-06 08:30 Copy command Faster than original select belal <belalhamed@gmail.com>
2015-02-06 08:38 ` Re: Copy command Faster than original select Pavel Stehule <pavel.stehule@gmail.com>
2015-02-06 08:44 ` Re: Copy command Faster than original select Belal Al-Hamed <belalhamed@gmail.com>
2015-02-06 10:36 ` Re: Copy command Faster than original select Matheus de Oliveira <matioli.matheus@gmail.com>
@ 2015-02-06 12:27 ` Belal Al-Hamed <belalhamed@gmail.com>
2015-02-06 13:12 ` Re: Copy command Faster than original select Matheus de Oliveira <matioli.matheus@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Belal Al-Hamed @ 2015-02-06 12:27 UTC (permalink / raw)
To: pgsql-performance
"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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Copy command Faster than original select
2015-02-06 08:30 Copy command Faster than original select belal <belalhamed@gmail.com>
2015-02-06 08:38 ` Re: Copy command Faster than original select Pavel Stehule <pavel.stehule@gmail.com>
2015-02-06 08:44 ` Re: Copy command Faster than original select Belal Al-Hamed <belalhamed@gmail.com>
2015-02-06 10:36 ` Re: Copy command Faster than original select Matheus de Oliveira <matioli.matheus@gmail.com>
2015-02-06 12:27 ` Re: Copy command Faster than original select Belal Al-Hamed <belalhamed@gmail.com>
@ 2015-02-06 13:12 ` Matheus de Oliveira <matioli.matheus@gmail.com>
2015-02-06 13:39 ` Re: Copy command Faster than original select Belal Al-Hamed <belalhamed@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Matheus de Oliveira @ 2015-02-06 13:12 UTC (permalink / raw)
To: Belal Al-Hamed <belalhamed@gmail.com>; +Cc: pgsql-performance
On Fri, Feb 6, 2015 at 10:27 AM, Belal Al-Hamed <belalhamed@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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Copy command Faster than original select
2015-02-06 08:30 Copy command Faster than original select belal <belalhamed@gmail.com>
2015-02-06 08:38 ` Re: Copy command Faster than original select Pavel Stehule <pavel.stehule@gmail.com>
2015-02-06 08:44 ` Re: Copy command Faster than original select Belal Al-Hamed <belalhamed@gmail.com>
2015-02-06 10:36 ` Re: Copy command Faster than original select Matheus de Oliveira <matioli.matheus@gmail.com>
2015-02-06 12:27 ` Re: Copy command Faster than original select Belal Al-Hamed <belalhamed@gmail.com>
2015-02-06 13:12 ` Re: Copy command Faster than original select Matheus de Oliveira <matioli.matheus@gmail.com>
@ 2015-02-06 13:39 ` Belal Al-Hamed <belalhamed@gmail.com>
2015-02-06 13:49 ` Re: Copy command Faster than original select Pavel Stehule <pavel.stehule@gmail.com>
2015-02-06 15:11 ` Re: Copy command Faster than original select Matheus de Oliveira <matioli.matheus@gmail.com>
0 siblings, 2 replies; 17+ messages in thread
From: Belal Al-Hamed @ 2015-02-06 13:39 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Copy command Faster than original select
2015-02-06 08:30 Copy command Faster than original select belal <belalhamed@gmail.com>
2015-02-06 08:38 ` Re: Copy command Faster than original select Pavel Stehule <pavel.stehule@gmail.com>
2015-02-06 08:44 ` Re: Copy command Faster than original select Belal Al-Hamed <belalhamed@gmail.com>
2015-02-06 10:36 ` Re: Copy command Faster than original select Matheus de Oliveira <matioli.matheus@gmail.com>
2015-02-06 12:27 ` Re: Copy command Faster than original select Belal Al-Hamed <belalhamed@gmail.com>
2015-02-06 13:12 ` Re: Copy command Faster than original select Matheus de Oliveira <matioli.matheus@gmail.com>
2015-02-06 13:39 ` Re: Copy command Faster than original select Belal Al-Hamed <belalhamed@gmail.com>
@ 2015-02-06 13:49 ` Pavel Stehule <pavel.stehule@gmail.com>
1 sibling, 0 replies; 17+ messages in thread
From: Pavel Stehule @ 2015-02-06 13:49 UTC (permalink / raw)
To: Belal Al-Hamed <belalhamed@gmail.com>; +Cc: pgsql-performance
2015-02-06 14:39 GMT+01:00 Belal Al-Hamed <belalhamed@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
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Copy command Faster than original select
2015-02-06 08:30 Copy command Faster than original select belal <belalhamed@gmail.com>
2015-02-06 08:38 ` Re: Copy command Faster than original select Pavel Stehule <pavel.stehule@gmail.com>
2015-02-06 08:44 ` Re: Copy command Faster than original select Belal Al-Hamed <belalhamed@gmail.com>
2015-02-06 10:36 ` Re: Copy command Faster than original select Matheus de Oliveira <matioli.matheus@gmail.com>
2015-02-06 12:27 ` Re: Copy command Faster than original select Belal Al-Hamed <belalhamed@gmail.com>
2015-02-06 13:12 ` Re: Copy command Faster than original select Matheus de Oliveira <matioli.matheus@gmail.com>
2015-02-06 13:39 ` Re: Copy command Faster than original select Belal Al-Hamed <belalhamed@gmail.com>
@ 2015-02-06 15:11 ` Matheus de Oliveira <matioli.matheus@gmail.com>
1 sibling, 0 replies; 17+ messages in thread
From: Matheus de Oliveira @ 2015-02-06 15:11 UTC (permalink / raw)
To: Belal Al-Hamed <belalhamed@gmail.com>; +Cc: pgsql-performance
On Fri, Feb 6, 2015 at 11:39 AM, Belal Al-Hamed <belalhamed@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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Copy command Faster than original select
2015-02-06 08:30 Copy command Faster than original select belal <belalhamed@gmail.com>
@ 2015-02-06 15:24 ` Igor Neyman <ineyman@perceptron.com>
2015-02-08 10:08 ` Re: Copy command Faster than original select Belal Al-Hamed <belalhamed@gmail.com>
2 siblings, 1 reply; 17+ messages in thread
From: Igor Neyman @ 2015-02-06 15:24 UTC (permalink / raw)
To: belal <belalhamed@gmail.com>; pgsql-performance
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@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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Copy command Faster than original select
2015-02-06 08:30 Copy command Faster than original select belal <belalhamed@gmail.com>
2015-02-06 15:24 ` Re: Copy command Faster than original select Igor Neyman <ineyman@perceptron.com>
@ 2015-02-08 10:08 ` Belal Al-Hamed <belalhamed@gmail.com>
0 siblings, 0 replies; 17+ messages in thread
From: Belal Al-Hamed @ 2015-02-08 10:08 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Copy command Faster than original select
2015-02-06 08:30 Copy command Faster than original select belal <belalhamed@gmail.com>
@ 2015-02-06 15:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 17+ messages in thread
From: Tom Lane @ 2015-02-06 15:48 UTC (permalink / raw)
To: belal <belalhamed@gmail.com>; +Cc: 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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2015-02-08 10:08 UTC | newest]
Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-02-06 08:30 Copy command Faster than original select belal <belalhamed@gmail.com>
2015-02-06 08:38 ` Pavel Stehule <pavel.stehule@gmail.com>
2015-02-06 08:44 ` Belal Al-Hamed <belalhamed@gmail.com>
2015-02-06 08:54 ` Pavel Stehule <pavel.stehule@gmail.com>
2015-02-06 09:15 ` Belal Al-Hamed <belalhamed@gmail.com>
2015-02-06 09:31 ` Pavel Stehule <pavel.stehule@gmail.com>
2015-02-06 09:50 ` Belal Al-Hamed <belalhamed@gmail.com>
2015-02-06 09:55 ` Pavel Stehule <pavel.stehule@gmail.com>
2015-02-06 10:36 ` Matheus de Oliveira <matioli.matheus@gmail.com>
2015-02-06 12:27 ` Belal Al-Hamed <belalhamed@gmail.com>
2015-02-06 13:12 ` Matheus de Oliveira <matioli.matheus@gmail.com>
2015-02-06 13:39 ` Belal Al-Hamed <belalhamed@gmail.com>
2015-02-06 13:49 ` Pavel Stehule <pavel.stehule@gmail.com>
2015-02-06 15:11 ` Matheus de Oliveira <matioli.matheus@gmail.com>
2015-02-06 15:24 ` Igor Neyman <ineyman@perceptron.com>
2015-02-08 10:08 ` Belal Al-Hamed <belalhamed@gmail.com>
2015-02-06 15:48 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox