Message-ID: <90df511ca8ff12c52e05b7ed3500b1233ef93def.camel@cybertec.at>
Subject: Re: Possible performance issues: DISTINCT ON + ORDER BY and JIT
 with aggregation
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Jhonathan Cruz <jhonathancruz48@gmail.com>, pgsql-bugs@postgresql.org
Date: Wed, 17 Dec 2025 16:14:11 +0100
In-Reply-To: 
 <CAP+GjonDf7X_cS8+iAYq8W8ZARZ4Hib4Lz49XguyhSj5oV1ing@mail.gmail.com>
References: 
	<CAP+GjonDf7X_cS8+iAYq8W8ZARZ4Hib4Lz49XguyhSj5oV1ing@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.2 (3.56.2-2.fc42) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/90df511ca8ff12c52e05b7ed3500b1233ef93def.camel%40cybertec.at>
Precedence: bulk

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.
>=20
> ----------------------------------------------------------------------
> 1) DISTINCT ON combined with ORDER BY
> ----------------------------------------------------------------------
>=20
> I observed cases where queries using DISTINCT ON together with ORDER BY
> produce execution plans with explicit sorting steps, even when the orderi=
ng
> requirements are clearly defined.
>=20
> Query:
>=20
> EXPLAIN (ANALYZE, BUFFERS)
> SELECT DISTINCT ON (grp)
> =C2=A0 =C2=A0 =C2=A0 =C2=A0grp, id, created_at
> FROM t
> ORDER BY grp, created_at DESC;
>=20
> Example EXPLAIN ANALYZE output:
>=20
> Unique =C2=A0(cost=3D45231.12..47731.12 rows=3D100 width=3D24)
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 (actual time=3D182.413..198.721 rows=3D100 lo=
ops=3D1)
> =C2=A0 Buffers: shared hit=3D12845
> =C2=A0 -> =C2=A0Sort =C2=A0(cost=3D45231.12..46481.12 rows=3D500000 width=
=3D24)
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 (actual time=3D182.410..191.256=
 rows=3D500000 loops=3D1)
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 Sort Key: grp, created_at DESC
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 Sort Method: quicksort =C2=A0Memory: 51200kB
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 Buffers: shared hit=3D12845
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 -> =C2=A0Seq Scan on t =C2=A0(cost=3D0.00..11=
231.00 rows=3D500000 width=3D24)
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 (actual time=3D0.012..38=
.117 rows=3D500000 loops=3D1)
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Buffers: shared hit=3D12=
845
> Planning Time: 0.213 ms
> Execution Time: 202.981 ms
>=20
> Question:
> - Is this planner behavior expected for DISTINCT ON queries, or could
> =C2=A0 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 filteri=
ng
> ----------------------------------------------------------------------
>=20
> On PostgreSQL 16.x, I observed that queries performing aggregation with
> light filtering may run slower with JIT enabled compared to JIT disabled.
>=20
> Query:
>=20
> SET jit =3D on;
>=20
> EXPLAIN (ANALYZE, BUFFERS)
> SELECT grp, count(*)
> FROM t
> WHERE id > 100
> GROUP BY grp;
>=20
> Example EXPLAIN ANALYZE output (JIT enabled):
>=20
> HashAggregate =C2=A0(cost=3D12431.22..12441.22 rows=3D100 width=3D12)
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0(actual time=3D156=
.234..156.941 rows=3D100 loops=3D1)
> =C2=A0 Group Key: grp
> =C2=A0 Buffers: shared hit=3D12483
> =C2=A0 -> =C2=A0Seq Scan on t =C2=A0(cost=3D0.00..11231.00 rows=3D499900 =
width=3D4)
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 (actual time=3D0.014..52.731 rows=3D499900 lo=
ops=3D1)
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 Filter: (id > 100)
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 Rows Removed by Filter: 100
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 Buffers: shared hit=3D12483
> JIT:
> =C2=A0 Functions: 5
> =C2=A0 Options: Inlining true, Optimization true, Expressions true, Defor=
ming true
> =C2=A0 Timing: Generation 2.312 ms, Inlining 18.942 ms,
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Optimization 94.117 ms, Emission 22.88=
4 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