Hi,
On 2026-02-05 15:23:38 -0500, Andres Freund wrote:
> Memory usage aside, it's also slow and expensive from the query execution and
> data transfer side. Because of the ORDER BY that the batching requires, the
> server needs to sort all of pg_largeobject_metadata before any rows can be
> returned.
>
> For 5M LOs:
> ┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
> │ QUERY PLAN
> │
> ├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
> │ Sort (cost=715978.34..728478.39 rows=5000020 width=72) (actual
> time=10292.252..10652.950 rows=5000020.00 loops=1)
> │
> │ Sort Key: pg_largeobject_metadata.lomowner,
> ((pg_largeobject_metadata.lomacl)::text), pg_largeobject_metadata.oid
> │
> │ Sort Method: quicksort Memory: 509110kB
> │
> │ -> Seq Scan on pg_largeobject_metadata (cost=0.00..159638.55
> rows=5000020 width=72) (actual time=0.034..2284.442 rows=5000020.00 loops=1) │
> │ SubPlan expr_1
> │
> │ -> Result (cost=0.00..0.01 rows=1 width=32) (actual
> time=0.000..0.000 rows=1.00 loops=5000020)
> │
> │ Planning Time: 0.117 ms
> │
> │ Serialization: time=3961.343 ms output=218686kB format=text
> │
> │ Execution Time: 14930.747 ms
> │
> └───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
> (9 rows)
This isn't quite the right query, sorry for that. I just tried moving the
acldefault() in a subselect, because I was wondering whether that'd prevent it
from being computed below the sort. Unfortunately no. It's a bit faster
without that, but not much:
EXPLAIN (ANALYZE, SERIALIZE, BUFFERS OFF, VERBOSE) SELECT oid, lomowner,
lomacl::pg_catalog.text, acldefault('L', lomowner) AS acldefault FROM
pg_largeobject_metadata ORDER BY lomowner, lomacl::pg_catalog.text, oid;
┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY
PLAN │
├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Sort (cost=665978.14..678478.19 rows=5000020 width=72) (actual
time=8887.007..9243.088 rows=5000020.00 loops=1)
│
│ Output: oid, lomowner, ((lomacl)::text), (acldefault('L'::"char",
lomowner))
│
│ Sort Key: pg_largeobject_metadata.lomowner,
((pg_largeobject_metadata.lomacl)::text), pg_largeobject_metadata.oid
│
│ Sort Method: quicksort Memory: 509110kB
│
│ -> Seq Scan on pg_catalog.pg_largeobject_metadata (cost=0.00..109638.35
rows=5000020 width=72) (actual time=0.029..823.895 rows=5000020.00 loops=1) │
│ Output: oid, lomowner, (lomacl)::text, acldefault('L'::"char",
lomowner)
│
│ Planning Time: 0.087 ms
│
│ Serialization: time=3965.649 ms output=218686kB format=text
│
│ Execution Time: 13516.925 ms
│
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(9 rows)
I might start a separate thread about this misoptimization...
Greetings,
Andres Freund
