8.4, One-Time Filter and subquery ( ... FROM function() union all ... ) - Mailing list 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