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