PostgreSQL中如何创建索引加速关联查询及优化现有查询？

1. 测试数据量过小 #

你当前只有几条测试数据，PostgreSQL的查询优化器会评估执行成本：对于极小的表，全表扫描（Seq Scan）的开销比走索引更低——因为索引需要额外IO定位数据块，反而不如直接扫全表高效，所以优化器会主动选择全表扫描。

2. 查询语句写法冗余 #

原查询里的子查询完全多余：先把phonebook和entries做全量关联，再和过滤后的t1关联，这种写法会让优化器处理更大的中间结果集，还容易触发全表扫描生成子查询结果。

1. 简化查询语句 #

去掉冗余子查询，直接通过过滤条件找到目标联系人的id，再关联获取该联系人的所有记录，写法简洁且优化器更容易选最优路径：

select pb.id, pb.name, e.label, e.value
from entries t1
join phonebook pb on t1.id = pb.id
join entries e on pb.id = e.id
where t1.label = 'offemail' and t1.value = 'amy@a.com';

更高效的写法（先拿到目标id再查询）：

with target_id as (
    select id from entries where label = 'offemail' and value = 'amy@a.com'
)
select pb.id, pb.name, e.label, e.value
from target_id ti
join phonebook pb on ti.id = pb.id
join entries e on pb.id = e.id;

2. 验证索引有效性（数据量提升后） #

当业务数据量增大后，优化器会自动选择索引扫描。如果要提前验证，可临时关闭全表扫描（仅测试用，生产环境别长期开）：

SET enable_seqscan = off;

之后查看执行计划，就能看到索引是否被使用。

3. 创建针对性索引 #

如果这类查询是高频操作，可创建覆盖索引进一步提升性能：

-- 保留你已有的索引，用于快速定位目标id
create unique index if not exists idx_entries_labelvalue on entries(label, value);
-- 快速获取同一id下所有条目，包含label和value避免回表查询
create index if not exists idx_entries_id_label_value on entries(id) include (label, value);

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