Avoid Sort on Indexed Group By caused by DATE_TRUNC Is there a way to avoid Sort operation for GROUP BY, caused by the DATE_TRUNC function not being recognized as tstamp, which is a part of the ix index? That function doesn't really affect the order maintained by the 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 &gt;= ?
     AND tstamp &lt;  ?
GROUP BY col, trunc


Precision parameter for DATE_TRUNC is dynamic, it could also be 'DAY' or 'YEAR'... so I cannot index that function without creating an index for each variation.

The execution plan I get:

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


Execution plan I want would look something like this:

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


(I get that if I don't use DATE_TRUNC)
There is no way to avoid the sort with your query, because PostgreSQL does not know if the rows returned by the index scan will be correctly sorted or not, and a group aggregate requires sorted input.

Your only chance is to increase work_mem until you get a hash aggregate, which does not require sorted input.