Function scan FDW pushdown - Mailing list pgsql-hackers

From Alexander Pyhalov
Subject Function scan FDW pushdown
Date
Msg-id dc6a29eb78064f5a3305049d8cd453c5@postgrespro.ru
Whole thread Raw
Responses Re: Function scan FDW pushdown
List pgsql-hackers
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
Attachment

pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: Added missing tab completion for alter subscription set option
Next
From: Robert Haas
Date:
Subject: Re: Race condition in recovery?