From: Alexander Pyhalov <a.pyhalov@postgrespro.ru>
To: Richard Guo <guofenglinux@gmail.com>
Cc: Etsuro Fujita <etsuro.fujita@gmail.com>
Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Subject: Re: foreign join error "variable not found in subplan target list"
Date: Wed, 10 Aug 2022 18:16:59 +0300
Message-ID: <bff1dfdd718d994830502c5ea0227136@postgrespro.ru> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: a.pyhalov@postgrespro.ru, guofenglinux@gmail.com, etsuro.fujita@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: foreign join error "variable not found in subplan target list"
In-Reply-To: <bff1dfdd718d994830502c5ea0227136@postgrespro.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox