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

Sergey Burladyan <eshkinkot@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.00rows=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