Re: Support "Right Semi Join" plan shapes
| От | Richard Guo |
|---|---|
| Тема | Re: Support "Right Semi Join" plan shapes |
| Дата | |
| Msg-id | CAMbWs4_3qQezUNDRfPNa7sK_aT4dCu-6CYFPD_Z-oVPnJSiPVA@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Support "Right Semi Join" plan shapes (vignesh C <vignesh21@gmail.com>) |
| Ответы |
Re: Support "Right Semi Join" plan shapes
|
| Список | pgsql-hackers |
On Sun, Jan 7, 2024 at 3:03 PM vignesh C <vignesh21@gmail.com> wrote:
One of the tests in CFBot has failed at [1] with:
- Relations: (public.ft1 t1) SEMI JOIN (public.ft2 t2)
- Remote SQL: SELECT r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6,
r1.c7, r1.c8 FROM "S 1"."T 1" r1 WHERE ((r1."C 1" < 20)) AND EXISTS
(SELECT NULL FROM "S 1"."T 1" r3 WHERE ((r3."C 1" > 10)) AND
((date(r3.c5) = '1970-01-17'::date)) AND ((r3.c3 = r1.c3))) ORDER BY
r1."C 1" ASC NULLS LAST
-(4 rows)
+ Sort Key: t1.c1
+ -> Foreign Scan
+ Output: t1.c1, t1.c2, t1.c3, t1.c4, t1.c5, t1.c6, t1.c7, t1.c8
+ Relations: (public.ft1 t1) SEMI JOIN (public.ft2 t2)
+ Remote SQL: SELECT r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5,
r1.c6, r1.c7, r1.c8 FROM "S 1"."T 1" r1 WHERE ((r1."C 1" < 20)) AND
EXISTS (SELECT NULL FROM "S 1"."T 1" r3 WHERE ((r3."C 1" > 10)) AND
((date(r3.c5) = '1970-01-17'::date)) AND ((r3.c3 = r1.c3)))
+(7 rows)
Thanks. I looked into it and have figured out why the plan differs.
With this patch the SEMI JOIN that is pushed down to the remote server
is now implemented using JOIN_RIGHT_SEMI, whereas previously it was
implemented using JOIN_SEMI. Consequently, this leads to changes in the
costs of the paths: path with the sort pushed down to remote server, and
path with the sort added atop the foreign join. And at last the latter
one wins by a slim margin.
I think we can simply update the expected file to fix this plan diff, as
attached.
Thanks
Richard
With this patch the SEMI JOIN that is pushed down to the remote server
is now implemented using JOIN_RIGHT_SEMI, whereas previously it was
implemented using JOIN_SEMI. Consequently, this leads to changes in the
costs of the paths: path with the sort pushed down to remote server, and
path with the sort added atop the foreign join. And at last the latter
one wins by a slim margin.
I think we can simply update the expected file to fix this plan diff, as
attached.
Thanks
Richard
Вложения
В списке pgsql-hackers по дате отправления: