lightdb fdw性能测试
首先造测试表和数据，
[zjh@hs-10-20-30-193 ~]$ ltsql -p23456 postgres ltsql (13.8-22.3) Type "help" for help. zjh@postgres=# CREATE USER fdw_user WITH ENCRYPTED PASSWORD 'secret'; CREATE ROLE zjh@postgres=# create table employee (id int, first_name varchar(20), last_name varchar(20)); CREATE TABLE zjh@postgres=# insert into employee values (1,'jobin','augustine'),(2,'avinash','vallarapu'),(3,'fernando','camargos'); INSERT 0 3 zjh@postgres=# GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE employee TO fdw_user; GRANT zjh@postgres=# create extension postgres_fdw; CREATE EXTENSION zjh@postgres=# CREATE SERVER hr zjh@postgres-# FOREIGN DATA WRAPPER postgres_fdw zjh@postgres-# OPTIONS (dbname 'postgres', host '127.0.0.1', port '23456'); CREATE SERVER zjh@postgres=# CREATE USER MAPPING for zjh SERVER hr OPTIONS (user 'fdw_user', password 'secret'); CREATE USER MAPPING zjh@postgres=# CREATE FOREIGN TABLE employee_fdw (id int, first_name character varying(20), last_name character varying(20)) SERVER hr OPTIONS (schema_name 'public', table_name 'employee'); CREATE FOREIGN TABLE
测试直接访问：
zjh@postgres=# select * from employee; id | first_name | last_name ----+------------+----------- 1 | jobin | augustine 2 | avinash | vallarapu 3 | fernando | camargos (3 rows) Time: 0.222 ms zjh@postgres=# select * from employee; id | first_name | last_name ----+------------+----------- 1 | jobin | augustine 2 | avinash | vallarapu 3 | fernando | camargos (3 rows) Time: 0.213 ms zjh@postgres=# select * from employee; id | first_name | last_name ----+------------+----------- 1 | jobin | augustine 2 | avinash | vallarapu 3 | fernando | camargos (3 rows) Time: 0.251 ms
测试FDW，
zjh@postgres=# select * from employee_fdw; id | first_name | last_name ----+------------+----------- 1 | jobin | augustine 2 | avinash | vallarapu 3 | fernando | camargos (3 rows) Time: 0.541 ms zjh@postgres=# select * from employee_fdw; id | first_name | last_name ----+------------+----------- 1 | jobin | augustine 2 | avinash | vallarapu 3 | fernando | camargos (3 rows) Time: 0.502 ms zjh@postgres=# select * from employee_fdw; id | first_name | last_name ----+------------+----------- 1 | jobin | augustine 2 | avinash | vallarapu 3 | fernando | camargos (3 rows) Time: 0.542 ms
可以发现，小数量差了一半多，都快0.3ms了。
造更多数据测试，
zjh@postgres=# insert into employee select id, 'firstname','lastname' from generate_series(4,100000) id; INSERT 0 99997
zjh@postgres=# select count(1) from employee; count -------- 100000 (1 row) Time: 5.340 ms zjh@postgres=# select count(1) from employee; count -------- 100000 (1 row) Time: 5.463 ms zjh@postgres=# select count(1) from employee_fdw; count -------- 100000 (1 row) Time: 5.714 ms zjh@postgres=# select count(1) from employee_fdw; count -------- 100000 (1 row) Time: 5.887 ms zjh@postgres=# explain select count(1) from employee; QUERY PLAN ----------------------------------------------------------------- Aggregate (cost=16.88..16.89 rows=1 width=8) -> Seq Scan on employee (cost=0.00..15.50 rows=550 width=0) (2 rows) Time: 0.319 ms zjh@postgres=# explain select count(1) from employee_fdw; QUERY PLAN ---------------------------------------------------- Foreign Scan (cost=108.53..152.69 rows=1 width=8) Relations: Aggregate on (employee_fdw) (2 rows) Time: 0.258 ms ------------可知，聚合发生在远程，如果执行超过1ms，性能差异就没有那么明显了
再增加数据量，
zjh@postgres=# insert into employee select id, 'firstname','lastname' from generate_series(100001,1000000) id; INSERT 0 900000 Time: 520.054 ms
zjh@postgres=# explain analyze select count(1) from employee a, employee b where a.id=b.id; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=10832577.83..10832577.84 rows=1 width=8) (actual time=714.154..714.155 rows=1 loops=1) -> Merge Join (cost=89752.00..9298263.80 rows=613725612 width=0) (actual time=317.340..680.131 rows=1000000 loops=1) Merge Cond: (a.id = b.id) -> Sort (cost=44876.00..45751.87 rows=350350 width=4) (actual time=160.142..224.013 rows=1000000 loops=1) Sort Key: a.id Sort Method: external merge Disk: 13792kB -> Seq Scan on employee a (cost=0.00..9873.50 rows=350350 width=4) (actual time=0.008..66.685 rows=1000000 loops=1) -> Materialize (cost=44876.00..46627.75 rows=350350 width=4) (actual time=157.192..294.172 rows=1000000 loops=1) -> Sort (cost=44876.00..45751.87 rows=350350 width=4) (actual time=157.189..223.590 rows=1000000 loops=1) Sort Key: b.id Sort Method: external merge Disk: 13792kB -> Seq Scan on employee b (cost=0.00..9873.50 rows=350350 width=4) (actual time=0.008..62.933 rows=1000000 loops=1) Planning Time: 0.069 ms Execution Time: 718.328 ms (14 rows) Time: 718.736 ms zjh@postgres=# explain analyze select count(1) from employee_fdw a, employee_fdw b where a.id=b.id; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=1495.53..1495.54 rows=1 width=8) (actual time=1655.846..1655.848 rows=1 loops=1) -> Merge Join (cost=732.29..1388.59 rows=42778 width=0) (actual time=1344.306..1620.665 rows=1000000 loops=1) Merge Cond: (a.id = b.id) -> Sort (cost=366.15..373.46 rows=2925 width=4) (actual time=635.747..700.207 rows=1000000 loops=1) Sort Key: a.id Sort Method: external merge Disk: 13792kB -> Foreign Scan on employee_fdw a (cost=100.00..197.75 rows=2925 width=4) (actual time=0.194..539.362 rows=1000000 loops=1) -> Sort (cost=366.15..373.46 rows=2925 width=4) (actual time=708.552..765.006 rows=1000000 loops=1) Sort Key: b.id Sort Method: external sort Disk: 17664kB -> Foreign Scan on employee_fdw b (cost=100.00..197.75 rows=2925 width=4) (actual time=0.178..538.436 rows=1000000 loops=1) Planning Time: 0.080 ms Execution Time: 1660.944 ms (13 rows) Time: 1661.433 ms (00:01.661) -------------------------查询没有被pushdown，性能相差就很大了，翻倍多，多了一次网络传输
增加条件就可以做pushdown，如下：
zjh@postgres=# explain (verbose,analyze) select count(1) from employee_fdw a join employee_fdw b on a.id=b.id and b.last_name='lastname' and a.last_name='last_name'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----- Foreign Scan (cost=100.00..194.84 rows=1 width=8) (actual time=54.915..54.916 rows=1 loops=1) Output: (count(1)) Relations: Aggregate on ((public.employee_fdw a) INNER JOIN (public.employee_fdw b)) Remote SQL: SELECT count(1) FROM (public.employee r1 INNER JOIN public.employee r2 ON (((r1.id = r2.id)) AND ((r2.last_name = 'lastname'::text)) AND ((r1.last_name = 'last_name'::text )))) Planning Time: 0.099 ms Execution Time: 55.160 ms (6 rows) Time: 55.634 ms zjh@postgres=# select count(1) from employee_fdw a join employee_fdw b on a.id=b.id and b.last_name='lastname' and a.last_name='lastname'; count -------- 999997 (1 row) Time: 452.738 ms
分页查询10,20,30行，
zjh@postgres=# select version() from employee where id =10; version ----------------------------------------------------------------------------------------------------------- LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit (1 row) Time: 48.984 ms zjh@postgres=# select version() from employee_fdw where id =10; version ----------------------------------------------------------------------------------------------------------- LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit (1 row) Time: 45.413 ms zjh@postgres=# select version() from employee where id>10 and id<20; version ----------------------------------------------------------------------------------------------------------- LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit (9 rows) Time: 53.922 ms zjh@postgres=# select version() from employee_fdw where id>10 and id<20; version ----------------------------------------------------------------------------------------------------------- LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit (9 rows) Time: 53.075 ms zjh@postgres=# select version() from employee where id>10 and id<30; version ----------------------------------------------------------------------------------------------------------- LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit (19 rows) Time: 56.326 ms zjh@postgres=# select version() from employee_fdw where id>10 and id<30; version ----------------------------------------------------------------------------------------------------------- LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit (19 rows) Time: 53.293 ms
创建索引测试，
zjh@postgres=# create index idx_emp on employee (id); CREATE INDEX Time: 307.286 ms zjh@postgres=# select version() from employee_fdw where id>10 and id<20; version ----------------------------------------------------------------------------------------------------------- LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit (9 rows) Time: 0.826 ms zjh@postgres=# select version() from employee where id>10 and id<20; version ----------------------------------------------------------------------------------------------------------- LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit (9 rows) Time: 0.319 ms zjh@postgres=# explain verbose select version() from employee_fdw where id>10 and id<20; QUERY PLAN ---------------------------------------------------------------------------------- Foreign Scan on public.employee_fdw (cost=100.00..161.58 rows=17 width=32) Output: version() Remote SQL: SELECT NULL FROM public.employee WHERE ((id > 10)) AND ((id < 20)) (3 rows) Time: 0.364 ms
-- 本质上还是序列化、反序列化问题
最后测试拷贝，
zjh@postgres=# copy (select * from employee limit 100000) to '/home/zjh/employ.dat'; COPY 100000 Time: 27.303 ms zjh@postgres=# copy (select * from employee_fdw limit 100000) to '/home/zjh/employ.dat'; COPY 100000 Time: 87.527 ms ----- 没有任何优化，完全走的物理拷贝模式，效果还是比较差的---
通过和pgpool ii在lightdb下的性能测试对比，可知，如果只是作为dblink的替代，fdw虽然约束不少，但是注意pushdown还是可以的，但是如果作为sharding或代理中间件，pgpool-ii综合表现会更加。
https://www.percona.com/blog/parallel-commits-for-transactions-using-postgres_fdw-on-postgresql-15/
https://www.percona.com/blog/2018/08/21/foreign-data-wrappers-postgresql-postgres_fdw/
https://www.percona.com/blog/2021/06/02/postgres_fdw-enhancement-in-postgresql-14
