Hi Alexander, On Thu, May 20, 2021 at 11:13 PM Alexander Pyhalov <a.pyha...@postgrespro.ru> wrote: > > Hi. > > The attached patch allows pushing joins with function RTEs to PostgreSQL > data sources. > This makes executing queries like this > > create foreign table f_pgbench_accounts (aid int, bid int, abalance int, > filler char(84)) SERVER local_srv OPTIONS (table_name > 'pgbench_accounts'); > select * from f_pgbench_accounts join unnest(array[1,2,3]) ON unnest = > aid; >
It will be good to provide some practical examples where this is useful. > more efficient. > > with patch: > > > So far I don't know how to visualize actual function expression used in > function RTE, as in postgresExplainForeignScan() es->rtable comes from > queryDesc->plannedstmt->rtable, and rte->functions is already 0. The actual function expression will be part of the Remote SQL of ForeignScan node so no need to visualize it separately. The patch will have problems when there are multiple foreign tables all on different servers or use different FDWs. In such a case the function scan's RelOptInfo will get the fpinfo based on the first foreign table the function scan is paired with during join planning. But that may not be the best foreign table to join. We should be able to plan all the possible joins. Current infra to add one fpinfo per RelOptInfo won't help there. We need something better. The patch targets only postgres FDW, how do you see this working with other FDWs? If we come up with the right approach we could use it for 1. pushing down queries with IN () clause 2. joining a small local table with a large foreign table by sending the local table rows down to the foreign server. -- Best Wishes, Ashutosh Bapat
