readme.txt

text/plain

Download

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 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
(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
--
\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;
--