Re: 8.4, One-Time Filter and subquery ( ... FROM function() union all ... )
От | Tom Lane |
---|---|
Тема | Re: 8.4, One-Time Filter and subquery ( ... FROM function() union all ... ) |
Дата | |
Msg-id | 15303.1246897234@sss.pgh.pa.us обсуждение исходный текст |
Ответ на | 8.4, One-Time Filter and subquery ( ... FROM function() union all ... ) (Sergey Burladyan <eshkinkot@gmail.com>) |
Список | pgsql-hackers |
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
В списке pgsql-hackers по дате отправления: