On Tue, Nov 14, 2017 at 10:37 AM, Robert Haas <robertmhaas@gmail.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
tenk1a (cost=0.00..22051.31 rows=50 width=244) Filter: (unique1 = (SubPlan 2)) SubPlan 2 -> Limit
(cost=2.01..2.16rows=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.00rows=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
