优化PostgreSQL中指定节点子集的边查询性能

我有一个PostgreSQL数据库，包含存储图中所有节点的表，以及存储节点间所有边的表。我希望指定一个节点子集，检索该子集内节点之间的所有边。
边表包含left_node_id和right_node_id两列，用于标识边连接的节点，因此查询需返回left_node_id和right_node_id均属于指定节点集的边。例如总共有100个节点，我关注其中20个节点间的连接。
我编写了如下查询，它可以正常运行但无法扩展：当边表有460万条边，仅指定10个节点时，查询耗时3分钟（设备为MacBook Pro M1、32GB内存），我需要支持10万+节点的场景，请问有没有更优的实现方式？

原查询代码：

-- edge query
edge_query = """
    select *
    from edges ed
    where ed.left_node_id in ({})
    and ed.right_node_id in ({})
"""

-- execute query in pandas (or your favourite dbase ide)
import pandas.io.sql as sqlio

edges_retrieve = sqlio.read_sql(edge_query.format(<text_nodes_list>),<connection>)

1. 创建针对性复合索引 #

针对查询的过滤条件，给边表创建包含left_node_id和right_node_id的复合索引，让PostgreSQL能快速定位符合条件的边：

CREATE INDEX idx_edges_both_nodes ON edges (left_node_id, right_node_id);

如果业务中存在反向查询（比如以right_node_id为优先过滤条件），可以额外创建一个反向复合索引：

CREATE INDEX idx_edges_both_nodes_reverse ON edges (right_node_id, left_node_id);

2. 替换IN子句的字符串拼接方案 #

当节点数量达到10万+时，用字符串拼接生成超长IN列表会导致SQL解析效率极低，甚至触发语法限制，推荐以下两种方案：

方案A：使用临时表关联查询 #

先将目标节点ID插入临时表（自动带主键索引），再通过关联查询筛选边：

# 创建临时表并插入目标节点数据
temp_table_sql = """
CREATE TEMPORARY TABLE target_nodes (node_id INT PRIMARY KEY);
INSERT INTO target_nodes (node_id) VALUES {};
"""
# 生成节点值的SQL格式字符串，如(1),(2),(3)...
values_str = ','.join([f'({n})' for n in node_list])
connection.execute(temp_table_sql.format(values_str))

# 执行关联查询
edge_query = """
SELECT ed.*
FROM edges ed
JOIN target_nodes tn1 ON ed.left_node_id = tn1.node_id
JOIN target_nodes tn2 ON ed.right_node_id = tn2.node_id;
"""
edges_retrieve = sqlio.read_sql(edge_query, connection)

方案B：使用PostgreSQL数组参数 #

借助psycopg2的数组参数支持，直接传递节点列表，避免SQL拼接，同时提升查询效率：

import psycopg2  # 需要使用psycopg2作为数据库驱动

edge_query = """
SELECT *
FROM edges ed
WHERE left_node_id = ANY(%s)
AND right_node_id = ANY(%s);
"""
# 直接传入节点列表作为参数，psycopg2会自动转换为PostgreSQL数组
edges_retrieve = sqlio.read_sql(edge_query, connection, params=(node_list, node_list))

3. 避免全列查询 #

如果不需要边表的所有字段，明确指定需要的列，减少数据传输和内存占用：

SELECT left_node_id, right_node_id, edge_attribute -- 替换为实际需要的列
FROM edges ed
...

4. 调整PostgreSQL内存配置（可选） #

针对大数据查询场景，调整数据库的内存参数，减少磁盘IO：

• work_mem：提升该值让排序、连接操作优先使用内存，比如设置为64MB（根据可用内存调整）
• shared_buffers：设置为系统内存的1/4左右（32GB内存可设为8GB）

临时设置示例：

SET work_mem = '64MB';
SET shared_buffers = '8GB';

如需永久生效，修改postgresql.conf文件后重启数据库。

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