8.4, One-Time Filter and subquery ( ... FROM function() union all ... )
От | Sergey Burladyan |
---|---|
Тема | 8.4, One-Time Filter and subquery ( ... FROM function() union all ... ) |
Дата | |
Msg-id | 87k52mbwb4.fsf@seb.progtech.ru обсуждение исходный текст |
Ответы |
Re: 8.4, One-Time Filter and subquery ( ... FROM function() union all ... )
(Sergey Burladyan <eshkinkot@gmail.com>)
Re: 8.4, One-Time Filter and subquery ( ... FROM function() union all ... ) (Tom Lane <tgl@sss.pgh.pa.us>) Re: 8.4, One-Time Filter and subquery ( ... FROM function() union all ... ) (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-hackers |
I am testing some of my queries with 8.4 and find some performance decline. 8.4 always execute functions in this subquery, even if result do not need it. 8.3 correctly optimize this and do not execute this functions, here is example: create function foo() returns int language sql as $$ select pg_sleep(5); select 1 $$; PostgreSQL 8.3.7 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debian 4.3.3-5) 4.3.3 EXPLAIN ANALYZE select * from (select 1 as i, r from foo() r union all select 2, r from foo() r) as x where i = 3; QUERY PLAN ------------------------------------------------------------------------------------------------Result (cost=0.00..0.54rows=2 width=8) (actual time=0.006..0.006 rows=0 loops=1) -> Append (cost=0.00..0.54 rows=2 width=8) (actualtime=0.003..0.003 rows=0 loops=1) -> Result (cost=0.00..0.26 rows=1 width=4) (actual time=0.001..0.001 rows=0loops=1) One-Time Filter: false -> Function Scan on foo r (cost=0.00..0.26 rows=1 width=4)(never executed) -> Result (cost=0.00..0.26 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=1) One-Time Filter: false -> Function Scan on foo r (cost=0.00..0.26 rows=1 width=4) (never executed)Totalruntime: 0.053 ms PostgreSQL 8.4.0 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debian 4.3.3-13) 4.3.3, 32-bit EXPLAIN ANALYZE select * from (select 1 as i, r from foo() r union all select 2, r from foo() r) as x where i = 3; QUERY PLAN -----------------------------------------------------------------------------------------------------------------------Result (cost=0.00..0.53 rows=2 width=36) (actual time=10007.352..10007.352 rows=0 loops=1) -> Append (cost=0.00..0.53 rows=2width=36) (actual time=10007.351..10007.351 rows=0 loops=1) -> Function Scan on foo r (cost=0.00..0.26 rows=1width=36) (actual time=5003.342..5003.342 rows=0 loops=1) Filter: (1 = 3) -> Function Scan onfoo r (cost=0.00..0.26 rows=1 width=36) (actual time=5004.004..5004.004 rows=0 loops=1) Filter: (2 = 3)Totalruntime: 10007.464 ms BTW, if i move function from FROM to SELECT - 8.4 correctly optimize it like 8.3: EXPLAIN ANALYZE select * from (select 1 as i, foo() as r union all select 2, foo()) as x where i = 3; QUERY PLAN ------------------------------------------------------------------------------------------------Result (cost=0.00..0.54rows=2 width=8) (actual time=0.005..0.005 rows=0 loops=1) -> Append (cost=0.00..0.54 rows=2 width=8) (actualtime=0.003..0.003 rows=0 loops=1) -> Result (cost=0.00..0.26 rows=1 width=0) (actual time=0.001..0.001 rows=0loops=1) One-Time Filter: false -> Result (cost=0.00..0.26 rows=1 width=0) (actual time=0.000..0.000rows=0 loops=1) One-Time Filter: falseTotal runtime: 0.048 ms Is this expected behavior ? Can 8.4 optimize first query like 8.3 ? Thinks ! ps: no response in pgsql-performance so i try ask in pgsql-hackers -- Sergey Burladyan
В списке pgsql-hackers по дате отправления:
Следующее
От: Sergey BurladyanДата:
Сообщение: Re: 8.4, One-Time Filter and subquery ( ... FROM function() union all ... )