Function scan FDW pushdown
От | Alexander Pyhalov |
---|---|
Тема | Function scan FDW pushdown |
Дата | |
Msg-id | dc6a29eb78064f5a3305049d8cd453c5@postgrespro.ru обсуждение исходный текст |
Ответы |
Re: Function scan FDW pushdown
|
Список | 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
Вложения
В списке pgsql-hackers по дате отправления: