Repeating Append operation
От | Gurjeet Singh |
---|---|
Тема | Repeating Append operation |
Дата | |
Msg-id | 65937bea1003191109u34ff7039uc7b7c2e7ce051a92@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Repeating Append operation
(Robert Haas <robertmhaas@gmail.com>)
|
Список | pgsql-hackers |
<div dir="ltr">Hi all,<br /><br /><span style="font-family: courier new,monospace;">explain </span><br style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;">select v from (</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">select array(</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;"> select1</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;"> union all</span><br style="font-family: courier new,monospace;" /><span style="font-family: couriernew,monospace;"> select 2) as v</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">from (select 1) ) as s</span><br style="font-family: courier new,monospace;" /><spanstyle="font-family: courier new,monospace;">where v is not null;</span><br style="font-family: courier new,monospace;"/><br style="font-family: courier new,monospace;" />The plan looks like:<br /><br /><span style="font-family:courier new,monospace;"> QUERY PLAN</span><br style="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;">--------------------------------------------------------</span><brstyle="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;"> Result (cost=0.08..0.10 rows=1 width=0)</span><br style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;"> One-Time Filter: ($1 ISNOT NULL)</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;"> InitPlan</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;"> -> Append (cost=0.00..0.04 rows=2 width=0)</span><br style="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;"> -> Result (cost=0.00..0.01 rows=1 width=0)</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;"> -> Result (cost=0.00..0.01 rows=1 width=0)</span><br style="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;"> -> Append (cost=0.00..0.04 rows=2 width=0)</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;"> -> Result (cost=0.00..0.01 rows=1 width=0)</span><br style="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;"> -> Result (cost=0.00..0.01 rows=1 width=0)</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;"> -> Result (cost=0.00..0.01 rows=1 width=0)</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">(10 rows)</span><br style="font-family: courier new,monospace;" /><br /> Itseems that that the UNION ALL part of the query will be executed twice. If I remove the WHERE clause the I see only oneAppend operation. I had a suspicion that its just the display of the plan that showed the same plan twice, but then Inoticed that the overall cost of the query also drops making me think that this UNION ALL will actually be executed twice.<br/><br /> The plan without the WHERE clause is:<br /><span style="font-family: courier new,monospace;"> QUERY PLAN</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">----------------------------------------------------------------------</span><brstyle="font-family: couriernew,monospace;" /><span style="font-family: courier new,monospace;"> Subquery Scan __unnamed_subquery_0 (cost=0.04..0.06rows=1 width=0)</span><br style="font-family: courier new,monospace;" /><span style="font-family: couriernew,monospace;"> InitPlan</span><br style="font-family: courier new,monospace;" /><span style="font-family: couriernew,monospace;"> -> Append (cost=0.00..0.04 rows=2 width=0)</span><br style="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;"> -> Result (cost=0.00..0.01 rows=1 width=0)</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;"> -> Result (cost=0.00..0.01 rows=1 width=0)</span><br style="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;"> -> Result (cost=0.00..0.01 rows=1 width=0)</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">(6 rows)</span><br/><br /> I had seen this with a bigger query on actual tables, and this is just a reproducible test case.In the original query I see 'SubPlan' instead of the 'InitPlan' seen here.<br /><br />Head of plan with WHERE clause:<br/><span style="font-family: courier new,monospace;"> Seq Scan on "Person" p (cost=0.00..280486580881.10 rows=1373415width=4)</span><br /><br />Head of plan without WHERE clause:<br /><span style="font-family: courier new,monospace;"> Seq Scan on "Person" p (cost=0.00..140594841889.03 rows=1380317 width=4)</span><br /><br clear="all" /> Is there a way to avoid this double evaluation?<br /><br />Thanks in advance.<br />-- <br />gurjeet.singh<br />@ EnterpriseDB- The Enterprise Postgres Company<br /><a href="http://www.enterprisedb.com">http://www.enterprisedb.com</a><br/><br />singh.gurjeet@{ gmail | yahoo }.com<br />Twitter/Skype:singh_gurjeet<br /><br />Mail sent from my BlackLaptop device<br /></div>
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Robert TreatДата:
Сообщение: Postgres officially accepted in to 2010 Google Summer of Code program