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.54 rows=2 width=8) (actual time=0.006..0.006 rows=0
loops=1)
-> Append (cost=0.00..0.54 rows=2 width=8) (actual time=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=0 loops=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)
Total runtime: 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=2 width=36) (actual
time=10007.351..10007.351 rows=0 loops=1)
-> Function Scan on foo r (cost=0.00..0.26 rows=1 width=36) (actual
time=5003.342..5003.342 rows=0 loops=1)
Filter: (1 = 3)
-> Function Scan on foo r (cost=0.00..0.26 rows=1 width=36) (actual
time=5004.004..5004.004 rows=0 loops=1)
Filter: (2 = 3)
Total runtime: 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.54 rows=2 width=8) (actual time=0.005..0.005 rows=0
loops=1)
-> Append (cost=0.00..0.54 rows=2 width=8) (actual time=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=0 loops=1)
One-Time Filter: false
-> Result (cost=0.00..0.26 rows=1 width=0) (actual time=0.000..0.000
rows=0 loops=1)
One-Time Filter: false
Total 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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
