Re: [HACKERS] parallelize queries containing initplans

On Tue, Nov 14, 2017 at 10:37 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> The problem would happen if the plan for InitPlan $1 in the above
> example itself referenced a parameter from an upper query level, and
> the value of that parameter changed, and then this section of the plan
> tree was rescanned. I'm not sure I can write a query like that
> off-hand, but I think it's possible to do so.

OK, here's an example:

regression=# explain select * from tenk1 a where unique1 = (select
unique1 from tenk1 b where (select string4 from tenk1 c where c.ten =
a.ten order by unique1 limit 1) < b.string4 limit 1);
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Seq Scan on tenk1 a (cost=0.00..22051.31 rows=50 width=244)
Filter: (unique1 = (SubPlan 2))
SubPlan 2
-> Limit (cost=2.01..2.16 rows=1 width=4)
InitPlan 1 (returns $1)
-> Limit (cost=0.29..2.01 rows=1 width=68)
-> Index Scan using tenk1_unique1 on tenk1 c
(cost=0.29..1727.20 rows=1000 width=68)
Filter: (ten = a.ten)
-> Seq Scan on tenk1 b (cost=0.00..483.00 rows=3333 width=4)
Filter: ($1 < string4)
(10 rows)

InitPlan 1 has got to be re-evaluated for every row in the "Seq Scan
on tenk1 a", and each iteration could return a different value for $1,
and some of those values might be wider than others -- well, not
really, because in this example string4 is actually declared as type
"name". But if you imagine it as type "text" then you can see the
problem.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company