又起飞了！让PostgreSQL性能足足提升1000多倍的扩展！

Whoami：5年+金融、政府、医疗领域工作经验的DBA
Certificate：PGCM、OCP、YCP
Skill：Oracle、Mysql、PostgreSQL、国产数据库
Platform：CSDN、墨天轮、公众号（呆呆的私房菜）

# 演示环境操作系统：ubuntu 24.04
# 服务器配置：4C 8G
root@chen:~# uname -a
Linux chen 6.8.0-49-generic #49-Ubuntu SMP PREEMPT_DYNAMIC Mon Nov  4 02:06:24 UTC 2024 x86_64 x86_64 x86_64 GNU/Linux
root@chen:~# cat proc/cpuinfo | grep process | wc -l
4
root@chen:~# cat proc/meminfo | grep Mem
MemTotal:        4009388 kB
MemFree:         3386428 kB
MemAvailable:    3497688 kB


1. 配置在线源
sudo vi etc/apt/sources.list 
# 默认注释了源码镜像以提高 apt update 速度，如有需要可自行取消注释
deb https://mirrors.tuna.tsinghua.edu.cn/ubuntu/ noble main restricted universe multiverse
deb https://mirrors.tuna.tsinghua.edu.cn/ubuntu/ noble-updates main restricted universe multiverse
deb https://mirrors.tuna.tsinghua.edu.cn/ubuntu/ noble-backports main restricted universe multiverse
apt-get update


2. 安装postgresql 15
apt install -y postgresql-common gnupg2
/usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
apt install postgresql-15


3. 安装pgxman
curl -sfL https://install.pgx.sh | sh -


4. 安装pg_duckdb扩展
pgxman install pg_duckdb


5. 启动数据库，并加载pg_duckdb插件
systemctl start postgresql
psql -c "alter system set shared_preload_libraries ='pg_duckdb';"
systemctl restart postgresql
psql -c "create extension pg_duckdb;"

1. 安装duckdb
wget https://github.com/duckdb/duckdb/releases/download/v1.1.3/duckdb_cli-linux-amd64.zip
unzip duckdb_cli-linux-amd64.zip
./duckdb


2. 安装tpcds扩展
install tpcds;
load tpcds;
-- 生成规模因子为1的测试数据，即1GB左右数据
call dsdgen(sf=1);
-- 导出数据为csv格式，并且用“|”分隔
export database 'public' (format csv, delimiter '|');


3. 导入元数据到postgresql数据库中
psql -f public/schema.sql


4. 调整load.sql格式，并导入实际数据到表中
sed -i 's/COPY/\\copy/' public/load.sql
psql -f public/load.sql

1. 在postgresql内核中运行
postgres=# set duckdb.enable_external_access = false;
postgres=# \timing on
Timing is on.
postgres=# 
postgres=# WITH customer_total_return AS
  (SELECT sr_customer_sk AS ctr_customer_sk,
          sr_store_sk AS ctr_store_sk,
          sum(sr_return_amt) AS ctr_total_return
   FROM store_returns,
        date_dim
   WHERE sr_returned_date_sk = d_date_sk
     AND d_year = 2000
   GROUP BY sr_customer_sk,
            sr_store_sk)
SELECT c_customer_id
FROM customer_total_return ctr1,
     store,
     customer
WHERE ctr1.ctr_total_return >
    (SELECT avg(ctr_total_return)*1.2
     FROM customer_total_return ctr2
     WHERE ctr1.ctr_store_sk = ctr2.ctr_store_sk)
  AND s_store_sk = ctr1.ctr_store_sk
  AND s_state = 'TN'
  AND ctr1.ctr_customer_sk = c_customer_sk
ORDER BY c_customer_id
LIMIT 100;
Time: 134320.570 ms (02:14.321)


2. 在pg_duckdb内核中执行
postgres=# SET duckdb.force_execution = true;
SET
Time: 0.155 ms
postgres=# WITH customer_total_return AS
  (SELECT sr_customer_sk AS ctr_customer_sk,
          sr_store_sk AS ctr_store_sk,
          sum(sr_return_amt) AS ctr_total_return
   FROM store_returns,
        date_dim
   WHERE sr_returned_date_sk = d_date_sk
     AND d_year = 2000
   GROUP BY sr_customer_sk,
            sr_store_sk)
SELECT c_customer_id
FROM customer_total_return ctr1,
     store,
     customer
WHERE ctr1.ctr_total_return >
    (SELECT avg(ctr_total_return)*1.2
     FROM customer_total_return ctr2
     WHERE ctr1.ctr_store_sk = ctr2.ctr_store_sk)
  AND s_store_sk = ctr1.ctr_store_sk
  AND s_state = 'TN'
  AND ctr1.ctr_customer_sk = c_customer_sk
ORDER BY c_customer_id
LIMIT 100;
Time: 121.312 ms