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

Hello,

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.

Minimal reproduction:

CREATE TABLE t (
id bigint,
grp int,
created_at timestamp
);

INSERT INTO t
SELECT
g,
g % 100,
now() - (g || ' seconds')::interval
FROM generate_series(1, 500000) g;

ANALYZE t;

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

Observed behavior:
- Explicit Sort node before Unique
- Full sort over all rows
- Execution time higher than expected

Question:
- Is this planner behavior expected for DISTINCT ON queries, or could
this case potentially avoid a full sort?

----------------------------------------------------------------------
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

For comparison, with JIT disabled:

SET jit = off;

EXPLAIN (ANALYZE, BUFFERS)
SELECT grp, count(*)
FROM t
WHERE id > 100
GROUP BY grp;

Example EXPLAIN ANALYZE output (JIT disabled):

HashAggregate (cost=12431.22..12441.22 rows=100 width=12)
(actual time=41.312..42.117 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.011..35.912 rows=499900 loops=1)
Filter: (id > 100)
Rows Removed by Filter: 100
Buffers: shared hit=12483
Planning Time: 0.121 ms
Execution Time: 44.287 ms

Observed behavior:
- JIT compilation time dominates query execution
- Query runs significantly faster with JIT disabled
- This differs from behavior observed on PostgreSQL 14.x for similar queries

Environment:
- PostgreSQL version: 16.x
- OS: <fill if needed>
- CPU: <fill if needed>
- jit: on/off
- shared_buffers: <fill if needed>

I am unsure whether these behaviors are expected, regressions, or already
tracked issues, and would appreciate any feedback or direction.

Regards,
Jhonathan Cruz