pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedUNION and bad performance
7+ messages / 7 participants
[nested] [flat]
* UNION and bad performance
@ 2010-12-09 10:52 Olivier Pala <olivier.pala@xgs-france.com>
0 siblings, 2 replies; 7+ messages in thread
From: Olivier Pala @ 2010-12-09 10:52 UTC (permalink / raw)
To: pgsql-performance; +Cc: Olivier Pala <olivier.pala@xgs-france.com>
Hi,
I have a performance trouble with UNION query
First I have this view :
SELECT a,b,c FROM table1, table2 WHERE jointure AND condition
Result : 150 000 records in 1~2 s
Then, I adding an UNION into the same view :
SELECT a,b,c FROM table1, table2 WHERE jointure AND condition
UNION
SELECT a,b,c FROM table3
Result : 150 200 records in 6~7 s
Why, do I have bad performance only for 200 adding records ?
Thanks
*SGBD : Postgres 8.3 et 8.4*
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: UNION and bad performance
@ 2010-12-11 10:27 Marc Mamin <M.Mamin@intershop.de>
parent: Olivier Pala <olivier.pala@xgs-france.com>
1 sibling, 1 reply; 7+ messages in thread
From: Marc Mamin @ 2010-12-11 10:27 UTC (permalink / raw)
To: Olivier Pala <olivier.pala@xgs-france.com>; pgsql-performance
Hello,
UNION will remove all duplicates, so that the result additionally
requires to be sorted.
Anyway, for performance issues, you should always start investigation
with explain analyze .
regards,
Marc Mamin
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Olivier
Pala
Sent: Donnerstag, 9. Dezember 2010 11:52
To: pgsql-performance@postgresql.org
Cc: Olivier Pala
Subject: [PERFORM] UNION and bad performance
Hi,
I have a performance trouble with UNION query
First I have this view :
SELECT a,b,c FROM table1, table2 WHERE jointure AND condition
Result : 150 000 records in 1~2 s
Then, I adding an UNION into the same view :
SELECT a,b,c FROM table1, table2 WHERE jointure AND condition
UNION
SELECT a,b,c FROM table3
Result : 150 200 records in 6~7 s
Why, do I have bad performance only for 200 adding records ?
Thanks
SGBD : Postgres 8.3 et 8.4
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: UNION and bad performance
@ 2010-12-11 13:45 Andreas Kretschmer <akretschmer@spamfence.net>
parent: Marc Mamin <M.Mamin@intershop.de>
0 siblings, 0 replies; 7+ messages in thread
From: Andreas Kretschmer @ 2010-12-11 13:45 UTC (permalink / raw)
To: pgsql-performance
Marc Mamin <M.Mamin@intershop.de> wrote:
> Hello,
>
>
>
> UNION will remove all duplicates, so that the result additionally requires to
> be sorted.
Right, to avoid the SORT and UNIQUE - operation you can use UNION ALL
>
> Anyway, for performance issues, you should always start investigation with
> explain analyze .
ACK.
Arguments to support bottom-posting...
A: Because we read from top to bottom, left to right.
Q: Why should I start my reply below the quoted text?
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
A: The lost context.
Q: What makes top-posted replies harder to read than bottom-posted?
A: Yes.
Q: Should I trim down the quoted part of an email to which I'm replying?
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: UNION and bad performance
@ 2010-12-12 13:12 pasman pasmański <pasman.p@gmail.com>
parent: Olivier Pala <olivier.pala@xgs-france.com>
1 sibling, 1 reply; 7+ messages in thread
From: pasman pasmański @ 2010-12-12 13:12 UTC (permalink / raw)
To: pgsql-performance
> UNION will remove all duplicates, so that the result additionally requires to
> be sorted.
>Right, to avoid the SORT and UNIQUE - operation you can use UNION ALL
by the way maybe apply hashing to calculate UNION be better ?
------------
pasman
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: UNION and bad performance
@ 2010-12-20 18:57 Robert Haas <robertmhaas@gmail.com>
parent: pasman pasmański <pasman.p@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Robert Haas @ 2010-12-20 18:57 UTC (permalink / raw)
To: pasman pasmański <pasman.p@gmail.com>; +Cc: pgsql-performance
2010/12/12 pasman pasmański <pasman.p@gmail.com>:
>> UNION will remove all duplicates, so that the result additionally requires to
>> be sorted.
>
>>Right, to avoid the SORT and UNIQUE - operation you can use UNION ALL
>
>
> by the way maybe apply hashing to calculate UNION be better ?
The planner already considers such plans.
rhaas=# explain select a from generate_series(1,100) a union select a
from generate_series(1,100) a;
QUERY PLAN
--------------------------------------------------------------------------------------
HashAggregate (cost=45.00..65.00 rows=2000 width=4)
-> Append (cost=0.00..40.00 rows=2000 width=4)
-> Function Scan on generate_series a (cost=0.00..10.00
rows=1000 width=4)
-> Function Scan on generate_series a (cost=0.00..10.00
rows=1000 width=4)
(4 rows)
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: UNION and bad performance
@ 2014-06-08 13:58 pinker <pinker@onet.eu>
parent: Robert Haas <robertmhaas@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: pinker @ 2014-06-08 13:58 UTC (permalink / raw)
To: pgsql-performance
>> rhaas=# explain select a from generate_series(1,100) a union select a
>> from generate_series(1,100) a;
>> QUERY PLAN
>> --------------------------------------------------------------------------------------
>> HashAggregate (cost=45.00..65.00 rows=2000 width=4)
>> -> Append (cost=0.00..40.00 rows=2000 width=4)
Why in this case the estimated number of rows is 2000? Is it standard
planner behavior?
--
View this message in context: http://postgresql.1045698.n5.nabble.com/UNION-and-bad-performance-tp3301375p5806445.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] 7+ messages in thread
* Re: UNION and bad performance
@ 2014-06-08 15:53 David G Johnston <david.g.johnston@gmail.com>
parent: pinker <pinker@onet.eu>
0 siblings, 0 replies; 7+ messages in thread
From: David G Johnston @ 2014-06-08 15:53 UTC (permalink / raw)
To: pgsql-performance
pinker wrote
>>> rhaas=# explain select a from generate_series(1,100) a union select a
>>> from generate_series(1,100) a;
>>> QUERY PLAN
>>> --------------------------------------------------------------------------------------
>>> HashAggregate (cost=45.00..65.00 rows=2000 width=4)
>>> -> Append (cost=0.00..40.00 rows=2000 width=4)
>
>
> Why in this case the estimated number of rows is 2000? Is it standard
> planner behavior?
http://www.postgresql.org/docs/9.1/static/sql-createfunction.html
Note the "ROWS" property.
Functions are black-boxes to the planner so it has no means of estimating a
row count. So a set returning function uses 1,000 and all others use 1.
Determining "COST" is similarly problematic.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/UNION-and-bad-performance-tp3301375p5806450.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] 7+ messages in thread
end of thread, other threads:[~2014-06-08 15:53 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-12-09 10:52 UNION and bad performance Olivier Pala <olivier.pala@xgs-france.com>
2010-12-11 10:27 ` Marc Mamin <M.Mamin@intershop.de>
2010-12-11 13:45 ` Andreas Kretschmer <akretschmer@spamfence.net>
2010-12-12 13:12 ` pasman pasmański <pasman.p@gmail.com>
2010-12-20 18:57 ` Robert Haas <robertmhaas@gmail.com>
2014-06-08 13:58 ` pinker <pinker@onet.eu>
2014-06-08 15:53 ` David G Johnston <david.g.johnston@gmail.com>
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