Post #19

以前用 SQL 数据库时数据量一般都很小，以至于无论生成什么样的 query plan，查询都能很快完成（尤其是一般 web server 使用数据库的模式都很少涉及到多个表的 join，非常容易规划） 最近需要对比较大量的数据做复杂的分析，然后将结果画在 Grafana 上，所以专门调研了一番怎么优化查询，确保秒级完成。才发现原来对 join 的规划几乎是这里面最复杂的问题，而我以前以为很棒的 PostgreSQL 的 query planner 其实因为是通用的，对一些具体场景能做的事情非常非常有限，远远达不到想象中什么都能优化好的状态。 在有多个表需要 join 时，需要知道每个表 where 选择出来的大致行数，以及两两 join 起来后的大致行数，才能决定最佳的 join 顺序，以及每一步 join 要使用的算法。但行数的估计是非常困难的，如果对具体数据库的使用场景没有额外信息的话。PostgreSQL 默认做法是把表中每一列看作相互独立的，分别采样。规划时对每一列上的 where 条件独立计算概率，这对于列之间有依赖关系的情况就会有问题。用户可以手工创建一些采样指示，要求数据库对某几列合起来采样，但创建这种指示比较麻烦而且成本也高。 另外，即使能很好地估计每一个原始表的行数，要估计 join 结果的行数也是很困难的。在我的查询中，经常先把原始数据降采样成某个间隔等间距的时序数据（比如每行有时间和值两列，时间都是整分钟），然后把多个这样的序列按时间列 join 起来。PostgreSQL 没法知道这样 join 的结果的行数一定和 join 的各个表的行数相等（因为它们的时间列已经对齐了），有时会认为结果是笛卡尔积，有时会认为结果非常小。 基于这个现象，目前我发现的最大的优化措施是 set jit=off; 😂因为我自己知道我用到的 join 都是很容易做的，结果行数也不多，而数据库常常误以为结果行数是笛卡尔积那么多，处理起来需要小时甚至天量级的时间，然后认为有必要启用 JIT。JIT 需要数十秒，但在我的查询中不会带来什么回报。 另一个简单又有效的优化措施是多用 with 语法（common table expressions），并且为每一个这样定义的中间结果手工指定 materialized 或者 not materialized：如果只是简单的扫描型查询，但会输出大量数据，或者后续需要过滤它的结果，就指定 not materialized，以便 inline 进用到的地方并且一起规划。如果是昂贵的查询，并且产生很小的结果，就指定 materialized，以便后续直接使用结果。指望数据库自己知道每个中间结果该不该 inline 是很不现实的。 （为什么要用 SQL 数据库/为什么要用 PostgreSQL？因为据我所知别的数据库在很多方面更糟。尤其不推荐 InfluxDB，如果你想做需要 join 不同的数据才能实现的分析型查询的话。）