PostgreSQL JOIN查询EXPLAIN执行计划解读与慢查询排查

先把你总结的要点补全，照着这个顺序看执行计划不会错：

• 执行顺序按缩进深度优先：缩进最深、cost从0.00开始的节点最先执行，上层节点消费下层节点的输出，从下往上、从深到浅读
• 优先看真实执行指标：cost是优化器的预估值，不准的时候很多，优先看actual time（真实耗时）、Buffers（数据块扫描量，shared hit是内存读、read是磁盘读，数值越高开销越大）、Rows Removed by Filter（被过滤掉的无效行数，这个值高必然是瓶颈）
• 并行节点算总耗时要乘loops：节点标注的loops是这个步骤实际执行的次数，单次耗时乘循环次数才是这个步骤的总开销
• 不要被最顶层节点的cost骗了：顶层节点的cost是所有下层节点的累计值，瓶颈一定在累计开销占比最高的下层节点

顺着执行计划从深到浅拆，问题非常明确：

1. 最先执行的是graph_family_links表的祖先ID索引扫描：用现有索引index_graph_family_links_on_ancestor_concept_id筛出ancestor_concept_id=1016的记录，一共扫出47346条，这个步骤只扫了67个内存块，本身很快。
2. 紧接着的Parallel Bitmap Heap Scan回表步骤占了整个查询99%的开销：
   • 这个步骤拿着前面索引扫出的47346条记录回表，逐行判断generation=1的条件，两个并行worker一共过滤掉了37700条不符合条件的记录，最后每个worker只留了6条有效记录
   • 这个步骤一共扫了12985个内存块，占总Buffer扫描量的99%以上，本质是索引建错了：你只在descendent_concept_id上建了索引，但graph_family_links表的过滤条件是ancestor_concept_id和generation两个字段，现有单字段索引只能先筛出祖先ID匹配的近5万条记录，回表再挨个判断generation值，扫了大量完全没用的数据。
3. 后面的关联、排序、去重步骤开销可以忽略：
   • 关联graph_concepts的时候走主键索引，每次只查1条记录，一共才执行13次，几乎没耗时
   • 最后做DISTINCT排序的时候只有13条结果，排序内存才占25kB，完全不是问题

注：你贴的执行计划里真实执行时间只有33.7ms，和你说的3秒耗时不符，大概率是首次执行冷数据读、统计信息过期或者没跑ANALYZE的问题，但瓶颈点不受影响。

直接给graph_family_links表建三字段联合索引即可：

CREATE INDEX idx_fl_ancestor_gen_desc ON graph_family_links(ancestor_concept_id, generation, descendent_concept_id);

建完这个索引后，过滤graph_family_links表的时候可以直接在索引层面同时满足ancestor_concept_id=1016和generation=1两个条件，不需要回表扫几万条无效记录，直接从索引里拿到符合条件的descendent_concept_id去关联graph_concepts表，总耗时可以降到1ms级别。
你之前建的descendent_concept_id单字段索引在这个查询里完全用不上：这个查询是先过滤graph_family_links再关联graph_concepts，descendent_concept_id是关联输出字段，不是过滤条件，在驱动表上建这个索引对过滤效率没有任何提升，被驱动表graph_concepts的关联字段是主键，本身就有索引，足够用了。

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