PostgreSQL查询性能咨询：全表扫描、字段选择与水平分区表差异问题

问题1：Select * from lineitem与Select l_orderkey from lineitem是否无性能差异？ #

• 二者存在明显性能差异，即使PostgreSQL为行式存储也不例外：
  • 行式存储虽然按行读取磁盘块，但读取到内存后需要解析每行的元组结构，仅取单列时不需要处理其他字段的编码、类型转换逻辑，CPU开销更低
  • 如果l_orderkey列存在索引，PostgreSQL会直接走索引-only扫描，不需要读取主表数据，性能会比全表扫描*快数倍甚至数十倍
  • 网络传输层面，仅返回单列的数据量远小于返回全表所有列，你测试时设置了fetchsize分批拉取，数据量更小的情况下网络往返开销也会更低

问题2：同是全表扫描，Select count(*)为什么比Select *耗时更低？ #

• 两个查询的各阶段开销差异极大：
  • count(*)只需要在扫描每个磁盘块时统计有效元组的数量，不需要解析元组的具体字段内容，也不需要把字段数据返回给客户端，整个计算过程几乎都在数据库服务端完成，只有最终的单个统计值会传输到Java端
  • Select *需要解析每行所有字段的内容，还要把全量数据通过网络传输到Java客户端，哪怕你Java代码里没有读取字段值，JDBC驱动也会把所有字段数据拉到本地内存，二者的CPU、IO、网络开销完全不在一个量级

问题3：水平拆分后查询耗时不符合预期的原因 #

• 拆分后的ordersO表和原表过滤o_orderstatus='O'的查询耗时接近属于正常情况，核心原因如下：
  • 原表查询Select * from orders where o_orderstatus='O'时，即使没有分区，只要o_orderstatus列有统计信息，PostgreSQL执行器也是全表扫描后过滤符合条件的行，扫描的总磁盘块数为整个orders表的大小，而ordersO的大小是原表的一半，理论上扫描IO应该减半，但你忽略了两个影响因素：
    1. 如果你没有给原表的o_orderstatus建索引，过滤操作本身是逐行判断的，CPU开销极低，瓶颈在磁盘IO时，如果是机械硬盘，随机IO开销占主导的话，连续扫描100G和扫描50G的差异可能被寻址开销抹平；如果是页缓存命中的场景，内存扫描的速度极快，二者的耗时差异会被误差掩盖
    2. 你测试的Java代码开销占比可能过高：两次查询拉取的结果集数据量完全相同（都是所有o_orderstatus='O'的行），网络传输和JDBC处理的开销完全一致，如果这部分开销占总耗时的比例很高，那么数据库端扫描的IO差异就体现不出来

你测试用的Java代码存在两个小问题会影响测试精度：一是System.currentTimeMillis()的精度只有毫秒级，高耗时场景误差小但低耗时场景误差大，建议用System.nanoTime()；二是没有做预热，第一次查询的页缓存没命中，后面查询缓存命中会导致结果不准，建议每个查询跑3次以上取后几次的结果。

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