Sergey Burladyan <eshkin...@gmail.com> writes:
> 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 $$;
> 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;
Hmm. This doesn't actually have anything to do with functions; for
example in 8.3
regression=# explain select * from (select 1 as i, * from tenk1 a union all
select 2, * from tenk1 b) as x where i = 3;
QUERY PLAN
-----------------------------------------------------------------------------
Result (cost=0.00..916.02 rows=2 width=248)
-> Append (cost=0.00..916.02 rows=2 width=248)
-> Result (cost=0.00..458.00 rows=1 width=244)
One-Time Filter: false
-> Seq Scan on tenk1 a (cost=0.00..458.00 rows=1 width=244)
-> Result (cost=0.00..458.00 rows=1 width=244)
One-Time Filter: false
-> Seq Scan on tenk1 b (cost=0.00..458.00 rows=1 width=244)
(8 rows)
but in 8.4
regression=# explain select * from (select 1 as i, * from tenk1 a union all
select 2, * from tenk1 b) as x where i = 3;
QUERY PLAN
------------------------------------------------------------------------
Result (cost=0.00..966.00 rows=100 width=276)
-> Append (cost=0.00..966.00 rows=100 width=276)
-> Seq Scan on tenk1 a (cost=0.00..483.00 rows=50 width=276)
Filter: (1 = 3)
-> Seq Scan on tenk1 b (cost=0.00..483.00 rows=50 width=276)
Filter: (2 = 3)
(6 rows)
The reason for the change is that 8.4 is smart enough to flatten UNION
ALL subqueries that have non-Var select list items. Which means that
when set_append_rel_pathlist pushes the appendrel's "i = 3" restriction
down into the member queries, it's pushing the modified restrictions
into plain relation scans instead of subquery scans. Before,
const-simplification and recognition of the resulting constant-false
quals happened when the whole planner was recursively invoked on the
subquery, but for plain relation scans we assume all that was already
done. So we have a layer of processing that's getting missed out in
examples like these. It was never important before because the old
code couldn't produce a constant qual condition that way (since the
substituted expression would necessarily be a Var).
I'm inclined to think the right fix involves making
set_append_rel_pathlist perform const simplification and check for
pseudoconstant quals after it does adjust_appendrel_attrs(). It
might take a bit of code refactoring to do that conveniently, though.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
