From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Jhonathan Cruz <jhonathancruz48@gmail.com>
To: pgsql-bugs@postgresql.org
Subject: Re: Possible performance issues: DISTINCT ON + ORDER BY and JIT with aggregation
Date: Wed, 17 Dec 2025 16:14:11 +0100
Message-ID: <90df511ca8ff12c52e05b7ed3500b1233ef93def.camel@cybertec.at> (raw)
In-Reply-To: <CAP+GjonDf7X_cS8+iAYq8W8ZARZ4Hib4Lz49XguyhSj5oV1ing@mail.gmail.com>
References: <CAP+GjonDf7X_cS8+iAYq8W8ZARZ4Hib4Lz49XguyhSj5oV1ing@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: laurenz.albe@cybertec.at, jhonathancruz48@gmail.com
Subject: Re: Possible performance issues: DISTINCT ON + ORDER BY and JIT with aggregation
In-Reply-To: <90df511ca8ff12c52e05b7ed3500b1233ef93def.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox