提高PostgreSQL查询性能

Question

我正在数据库中运行以下查询：

select
(
  select least(2147483647, sum(pb.nr_size)) 
  from tb_pr_dc pd
  inner join tb_pr_dc_bn pb on 1=1
    and pb.id_pr_dc_bn = pd.id_pr_dc_bn 
  where 1=1
    and pd.id_pr = pt.id_pr -- outer query column
) 
from 
(
  select regexp_split_to_table('[list of 500 ids]', ',')::integer id_pr 
) pt 
;

它输出500行，其中只有一个结果列，运行大约需要1分钟43秒。解释（分析、详细、缓冲）输出以下计划：

Subquery Scan on pt  (cost=0.00..805828.19 rows=1000 width=8) (actual time=96.791..103205.872 rows=500 loops=1)
  Output: (SubPlan 1)
  Buffers: shared hit=373771 read=153484
  ->  Result  (cost=0.00..22.52 rows=1000 width=4) (actual time=0.434..3.729 rows=500 loops=1)
        Output: ((regexp_split_to_table('[list of 500 ids]', ',')::integer id_pr)
        ->  ProjectSet  (cost=0.00..5.02 rows=1000 width=32) (actual time=0.429..2.288 rows=500 loops=1)
              Output: (regexp_split_to_table('[list of 500 ids]', ',')::integer id_pr
              ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)
  SubPlan 1
    ->  Aggregate  (cost=805.78..805.80 rows=1 width=8) (actual time=206.399..206.400 rows=1 loops=500)
          Output: LEAST('2147483647'::bigint, sum((pb.nr_size)::integer))
          Buffers: shared hit=373771 read=153484
          ->  Nested Loop  (cost=0.87..805.58 rows=83 width=4) (actual time=1.468..206.247 rows=219 loops=500)
                Output: pb.nr_size
                Inner Unique: true
                Buffers: shared hit=373771 read=153484
                ->  Index Scan using tb_pr_dc_in05 on db.tb_pr_dc pd  (cost=0.43..104.02 rows=83 width=4) (actual time=0.233..49.289 rows=219 loops=500)
                      Output: pd.id_pr_dc, pd.ds_pr_dc, pd.id_pr, pd.id_user_in, pd.id_user_ex, pd.dt_in, pd.dt_ex, pd.ds_mt_ex, pd.in_at, pd.id_tp_pr_dc, pd.id_pr_xz (...)
                      Index Cond: ((pd.id_pr)::integer = pt.id_pr)
                      Buffers: shared hit=24859 read=64222
                ->  Index Scan using tb_pr_dc_bn_pk on db.tb_pr_dc_bn pb  (cost=0.43..8.45 rows=1 width=8) (actual time=0.715..0.715 rows=1 loops=109468)
                      Output: pb.id_pr_dc_bn, pb.ds_ex, pb.ds_md_dc, pb.ds_m5_dc, pb.nm_aq, pb.id_user, pb.dt_in, pb.ob_pr_dc, pb.nr_size, pb.ds_sg, pb.ds_cr_ch, pb.id_user_ (...)
                      Index Cond: ((pb.id_pr_dc_bn)::integer = (pd.id_pr_dc_bn)::integer)
                      Buffers: shared hit=348912 read=89262
Planning Time: 1.151 ms
Execution Time: 103206.243 ms

逻辑是：对于每个选择的id_pr（在500个id的列表中）计算整数列pb的总和。与之关联的nr_size，返回该金额与数字2147483647之间的较小值。结果必须包含500行，每个id对应一行，我们已经知道它们将与子查询中的至少一行匹配，因此不会生成空值。

索引tb_pr_dc_in05仅是id_pr上的一个b树，属于整数类型。索引tb_pr_dc_bn_pk是主键id_pr_dc_bn上的b树，也是整数类型。表tb_pr_dc中的每个id_pr都有许多行。实际上，我们在tb_pr_dc中有209217个唯一的id_prs，总共有13910855行。表tb_pr_dc_bn的行数相同。

可以看到，我们定义了500个id来查询tb_pr_dc，查找109,468行（小于表大小的1%），然后在tb_pr_dc_bn中查找相同数量的行。然而，索引看起来很好，要计算的行数很少，所以我不明白为什么要花这么多时间来运行这个查询。许多其他查询在其他表上读取更多数据并进行更多计算都运行良好。DBA刚刚运行了重新索引和真空分析，但它仍然以同样缓慢的方式运行。我们正在Linux上运行PostgreSQL 11。我在没有并发访问的副本中运行此查询。

还有什么可以提高查询性能的地方？

谢谢你的关注。

Answer 1

花费的时间是在表中跳来跳去寻找109468个随机分散的行，并发出随机的IO请求来这样做。你可以验证打开track_io_timing并重新执行计划（可能只是让它全局打开，默认情况下，开销很低，它产生的价值很高），但我足够肯定，在达到这一点之前，我不需要看到那个输出结论。其他更快的查询可能访问更少的磁盘页，因为它们访问的数据包装更紧密，或者组织得更有序。事实上，考虑到你的查询需要阅读多少页，我会说你的查询相当快。

您会问为什么在计划的内部节点中会输出这么多列。原因是PostgreSQL通常只是传递指向元组在共享_缓冲区中的位置的指针，而被指向的元组具有表本身所具有的列。它可以分配内存，在其中存储经过重新格式化的元组版本，去掉不必要的列，但这通常需要更多的工作，而不是更少的工作。如果这是复制和重新形成元组的原因，它会在复制和重新形成元组的同时删除无关的列。但它不会无缘无故这么做。

加快速度的一种方法是创建启用仅索引扫描的索引。tb_pr_dc（id_pr，id_pr_dc_bn）和tb_pr_dc_bn（id_pr_dc_bn，nr_size）。

如果这还不够，可能还有其他方法来改善这一点；但是，如果我一直被你对表名和列名的冗长的、无法描述的、无法发音的胡言乱语分心，我就无法把它们想清楚。