Improving information_schema._pg_expandarray()

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 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.

+1

regards

Pavel

[ 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 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 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