Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible - Mailing list pgsql-hackers

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.442rows=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.895rows=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