Обсуждение: Performance of UNION vs IN
I have a very complex view zinfoexp and running the view as: SELECT * FROM zinfoexp WHERE idmembre in (1,84) take 2700 ms So, I try another syntax: SELECT * FROM zinfoexp WHERE idmembre = 1 union SELECT * FROM zinfoexp WHERE idmembre = 84 and for me, two calls to my view takes a lot of time (may be x2) and it takes 134 ms ! How is it possible that the optimizer cannot optimize the IN as UNION ? I have a database postgresql 9.3 freshly vacuumed -- Jean-Max Reymond CKR Solutions Open Source http://www.ckr-solutions.com
On 20/03/14 17:57, Jean-Max Reymond wrote: > I have a very complex view zinfoexp and running the view as: > SELECT * FROM zinfoexp WHERE idmembre in (1,84) > take 2700 ms > > So, I try another syntax: > SELECT * FROM zinfoexp WHERE idmembre = 1 > union > SELECT * FROM zinfoexp WHERE idmembre = 84 > > and for me, two calls to my view takes a lot of time (may be x2) and it > takes 134 ms ! try SELECT * FROM zinfoexp WHERE idmembre=1 OR idmembre=84 This will probably be even faster. Also, the 2 statements of your's are not semantically equal. UNION implies DISTINCT, see: select * from (values (1), (1), (2)) t(i) UNION select 19; i ---- 19 1 2 (3 rows) What you want is UNION ALL: select * from (values (1), (1), (2)) t(i) UNION ALL select 19; i ---- 1 1 2 19 (4 rows) Torsten
Le 20/03/2014 18:13, Torsten Förtsch a écrit : > On 20/03/14 17:57, Jean-Max Reymond wrote: >> I have a very complex view zinfoexp and running the view as: >> SELECT * FROM zinfoexp WHERE idmembre in (1,84) >> take 2700 ms >> >> So, I try another syntax: >> SELECT * FROM zinfoexp WHERE idmembre = 1 >> union >> SELECT * FROM zinfoexp WHERE idmembre = 84 >> >> and for me, two calls to my view takes a lot of time (may be x2) and it >> takes 134 ms ! > > try > > SELECT * FROM zinfoexp WHERE idmembre=1 OR idmembre=84 > > This will probably be even faster. > > Also, the 2 statements of your's are not semantically equal. UNION > implies DISTINCT, see: > > select * from (values (1), (1), (2)) t(i) UNION select 19; > i > ---- > 19 > 1 > 2 > (3 rows) > > What you want is UNION ALL: > > select * from (values (1), (1), (2)) t(i) UNION ALL select 19; > i > ---- > 1 > 1 > 2 > 19 > (4 rows) > > > Torsten > same numbers with DISTINCT and UNION ALL (construction of VIEW does an implicit DISTINCT). -- Jean-Max Reymond Éruption de l'Etna: http://jmreymond.free.fr/Etna2002