Improving information_schema._pg_expandarray()
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t48939psql -h localhost -U postgresBuilt from patchset v3 (message #3), September 20, 2026 at 03:00 PM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t48939_3 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t48939_3 && git checkout t48939_3Patchset v3 (message #3) is on t48939_3
I happened to notice that information_schema._pg_expandarray(),
which has the nigh-unreadable definition
AS 'select $1[s],
s operator(pg_catalog.-) pg_catalog.array_lower($1,1) operator(pg_catalog.+) 1
from pg_catalog.generate_series(pg_catalog.array_lower($1,1),
pg_catalog.array_upper($1,1),
1) as g(s)';
can now be implemented using unnest():
AS 'SELECT * FROM pg_catalog.unnest($1) WITH ORDINALITY';
It seems to be slightly more efficient this way, but the main point
is to make it more readable.
I then realized that we could also borrow unnest's infrastructure
for rowcount estimation:
ROWS 100 SUPPORT pg_catalog.array_unnest_support
because array_unnest_support just looks at the array argument and
doesn't have any hard dependency on the function being specifically
unnest(). I'm not sure that any of its uses in information_schema
can benefit from that right now, but surely it can't hurt.
One minor annoyance is that psql.sql is using _pg_expandarray
as a test case for \sf[+]. While we could keep doing so, I think
the main point of that test case is to exercise \sf+'s line
numbering ability, so the new one-line body is not a great test.
I changed that test to use _pg_index_position instead.
regards, tom lane
Attachments:
v1-improve-_pg_expandarray.patchtext/x-diff; charset=us-ascii; name=v1-improve-_pg_expandarray.patchDownload+29-25
so 23. 12. 2023 v 19:18 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:
I happened to notice that information_schema._pg_expandarray(),
which has the nigh-unreadable definitionAS 'select $1[s],
s operator(pg_catalog.-) pg_catalog.array_lower($1,1)
operator(pg_catalog.+) 1
from pg_catalog.generate_series(pg_catalog.array_lower($1,1),
pg_catalog.array_upper($1,1),
1) as g(s)';can now be implemented using unnest():
AS 'SELECT * FROM pg_catalog.unnest($1) WITH ORDINALITY';
It seems to be slightly more efficient this way, but the main point
is to make it more readable.I then realized that we could also borrow unnest's infrastructure
for rowcount estimation:ROWS 100 SUPPORT pg_catalog.array_unnest_support
because array_unnest_support just looks at the array argument and
doesn't have any hard dependency on the function being specifically
unnest(). I'm not sure that any of its uses in information_schema
can benefit from that right now, but surely it can't hurt.One minor annoyance is that psql.sql is using _pg_expandarray
as a test case for \sf[+]. While we could keep doing so, I think
the main point of that test case is to exercise \sf+'s line
numbering ability, so the new one-line body is not a great test.
I changed that test to use _pg_index_position instead.
+1
regards
Pavel
Show quoted text
regards, tom lane
[ I got distracted while writing this follow-up and only just found it
in my list of unsent Gnus buffers, and now it's probably too late to
make it for 17, but here it is anyway while I remember. ]
Tom Lane <tgl@sss.pgh.pa.us> writes:
I happened to notice that information_schema._pg_expandarray(),
which has the nigh-unreadable definitionAS 'select $1[s],
s operator(pg_catalog.-) pg_catalog.array_lower($1,1) operator(pg_catalog.+) 1
from pg_catalog.generate_series(pg_catalog.array_lower($1,1),
pg_catalog.array_upper($1,1),
1) as g(s)';can now be implemented using unnest():
AS 'SELECT * FROM pg_catalog.unnest($1) WITH ORDINALITY';
It seems to be slightly more efficient this way, but the main point
is to make it more readable.
I didn't spot this until it got committed, but it got me wondering what
eliminating the wrapper function completely would look like, so I
whipped up the attached. It instead calls UNNEST() laterally in the
queries, which has the side benefit of getting rid of several
subselects, one of which was particularly confusing. In one place the
lateral form eliminated the need for WITH ORDINALITY as well.
- ilmari