MIME-Version: 1.0
Date: Wed, 10 Aug 2022 18:16:59 +0300
From: Alexander Pyhalov <a.pyhalov@postgrespro.ru>
To: Richard Guo <guofenglinux@gmail.com>
Cc: Etsuro Fujita <etsuro.fujita@gmail.com>, PostgreSQL mailing lists
 <pgsql-bugs@lists.postgresql.org>
Subject: Re: foreign join error "variable not found in subplan target list"
In-Reply-To: 
 <CAMbWs491hb2ecgG--nFiifLMyM7a+XRMK4eDse=xq1jkOkda1A@mail.gmail.com>
References: <cfb17bf6dfdf876467bd5ef533852d18@postgrespro.ru>
 <CAPmGK16mosKq6h=TbQP8wKJu4HFdCBjrxAV+TLYfovuNmfNhHA@mail.gmail.com>
 <CAMbWs4_k4yFmOJ+U-Ap7BvQTbdUYhKmVchV=yJFHdtw_10nQZg@mail.gmail.com>
 <fcc74118ee33ca5e213fa855ba70ff22@postgrespro.ru>
 <CAMbWs4_xn7drbNBDSLKGD7nDLxM3aMOSTXz6CbWugPJ-7JzZbg@mail.gmail.com>
 <CAMbWs49bW4i06Daf8eb1+tsbJVymA4U-3v6rRoNvqKaJSm459Q@mail.gmail.com>
 <80d1b0827ea8643e0163cd06b384b9d4@postgrespro.ru>
 <CAMbWs491hb2ecgG--nFiifLMyM7a+XRMK4eDse=xq1jkOkda1A@mail.gmail.com>
User-Agent: Roundcube Webmail/1.4.11
Message-ID: <bff1dfdd718d994830502c5ea0227136@postgrespro.ru>
Content-Type: text/plain; charset=UTF-8;
 format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/bff1dfdd718d994830502c5ea0227136%40postgrespro.ru>
Precedence: bulk

Richard Guo писал 2022-08-10 11:36:
> On Wed, Aug 10, 2022 at 3:06 PM Alexander Pyhalov
> <a.pyhalov@postgrespro.ru> wrote:
> 
>> Richard Guo писал 2022-08-10 08:28:
>>> On Wed, Aug 10, 2022 at 10:15 AM Richard Guo
>> <guofenglinux@gmail.com>
>>> wrote:
>>> 
>>>> Currently the outer_plan used in postgresGetForeignPlan() can
>> only
>>>> be
>>>> 'Join' or 'Sort + Join'. I'm wondering whether we can take this
>>>> knowledge into consideration when we fix the outer_plan's tlist,
>> to
>>>> also
>>>> fix the Join's tlist if it is below the Sort node.
>>> 
>>> Alternatively, how about we include in the EPQ path's pathtarget
>>> thecolumns required for evaluating the local conditions when we
>>> consider
>>> EPQ paths with pathkeys? Something like attached.
>>> 
>>> Thanks
>>> Richard
>> 
>> Hi.
>> Why are we sure that epq_path can provide all vars from
>> restrictinfo?
> 
> The local conditions come from the joinrel's restrictlist, which
> contains all the clauses that syntactically belong at the join level.
> So
> I think the join path for EPQ checks should be able to provide all the
> exprs needed by local_conds.
> 
> Thanks
> Richard

OK. It looks good to me. The only thing which surprised me that in test 
case we see unnecessary sort in remote query. However, it's explained by 
selected costs and STD_FUZZ_FACTOR, so that sorted path has essentially 
the same cost as non-sorted one according to 
compare_path_costs_fuzzily().

EXPLAIN (verbose, costs off)
UPDATE ft2 SET c3 = 'baz'
   FROM ft4 INNER JOIN ft5 ON (ft4.c1 = ft5.c1)
   WHERE ft2.c1 > 2000 AND ft2.c2 === ft4.c1
   RETURNING ft2.*, ft4.*, ft5.*;
                                                                          
                                                                          
                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Update on public.ft2
    Output: ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6, ft2.c7, 
ft2.c8, ft4.c1, ft4.c2, ft4.c3, ft5.c1, ft5.c2, ft5.c3
    Remote SQL: UPDATE "S 1"."T 1" SET c3 = $2 WHERE ctid = $1 RETURNING 
"C 1", c2, c3, c4, c5, c6, c7, c8
    ->  Hash Join
          Output: 'baz'::text, ft2.ctid, ft2.*, ft4.*, ft5.*, ft4.c1, 
ft4.c2, ft4.c3, ft5.c1, ft5.c2, ft5.c3
          Hash Cond: (ft5.c1 = ft4.c1)
          ->  Foreign Scan on public.ft5
                Output: ft5.*, ft5.c1, ft5.c2, ft5.c3
                Remote SQL: SELECT c1, c2, c3 FROM "S 1"."T 4"
          ->  Hash
                Output: ft2.ctid, ft2.*, ft4.*, ft4.c1, ft4.c2, ft4.c3, 
ft2.c2
                ->  Foreign Scan
                      Output: ft2.ctid, ft2.*, ft4.*, ft4.c1, ft4.c2, 
ft4.c3, ft2.c2
                      Filter: (ft2.c2 === ft4.c1)
                      Relations: (public.ft2) INNER JOIN (public.ft4)
                      Remote SQL: SELECT r1.ctid, CASE WHEN (r1.*)::text 
IS NOT NULL THEN ROW(r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, 
r1.c8) END, CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2.c1, r2.c2, 
r2.c3) END, r2.c1, r2.c2, r2.c3, r1.c2 FROM ("S 1"."T 1" r1 INNER JOIN 
"S 1"."T 3" r2 ON (((r1."C 1" > 2000)))) ORDER BY r2.c1 ASC NULLS LAST 
FOR UPDATE OF r1
                      ->  Sort
                            Output: ft2.ctid, ft2.*, ft4.*, ft4.c1, 
ft4.c2, ft4.c3, ft2.c2
                            Sort Key: ft4.c1
                            ->  Nested Loop
                                  Output: ft2.ctid, ft2.*, ft4.*, ft4.c1, 
ft4.c2, ft4.c3, ft2.c2
                                  Join Filter: (ft2.c2 === ft4.c1)
                                  ->  Foreign Scan on public.ft2
                                        Output: ft2.ctid, ft2.*, ft2.c2
                                        Remote SQL: SELECT "C 1", c2, c3, 
c4, c5, c6, c7, c8, ctid FROM "S 1"."T 1" WHERE (("C 1" > 2000)) FOR 
UPDATE
                                  ->  Foreign Scan on public.ft4
                                        Output: ft4.*, ft4.c1, ft4.c2, 
ft4.c3
                                        Remote SQL: SELECT c1, c2, c3 
FROM "S 1"."T 3"


-- 
Best regards,
Alexander Pyhalov,
Postgres Professional