Django ORM分区表datetime查询耗时突变差异原因排查求助

问题背景 #

我有一张带datetime字段btree索引的分区表，在Django 2.0中使用两种ORM查询方式，性能出现巨大差异：

• 第一种查询（耗时3.5-5秒）：

MyModel.objects.filter(my_datetime_field__gt=date(2022, 1 , 1))

• 第二种查询（耗时0.05秒）：

MyModel.objects.filter(my_datetime_field__date__gt=date(2022, 1 , 1))

此前两种请求耗时基本一致，当前出现差异的原因是什么？

环境信息 #

• Django版本：2.0
• PostgreSQL版本：12.3
• 索引类型：btree

已尝试操作 #

• 执行VACUUM (VERBOSE, ANALYZE) my_table
• 执行REINDEX INDEX my_index

可能的原因分析 #

1. 索引与分区裁剪逻辑差异（核心原因） #

两种查询生成的SQL逻辑不同，直接影响PostgreSQL对索引和分区的利用：

• 第一种查询生成的SQL近似为：

  SELECT * FROM my_table WHERE my_datetime_field > '2022-01-01'::date;
  

  PostgreSQL会自动将date类型转为datetime（即2022-01-01 00:00:00），但如果分区表的分区键是datetime类型，这种隐式类型转换可能导致PostgreSQL无法正确识别分区过滤条件，跳过分区裁剪，进而扫描所有子分区；同时若表的统计信息存在偏差，数据库可能放弃使用btree索引，选择全表扫描，导致耗时剧增。
• 第二种查询生成的SQL近似为：

  SELECT * FROM my_table WHERE DATE(my_datetime_field) > '2022-01-01'::date;
  

  虽然对字段使用了DATE()函数，但PostgreSQL 12.3支持对函数式分区键或带函数的过滤条件进行分区裁剪，若你的分区表是按日期分区，数据库能精准识别需要扫描的子分区；另外如果针对DATE(my_datetime_field)创建了函数索引，或者分区键本身基于日期，索引会被直接命中，查询速度大幅提升。

2. 统计信息或索引的分区覆盖问题 #

虽然你执行了VACUUM ANALYZE和REINDEX，但可能存在遗漏：

• 子分区统计信息未更新：VACUUM ANALYZE主表不会自动同步所有子分区的统计信息，部分子分区的统计数据过时，导致查询计划选择错误。
• 分区索引未完全重建：主表的btree索引是分区索引，REINDEX INDEX主表索引可能未覆盖到所有子分区的索引，个别子分区的索引仍存在异常，影响查询性能。

3. 数据分布的显著变化 #

近期表中数据分布发生较大变动（比如新增了大量早于2022-01-01的历史数据），第一种查询因无法裁剪分区，需要扫描的数据集规模剧增；而第二种查询通过分区裁剪仅访问少量符合条件的分区，因此性能差距被放大。

内容的提问来源于stack exchange，提问作者Валерий Аббакумов