避免由 DATE_TRUNC 引起的對 Indexed Group By 排序

有沒有辦法避免由於函式未被辨識為索引的一部分而導致的Sort操作？該功能不會真正影響.GROUP BY``DATE_TRUNC``tstamp``ix``ix

CREATE INDEX ix ON tbl(col, tstamp, num)

 SELECT col,
        DATE_TRUNC('MONTH', tstamp AT TIME ZONE 'UTC') AS trunc,
        SUM(num)
   FROM tbl
  WHERE col IN (?, ?, ...)
    AND tstamp >= ?
    AND tstamp <  ?
GROUP BY col, trunc

精度參數DATE_TRUNC是動態的，它也可以是'DAY'或'YEAR'……所以如果不為每個變體創建索引，我就無法索引該函式。

我得到的執行計劃：

Finalize GroupAggregate
 Group Key: col, DATE_TRUNC('MONTH', tstamp AT TIME ZONE 'UTC')
 ->  Gather Merge
       Workers Planned: 2
       Workers Launched: 2
       ->  Partial GroupAggregate
             Group Key: col, DATE_TRUNC('MONTH', tstamp AT TIME ZONE 'UTC')
             ->  Sort
                   Sort Key: col, DATE_TRUNC('MONTH', tstamp AT TIME ZONE 'UTC')
                   Sort Method: external merge  Disk: ...kB
                   -> Parallel Index Only Scan using ix on tbl
                        Index Cond: ((col = ANY (...)) AND (tstamp >= ...) AND (tstamp < ...))
                        Heap Fetches: 0

我想要的執行計劃看起來像這樣：

GroupAggregate
 Group Key: col, DATE_TRUNC('MONTH', tstamp AT TIME ZONE 'UTC')
 ->  Index Only Scan using ix on tbl
       Index Cond: ((col = ANY (...)) AND (tstamp >= ...) AND (tstamp < ...))
       Heap Fetches: 0

（如果我不使用，我會明白DATE_TRUNC）

沒有辦法避免使用您的查詢進行排序，因為 PostgreSQL 不知道索引掃描返回的行是否會正確排序，並且組聚合需要排序輸入。

你唯一的機會是增加work_mem，直到你得到一個不需要排序輸入的雜湊聚合。

引用自：https://dba.stackexchange.com/questions/264294