如何加速26.6亿行PostgreSQL表的抽样查询并导出CSV

我尝试在一张约26.6亿行的PostgreSQL表上执行以下查询，目前使用Python的psycopg库，最终需将结果转换为CSV文件：

import csv
from psycopg2 import sql
year = 2013
stmt=sql.SQL(f"""SELECT * FROM skill TABLESAMPLE SYSTEM (1) WHERE skillclusterfamily='Science and Research' AND DATE_PART('year',jobdate)={year}""")
cur.execute(stmt)
res=cur.fetchall()
print(res)

需注意1%抽样涉及约2660万行数据，单次查询耗时约2小时，效率过低。我无NVIDIA GPU可用，最终目标是循环遍历2007-2021年数据，将随机抽样结果合并到一个CSV文件中（2660万行已满足需求）。试过SYSTEM和BERNOULLI抽样均未提速，且无法安装扩展，请问如何加速这类查询？

1. 先过滤再抽样，利用索引缩小数据范围 #

当前查询逻辑是先全表抽样1%，再过滤目标年份和分类，会浪费大量资源处理无关数据。反过来先通过索引筛选出符合条件的行，再对结果抽样，能大幅减少处理量。

首先创建联合索引（针对过滤条件优化）：

CREATE INDEX idx_skill_cluster_year ON skill (skillclusterfamily, DATE_PART('year', jobdate));

修改查询逻辑，先过滤后抽样：

year = 2013
stmt = sql.SQL("""
    SELECT * FROM (
        SELECT * FROM skill 
        WHERE skillclusterfamily='Science and Research' 
          AND DATE_PART('year', jobdate) = %s
    ) AS filtered_data
    TABLESAMPLE SYSTEM (1);
""")
cur.execute(stmt, (year,))

2. 批量处理所有年份，避免多次查询开销 #

不需要逐年循环查询，一次性筛选2007-2021年的目标数据后统一抽样，减少连接和查询的重复开销：

stmt = sql.SQL("""
    SELECT * FROM (
        SELECT * FROM skill 
        WHERE skillclusterfamily='Science and Research' 
          AND DATE_PART('year', jobdate) BETWEEN 2007 AND 2021
    ) AS filtered_all_years
    TABLESAMPLE SYSTEM (1);
""")
cur.execute(stmt)

3. 用PostgreSQL COPY命令直接导出，跳过Python内存中转 #

fetchall()会把所有数据加载到Python内存，既慢又占用资源。用PostgreSQL内置的COPY命令直接导出CSV，效率远高于Python处理：

方案A：直接导出到服务器本地文件（需权限） #

cur.execute("""
    COPY (
        SELECT * FROM (
            SELECT * FROM skill 
            WHERE skillclusterfamily='Science and Research' 
              AND DATE_PART('year', jobdate) BETWEEN 2007 AND 2021
        ) AS filtered_all_years
        TABLESAMPLE SYSTEM (1)
    ) TO '/path/to/output.csv' WITH CSV HEADER;
""")

方案B：通过Python分批次写入本地文件 #

如果无法写入服务器本地，用fetchmany()分批次获取数据，避免内存溢出：

import csv

with open('output.csv', 'w', newline='') as f:
    writer = csv.writer(f)
    # 写入表头
    cur.execute("SELECT column_name FROM information_schema.columns WHERE table_name = 'skill'")
    headers = [row[0] for row in cur.fetchall()]
    writer.writerow(headers)
    
    # 分批次获取并写入数据
    cur.execute("""
        SELECT * FROM (
            SELECT * FROM skill 
            WHERE skillclusterfamily='Science and Research' 
              AND DATE_PART('year', jobdate) BETWEEN 2007 AND 2021
        ) AS filtered_all_years
        TABLESAMPLE SYSTEM (1);
    """)
    while True:
        rows = cur.fetchmany(10000)  # 每次取10000行，可根据内存调整
        if not rows:
            break
        writer.writerows(rows)

4. 用临时表预存过滤结果 #

如果需要多次操作，先把目标数据导入临时表（临时表默认存于内存，查询更快），再对临时表抽样：

CREATE TEMP TABLE filtered_skill AS
SELECT * FROM skill 
WHERE skillclusterfamily='Science and Research' 
  AND DATE_PART('year', jobdate) BETWEEN 2007 AND 2021;

-- 导出临时表抽样结果
COPY (SELECT * FROM filtered_skill TABLESAMPLE SYSTEM (1)) TO '/path/to/output.csv' WITH CSV HEADER;

内容的提问来源于stack exchange，提问作者Cr3