Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible

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