Обсуждение: 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.54rows=2 width=8) (actual time=0.006..0.006 rows=0 loops=1) -> Append (cost=0.00..0.54 rows=2 width=8)
(actualtime=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=0loops=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)Totalruntime: 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=2width=36) (actual time=10007.351..10007.351 rows=0 loops=1) -> Function Scan on foo r (cost=0.00..0.26
rows=1width=36) (actual time=5003.342..5003.342 rows=0 loops=1) Filter: (1 = 3) -> Function Scan
onfoo r (cost=0.00..0.26 rows=1 width=36) (actual time=5004.004..5004.004 rows=0 loops=1) Filter: (2 =
3)Totalruntime: 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.54rows=2 width=8) (actual time=0.005..0.005 rows=0 loops=1) -> Append (cost=0.00..0.54 rows=2 width=8)
(actualtime=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=0loops=1) One-Time Filter: false -> Result (cost=0.00..0.26 rows=1 width=0) (actual
time=0.000..0.000rows=0 loops=1) One-Time Filter: falseTotal 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
Re: 8.4, One-Time Filter and subquery ( ... FROM function() union all ... )
От
Sergey Burladyan
Дата:
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.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
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: falseTotal runtime: 0.179 ms
(3 rows)
regards, tom lane
Re: 8.4, One-Time Filter and subquery ( ... FROM function() union all ... )
От
Sergey Burladyan
Дата:
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. :-)<br /><br /> Which of the
followingtwo great things occurred?<br /> 1) Tom popped a quick fix on CVS HEAD? (Pretty fast!)<br /> 2) Tom or
somebodyelse had already done it?<br /><br /> Cheers,<br /> mark<br /><br /> On 07/07/2009 05:14 PM, Sergey Burladyan
wrote:<blockquote cite="mid:87prcckxfa.fsf@seb.progtech.ru" type="cite"><pre wrap="">Tom Lane <a
class="moz-txt-link-rfc2396E"href="mailto:tgl@sss.pgh.pa.us"><tgl@sss.pgh.pa.us></a> writes</pre><blockquote
type="cite"><prewrap="">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: falseTotal runtime: 0.179 ms
(3 rows) </pre></blockquote><pre wrap="">
Thank you, Tom !
</pre></blockquote><br /><br /><pre class="moz-signature" cols="72">--
Mark Mielke <a class="moz-txt-link-rfc2396E" href="mailto:mark@mielke.cc"><mark@mielke.cc></a>
</pre>
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
Re: 8.4, One-Time Filter and subquery ( ... FROM function() union all ... )
От
Sergey Burladyan
Дата:
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: falseTotal 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.00rows=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
Re: 8.4, One-Time Filter and subquery ( ... FROM function() union all ... )
От
Sergey Burladyan
Дата:
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