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

Sergey Burladyan <eshkinkot@gmail.com> writes:

Thinks !

Th_a_nks ! :)

--
Sergey Burladyan

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.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

Sergey Burladyan <eshkinkot@gmail.com> writes:

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

As of CVS HEAD you get

QUERY PLAN
------------------------------------------------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.011..0.011 rows=0 loops=1)
One-Time Filter: false
Total runtime: 0.179 ms
(3 rows)

regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

As of CVS HEAD you get

QUERY PLAN
------------------------------------------------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.011..0.011 rows=0 loops=1)
One-Time Filter: false
Total runtime: 0.179 ms
(3 rows)

Thank you, Tom !

--
Sergey Burladyan

I found Tom's response ambiguous - but positive in either way, so it
gave me a smile. :-)

Which of the following two great things occurred?
1) Tom popped a quick fix on CVS HEAD? (Pretty fast!)
2) Tom or somebody else had already done it?

Cheers,
mark

On 07/07/2009 05:14 PM, Sergey Burladyan wrote:

Tom Lane<tgl@sss.pgh.pa.us> writes

As of CVS HEAD you get

QUERY PLAN
------------------------------------------------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.011..0.011 rows=0 loops=1)
One-Time Filter: false
Total runtime: 0.179 ms
(3 rows)

Thank you, Tom !

--
Mark Mielke<mark@mielke.cc>

Mark Mielke <mark@mark.mielke.cc> writes:

Which of the following two great things occurred?
1) Tom popped a quick fix on CVS HEAD? (Pretty fast!)
2) Tom or somebody else had already done it?

http://archives.postgresql.org/pgsql-committers/2009-07/msg00067.php

regards, tom lane

Oh, now problem with simple query:

8.4.0 from Debian
explain analyze select i from t where i >= 10 and i = 1;
QUERY PLAN
------------------------------------------------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=1)
One-Time Filter: false
Total runtime: 0.030 ms

CVS HEAD
explain analyze select i from t where i >= 10 and i = 1;
QUERY PLAN
---------------------------------------------------------------------------------------------------
Seq Scan on t (cost=0.00..17030.00 rows=1 width=4) (actual time=449.651..449.651 rows=0 loops=1)
Filter: ((i >= 10) AND (i = 1))
Total runtime: 449.726 ms

--
Sergey Burladyan

Sergey Burladyan <eshkinkot@gmail.com> writes:

Oh, now problem with simple query:

8.4.0 from Debian
explain analyze select i from t where i >= 10 and i = 1;
QUERY PLAN
------------------------------------------------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=1)
One-Time Filter: false
Total runtime: 0.030 ms

CVS HEAD
explain analyze select i from t where i >= 10 and i = 1;
QUERY PLAN
---------------------------------------------------------------------------------------------------
Seq Scan on t (cost=0.00..17030.00 rows=1 width=4) (actual time=449.651..449.651 rows=0 loops=1)
Filter: ((i >= 10) AND (i = 1))
Total runtime: 449.726 ms

Hmm, that's got nothing to do with my recent patch, because there's no
appendrel anywhere. Are you sure you are using the same
constraint_exclusion setting in both cases?

regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

Sergey Burladyan <eshkinkot@gmail.com> writes:

Oh, now problem with simple query:

8.4.0 from Debian
explain analyze select i from t where i >= 10 and i = 1;
QUERY PLAN
------------------------------------------------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=1)
One-Time Filter: false
Total runtime: 0.030 ms

CVS HEAD
explain analyze select i from t where i >= 10 and i = 1;
QUERY PLAN
---------------------------------------------------------------------------------------------------
Seq Scan on t (cost=0.00..17030.00 rows=1 width=4) (actual time=449.651..449.651 rows=0 loops=1)
Filter: ((i >= 10) AND (i = 1))
Total runtime: 449.726 ms

Hmm, that's got nothing to do with my recent patch, because there's no
appendrel anywhere. Are you sure you are using the same
constraint_exclusion setting in both cases?

Oops, of course you are right, i have constraint_exclusion = on in Debian, but
constraint_exclusion = partition by default in CVS HEAD %)

Thanks for help, Tom !

--
Sergey Burladyan