Initplan placed at the righttree or the lefttree of joinnode

On Thu, 2020-10-22 at 09:43 +0000, Hou, Zhijie wrote:

Hi,

I try to make some special query plan like the following.
I have tried a lot SQL, but failed.
Can Initplan placed at the righttree or the lefttree of joinnode?

(Fake) QUERY PLAN
-------------------------------------------------------------------
Hash Join (cost=13.15..26.48 rows=140 width=8)
Hash Cond: (test1.a = test.a)
-> Seq Scan on test1 (cost=0.00..11.40 rows=140 width=4)
-> Hash (cost=11.40..11.40 rows=140 width=8)
-> InitPlan 1 (returns $0,$1)
Or

(Fake) QUERY PLAN
-------------------------------------------------------------------
Hash Join (cost=13.15..26.48 rows=140 width=8)
Hash Cond: (test1.a = test.a)
-> Seq Scan on test1 (cost=0.00..11.40 rows=140 width=4)
-> InitPlan 1 (returns $0,$1)
-> Hash (cost=11.40..11.40 rows=140 width=8)

Do you mean something like this (with the pgbench table):

EXPLAIN SELECT * FROM pgbench_accounts a JOIN pgbench_accounts b USING (aid) WHERE a.bid = (SELECT 42);
QUERY PLAN
----------------------------------------------------------------------------------------
Hash Join (cost=31707.01..93539.02 rows=100000 width=190)
Hash Cond: (b.aid = a.aid)
InitPlan 1 (returns $0)
-> Result (cost=0.00..0.01 rows=1 width=4)
-> Seq Scan on pgbench_accounts b (cost=0.00..26394.00 rows=1000000 width=97)
-> Hash (cost=28894.00..28894.00 rows=100000 width=97)
-> Seq Scan on pgbench_accounts a (cost=0.00..28894.00 rows=100000 width=97)
Filter: (bid = $0)
(8 rows)

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

"Hou, Zhijie" <houzj.fnst@cn.fujitsu.com> writes:

Can Initplan placed at the righttree or the lefttree of joinnode?

Initplans generally get attached to the topmost node of a given
(sub)query, cf create_plan(). You might be able to get what you want
by having an unflattenable subquery as one input of a join. I do not
think you're going to get any exciting results that way though ...
as create_plan() notes, the exact spot where an initplan is attached
to the tree isn't terribly relevant.

regards, tom lane