Hi everyone. I develop postgresql's extension such as fdw in my work. I'm interested in using postgresql for OLAP. After [1] having been withdrawn, I reviewed [1]. I think that this patch is realy useful when using OLAP queries. Furthermore, I think it would be more useful if this patch works on a foreign table. So, I would like to ask you a question on this patch in this new thread.
I changed this patch a little and confirmed that my idea is true. The followings are things I found and differences of between my prototype and this patch. 1. Things I found I execute a query which contain join of postgres_fdw's foreign table and a table and aggregation of the join result. In my setting, my prototype reduce this query's response by 93%. 2. Differences between my prototype and this patch (1) Pushdown aggregation of foeign table if FDW pushdown partial aggregation (2) postgres_fdw pushdowns some partial aggregations I attached my prototype source code and content of my experiment. I want to resume development of this patch if there is some possibility of accept of this patch's function. I took a contact to Mr.Houska on resuming development of this patch. As a result, Mr.Houska advised for me that I ask in pgsql-hackers whether any reviewers / committers are interested to work on the patch. Is anyone interested in my work? Sincerely yours. Yuuki Fujii [1] https://commitfest.postgresql.org/32/ -- Yuuki Fujii Information Technology R&D Center Mitsubishi Electric Corporation
v17-0004-pushdown-aggregation-foreign-table.patch
Description: v17-0004-pushdown-aggregation-foreign-table.patch
1. Restrictions of my prototype
(1) aggregate functions are avg, sum, count, min, max
(2) argment or sum of avg is bigint var
2. My experiment settings
(1) hardware settings
cpu:Intel Corei5-9500 processor(3.0 GHz/9MB cache)(6 cores)
ram:DDR4 3200Mhz 128GB
storage:512GB SSD(M.2 NVMe)
(2) software
os: CentOS7
postgresql source code:14(in development) with commit-id
947456a823d6b0973b68c6b38c8623a0504054e7
# build by "make world"
patchs in [1] I applied:
v17-0001-Introduce-RelInfoList-structure.patch
v17-0002-Aggregate-push-down-basic-functionality.patch
v17-0003-Use-also-partial-paths-as-the-input-for-grouped-path.patch
[1] https://commitfest.postgresql.org/32/
(3) PostgreSQL settings
max_parallel_workers_per_gather=6
work_mem=1000000
(4) tables and data
execute the following commands using postgresql's client tool such as psql
--
create database tmp;
\c tmp;
create table log as select 1::bigint as id, 1::bigint as size, 1::bigint as
total from generate_series(1,1000000000) t;
create table master as select t as id, 'hoge' || mod(t, 1000) as name from
generate_series(1,1000000) t;
create index master_idx on master(id);
create extension postgres_fdw;
create server local_server foreign data wrapper postgres_fdw OPTIONS (host
'localhost', port '5432', dbname 'tmp');
create user mapping for user server local_server options (user 'postgres',
password 'postgres');
create foreign table f_log(id bigint, size bigint, total bigint) server
local_server options (table_name 'log');
analyze;
set enable_agg_pushdown=true;
explain (verbose, analyze) select b.name, avg(a.total) from f_log a join master
b on a.id = b.id group by b.name;
set enable_agg_pushdown=false;
explain (verbose, analyze) select b.name, avg(a.total) from f_log a join master
b on a.id = b.id group by b.name;
--
