PostgreSQL中同逻辑查询在不同表执行耗时差异过大问题排查

表结构与索引信息 #

我有三张PostgreSQL表，数据量与结构如下：

• 表A：id varchar(36) pk, c_id varchar(36)，共120万行，已在c_id字段创建B-tree索引
• 表B：id varchar(36) pk, c_id varchar(36)，共60万行，已在c_id字段创建B-tree索引
• 表C：id varchar(36) pk, "number" varchar(128), "status" varchar(255)，共540万行；其中id为UUID字符串，number是12位字符串，status有50种可能取值；已在number和status字段创建B-tree索引

执行的两条查询语句 #

第一条查询（耗时约70ms）：

select a.id as id1, c.id as id2 from "A" a
    inner join "C" c on a."c_id" = c."id"
where c."status" in ('seven')
order by c."number" asc
limit 10;

第二条查询（耗时约1.5s）：

select b.id as id1, c.id as id2 from "B" b
    inner join "C" c on b."c_id" = c."id"
where c."status" in ('seven')
order by c."number" asc
limit 10;

性能差异与执行计划分析 #

两条查询逻辑完全一致，但耗时差距明显。对应的执行计划片段如下：

表A关联查询的执行计划片段 #

->  Index Scan using c_number_idx on C c (cost=0.43..917514.17 rows=2819568 width=50) (actual time=0.451..9.997 rows=17 loops=1)
Filter: (("status")::text = 'seven'::text)
Rows Removed by Filter: 4727
Buffers: shared hit=4679

表B关联查询的执行计划片段 #

->  Index Scan using c_number_idx on C c (cost=0.43..917514.17 rows=2819568 width=50) (actual time=0.673..878.256 rows=148095 loops=1)
Filter: (("status")::text = 'seven'::text)
Rows Removed by Filter: 184231
Buffers: shared hit=333349

疑问点 #

1. 为何过滤status时使用number字段的索引？
2. 针对表B的索引扫描为何有更多共享缓冲区命中和被过滤行数？
3. 为何表B的同逻辑查询耗时远高于表A？

已尝试的操作 #

对表B和C执行全清理、重建索引、多次执行查询，结果一致；因存在其他多种过滤需求，暂不考虑使用clustering。

附DDL语句 #

CREATE TABLE A(
  id varchar(36) primary key,
  c_id varchar(36)
);
create index on A(c_id);
CREATE TABLE B(
  id varchar(36) primary key,
  c_id varchar(36)
);
create index on B(c_id);
CREATE TABLE C(
  id varchar(36) primary key,
  "number" varchar(128), 
  "status" varchar(255)
);
create index on C("number");
create index on C("status");

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