Horton

Thread

Commits

  1. Improve the implementation of information_schema._pg_expandarray().

  1. Improving information_schema._pg_expandarray()

    Tom Lane <tgl@sss.pgh.pa.us> — 2023-12-23T18:18:05Z

    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

  2. Re: Improving information_schema._pg_expandarray()

    Pavel Stehule <pavel.stehule@gmail.com> — 2023-12-23T18:27:18Z

    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


>                         regards, tom lane
>
>

  3. Re: Improving information_schema._pg_expandarray()

    Dagfinn Ilmari Mannsåker <ilmari@ilmari.org> — 2024-05-13T16:56:07Z

    [ 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