Add semi-join pushdown to postgres_fdw
От | Alexander Pyhalov |
---|---|
Тема | Add semi-join pushdown to postgres_fdw |
Дата | |
Msg-id | c9e2a757cf3ac2333714eaf83a9cc184@postgrespro.ru обсуждение исходный текст |
Ответы |
Re: Add semi-join pushdown to postgres_fdw
|
Список | pgsql-hackers |
Hi. It's possible to extend deparsing in postgres_fdw, so that we can push down semi-joins, which doesn't refer to inner reltarget. This allows us to push down joins in queries like SELECT * FROM ft1 t1 WHERE t1.c1 < 10 AND t1.c3 IN (SELECT c3 FROM ft2 t2 WHERE date(c5) = '1970-01-17'::date); EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 < 10 AND t1.c3 IN (SELECT c3 FROM ft2 t2 WHERE date(c5) = '1970-01-17'::date); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 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" < 10)) AND (EXISTS (SELECT NULL FROM "S 1"."T 1" r3 WHERE ((date(r3.c5) = '1970-01-17'::date)) AND ((r1.c3 = r3.c3)))) Deparsing semi-joins leads to generating (text) conditions like 'EXISTS (SELECT NULL FROM inner_rel WHERE join_conds) . Such conditions are generated in deparseFromExprForRel() and distributed to nearest WHERE, where they are added to the list of and clauses. -- Best regards, Alexander Pyhalov, Postgres Professional
Вложения
В списке pgsql-hackers по дате отправления: