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;
more efficient.
with patch:
# explain analyze select * from f_pgbench_accounts join
unnest(array[1,2,3,4,5,6]) ON unnest = aid;
QUERY PLAN
------------------------------------------------------------------------------------------------
Foreign Scan (cost=100.00..116.95 rows=7 width=356) (actual
time=2.282..2.287 rows=6 loops=1)
Relations: (f_pgbench_accounts) INNER JOIN (FUNCTION RTE unnest)
Planning Time: 0.487 ms
Execution Time: 3.336 ms
without patch:
# explain analyze select * from f_pgbench_accounts join
unnest(array[1,2,3,4,5,6]) ON unnest = aid;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=100.14..158.76 rows=7 width=356) (actual
time=2.263..1268.607 rows=6 loops=1)
Hash Cond: (f_pgbench_accounts.aid = unnest.unnest)
-> Foreign Scan on f_pgbench_accounts (cost=100.00..157.74 rows=217
width=352) (actual time=2.190..1205.938 rows=100000 loops=1)
-> Hash (cost=0.06..0.06 rows=6 width=4) (actual time=0.041..0.043
rows=6 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Function Scan on unnest (cost=0.00..0.06 rows=6 width=4)
(actual time=0.025..0.028 rows=6 loops=1)
Planning Time: 0.389 ms
Execution Time: 1269.627 ms
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.
--
Best regards,
Alexander Pyhalov,
Postgres Professional