Re: 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