Re: Possible performance issues: DISTINCT ON + ORDER BY and JIT with aggregation

On Tue, 2025-12-16 at 21:25 -0300, Jhonathan Cruz wrote:
> I would like to report two query execution behaviors that may indicate
> performance issues or regressions. I am not certain whether these are
> known or expected behaviors, so I would appreciate guidance.
> 
> ----------------------------------------------------------------------
> 1) DISTINCT ON combined with ORDER BY
> ----------------------------------------------------------------------
> 
> I observed cases where queries using DISTINCT ON together with ORDER BY
> produce execution plans with explicit sorting steps, even when the ordering
> requirements are clearly defined.
> 
> Query:
> 
> EXPLAIN (ANALYZE, BUFFERS)
> SELECT DISTINCT ON (grp)
>        grp, id, created_at
> FROM t
> ORDER BY grp, created_at DESC;
> 
> Example EXPLAIN ANALYZE output:
> 
> Unique  (cost=45231.12..47731.12 rows=100 width=24)
>         (actual time=182.413..198.721 rows=100 loops=1)
>   Buffers: shared hit=12845
>   ->  Sort  (cost=45231.12..46481.12 rows=500000 width=24)
>             (actual time=182.410..191.256 rows=500000 loops=1)
>         Sort Key: grp, created_at DESC
>         Sort Method: quicksort  Memory: 51200kB
>         Buffers: shared hit=12845
>         ->  Seq Scan on t  (cost=0.00..11231.00 rows=500000 width=24)
>               (actual time=0.012..38.117 rows=500000 loops=1)
>               Buffers: shared hit=12845
> Planning Time: 0.213 ms
> Execution Time: 202.981 ms
> 
> Question:
> - Is this planner behavior expected for DISTINCT ON queries, or could
>   this case potentially avoid a full sort?

That is normal and as expected.  If you need all result rows, you have
to sort all the rows to find the unique results.

If you put a LIMIT clause on the query, PostgreSQL could perform a
top-N sort.

DISTINCT is easy to write, but sometimes the most expensive part of a
query.  I don't think that can be avoided.

> ----------------------------------------------------------------------
> 2) Possible JIT performance regression with aggregation and light filtering
> ----------------------------------------------------------------------
> 
> On PostgreSQL 16.x, I observed that queries performing aggregation with
> light filtering may run slower with JIT enabled compared to JIT disabled.
> 
> Query:
> 
> SET jit = on;
> 
> EXPLAIN (ANALYZE, BUFFERS)
> SELECT grp, count(*)
> FROM t
> WHERE id > 100
> GROUP BY grp;
> 
> Example EXPLAIN ANALYZE output (JIT enabled):
> 
> HashAggregate  (cost=12431.22..12441.22 rows=100 width=12)
>                (actual time=156.234..156.941 rows=100 loops=1)
>   Group Key: grp
>   Buffers: shared hit=12483
>   ->  Seq Scan on t  (cost=0.00..11231.00 rows=499900 width=4)
>         (actual time=0.014..52.731 rows=499900 loops=1)
>         Filter: (id > 100)
>         Rows Removed by Filter: 100
>         Buffers: shared hit=12483
> JIT:
>   Functions: 5
>   Options: Inlining true, Optimization true, Expressions true, Deforming true
>   Timing: Generation 2.312 ms, Inlining 18.942 ms,
>           Optimization 94.117 ms, Emission 22.884 ms, Total 138.255 ms
> Planning Time: 0.184 ms
> Execution Time: 159.214 ms

Now this is more like a bug.  JIT still has its problems.  I think not the
least of the problems is that PostgreSQL cannot predict how long the
compilation will take.

At the risk of drawing fire, I have now come to recommend turning JIT off
unless you have an analytical workload.

Yours,
Laurenz Albe