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

I am testing some of my queries with 8.4 and find some performance decline.

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 $$;

PostgreSQL 8.3.7 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debian 4.3.3-5) 4.3.3

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.54 rows=2 width=8) (actual time=0.006..0.006 rows=0 loops=1)
-> Append (cost=0.00..0.54 rows=2 width=8) (actual time=0.003..0.003 rows=0 loops=1)
-> Result (cost=0.00..0.26 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=1)
One-Time Filter: false
-> Function Scan on foo r (cost=0.00..0.26 rows=1 width=4) (never executed)
-> Result (cost=0.00..0.26 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=1)
One-Time Filter: false
-> Function Scan on foo r (cost=0.00..0.26 rows=1 width=4) (never executed)
Total runtime: 0.053 ms

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

BTW, if i move function from FROM to SELECT - 8.4 correctly optimize it like 8.3:

EXPLAIN ANALYZE select * from (select 1 as i, foo() as r union all select 2, foo()) as x where i = 3;
QUERY PLAN
------------------------------------------------------------------------------------------------
Result (cost=0.00..0.54 rows=2 width=8) (actual time=0.005..0.005 rows=0 loops=1)
-> Append (cost=0.00..0.54 rows=2 width=8) (actual time=0.003..0.003 rows=0 loops=1)
-> Result (cost=0.00..0.26 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1)
One-Time Filter: false
-> Result (cost=0.00..0.26 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=1)
One-Time Filter: false
Total runtime: 0.048 ms

Is this expected behavior ? Can 8.4 optimize first query like 8.3 ?

Thinks !

ps: no response in pgsql-performance so i try ask in pgsql-hackers

--
Sergey Burladyan

Sergey Burladyan <eshkinkot(at)gmail(dot)com> writes:

> Thinks !

Th_a_nks ! :)

--
Sergey Burladyan

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

Sergey Burladyan <eshkinkot(at)gmail(dot)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(at)sss(dot)pgh(dot)pa(dot)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(at)sss(dot)pgh(dot)pa(dot)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(at)mielke(dot)cc>

Mark Mielke <mark(at)mark(dot)mielke(dot)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(at)gmail(dot)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(at)sss(dot)pgh(dot)pa(dot)us> writes:

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