Django中annotate结合ExtractYear无法按提取年份过滤问题

我定义了如下KeyAccessLog模型，其中包含一个基于key、created字段的月份和年份提取值的复合索引：

class KeyAccessLog(models.Model):
    key = models.ForeignKey(
        Key, related_name="access_logs", on_delete=models.CASCADE
    )
    path = models.CharField(max_length=255)
    method = models.CharField(max_length=10)
    ip_address = models.GenericIPAddressField()
    created = models.DateTimeField(auto_add_now=True)

    class Meta:
        verbose_name = "Key Access Log"
        verbose_name_plural = "Key Access Logs"
        ordering = ("-pk",)
        indexes = [
            models.Index(
                "key",
                ExtractMonth("created"),
                ExtractYear("created"),
                name="key_month_year_idx",
            ),
        ]

    def __str__(self):
        return f"{self.key} - {self.path} - {self.method}"

当我尝试按key、month和year过滤时，ORM生成的SQL中，年份始终用范围查询（BETWEEN）而非EXTRACT(YEAR)，导致自定义索引无法生效：

执行代码：

dt_now = timezone.now()
qs = key.access_logs.filter(created__month=dt_now.month, created__year=dt_now.year)

生成的SQL：

SELECT
    "public_keyaccesslog"."id",
    "public_keyaccesslog"."key_id",
    "public_keyaccesslog"."path",
    "public_keyaccesslog"."method",
    "public_keyaccesslog"."ip_address",
    "public_keyaccesslog"."created"
FROM
    "public_keyaccesslog"
WHERE
    (
        "public_keyaccesslog"."key_id" = 1
        AND EXTRACT(
            MONTH
            FROM
                "public_keyaccesslog"."created" AT TIME ZONE 'UTC'
        ) = 5
        AND "public_keyaccesslog"."created" BETWEEN '2025-01-01 00:00:00+00:00' AND '2025-12-31 23:59:59.999999+00:00'
    )
ORDER BY
    "public_keyaccesslog"."id" DESC

即使显式标注月份和年份的提取，生成的SQL依然没有改变，我期望的SQL应该是用EXTRACT(YEAR)来过滤年份，从而触发自定义索引：

SELECT
    "public_keyaccesslog"."id",
    "public_keyaccesslog"."key_id",
    "public_keyaccesslog"."path",
    "public_keyaccesslog"."method",
    "public_keyaccesslog"."ip_address",
    "public_keyaccesslog"."created",
    EXTRACT(
        MONTH
        FROM
            "public_keyaccesslog"."created" AT TIME ZONE 'UTC'
    ) AS "month",
    EXTRACT(
        YEAR
        FROM
            "public_keyaccesslog"."created" AT TIME ZONE 'UTC'
    ) AS "year"
FROM
    "public_keyaccesslog"
WHERE
    (
        "public_keyaccesslog"."key_id" = 1
        AND EXTRACT(
            MONTH
            FROM
                "public_keyaccesslog"."created" AT TIME ZONE 'UTC'
        ) = 5
        AND EXTRACT(
            YEAR
            FROM
                "public_keyaccesslog"."created" AT TIME ZONE 'UTC'
        ) = 2025
    )
ORDER BY
    "public_keyaccesslog"."id" DESC

核心原因 #

Django的__year查询优化器会自动将年份过滤转换为范围查询（BETWEEN年初和年末），这是官方为兼容部分数据库性能做的优化，但会导致无法匹配基于EXTRACT的函数索引。必须显式使用函数表达式构建过滤条件，才能让ORM生成符合索引定义的SQL。

方法1：直接使用ExtractYear构建过滤条件 #

跳过__year快捷查询，用ExtractYear函数直接写过滤逻辑：

from django.db.models.functions import ExtractMonth, ExtractYear
from django.utils import timezone

dt_now = timezone.now()
qs = key.access_logs.filter(
    key=key,
    ExtractMonth("created") == dt_now.month,
    ExtractYear("created") == dt_now.year
)

方法2：结合Q表达式实现灵活过滤 #

如果需要和其他条件组合，可以用Q表达式封装：

from django.db.models import Q
from django.db.models.functions import ExtractMonth, ExtractYear

dt_now = timezone.now()
qs = key.access_logs.filter(
    Q(key=key) &
    Q(**{f"{ExtractMonth('created')}": dt_now.month}) &
    Q(**{f"{ExtractYear('created')}": dt_now.year})
)

方法3：统一时区处理（针对带时区的DateTimeField） #

如果created字段是带时区的，确保索引和查询中的时区处理一致，避免ORM自动转换逻辑：

1. 修改模型索引，明确指定时区：

from django.db.models import DateTimeField
from django.db.models.functions import ExtractMonth, ExtractYear, Cast

class KeyAccessLog(models.Model):
    # ... 其他字段 ...
    class Meta:
        # ... 其他配置 ...
        indexes = [
            models.Index(
                "key",
                ExtractMonth(Cast("created", DateTimeField(timezone=True))),
                ExtractYear(Cast("created", DateTimeField(timezone=True))),
                name="key_month_year_idx",
            ),
        ]

2. 查询时同步时区处理：

from django.db.models import DateTimeField
from django.db.models.functions import ExtractMonth, ExtractYear, Cast
from django.utils import timezone

dt_now = timezone.now()
qs = key.access_logs.filter(
    key=key,
    ExtractMonth(Cast("created", DateTimeField(timezone=True))) == dt_now.month,
    ExtractYear(Cast("created", DateTimeField(timezone=True))) == dt_now.year
)

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