Postgres Pro
Downloads
Home   >   mailing lists

Thread: Initplan placed at the righttree or the lefttree of joinnode

Initplan placed at the righttree or the lefttree of joinnode

From
"Hou, Zhijie"
Date:
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)

Best regards

Re: Initplan placed at the righttree or the lefttree of joinnode

From
Laurenz Albe
Date:
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

Re: Initplan placed at the righttree or the lefttree of joinnode

From
Tom Lane
Date:
"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