Re: 8.4, One-Time Filter and subquery ( ... FROM function() union all ... )

Sergey Burladyan <eshkinkot(at)gmail(dot)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