Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit58054de

Browse files
committed
Improve the implementation of information_schema._pg_expandarray().
This function was originally coded with a handmade expansionof the array subscripts. We can do it a little faster and farmore legibly today, by using unnest() WITH ORDINALITY.While at it, let's apply the rowcount estimation support that existsfor the underlying unnest() function: reduce the default ROWS estimateto 100 and attach array_unnest_support. I'm not sure thatarray_unnest_support can do anything useful today with the call sitesthat exist in information_schema, but it can't hurt, and the existingdefault rowcount of 1000 is surely much too high for any of thesecases.The psql.sql regression script is using _pg_expandarray() as atest case for \sf+. While we could keep doing so, the new one-linefunction body makes a poor test case for \sf+ row-numbering, soswitch it to print another information_schema function.Discussion:https://postgr.es/m/1424303.1703355485@sss.pgh.pa.us
1 parent6c361d3 commit58054de

File tree

5 files changed

+30
-26
lines changed

5 files changed

+30
-26
lines changed

‎src/backend/catalog/information_schema.sql

Lines changed: 2 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -43,11 +43,8 @@ SET search_path TO information_schema;
4343
CREATEFUNCTION_pg_expandarray(IN anyarray, OUT x anyelement, OUT nint)
4444
RETURNS SETOF RECORD
4545
LANGUAGE sql STRICT IMMUTABLE PARALLEL SAFE
46-
AS'select $1[s],
47-
s operator(pg_catalog.-) pg_catalog.array_lower($1,1) operator(pg_catalog.+) 1
48-
from pg_catalog.generate_series(pg_catalog.array_lower($1,1),
49-
pg_catalog.array_upper($1,1),
50-
1) as g(s)';
46+
ROWS100 SUPPORTpg_catalog.array_unnest_support
47+
AS'SELECT * FROM pg_catalog.unnest($1) WITH ORDINALITY';
5148

5249
/* Given an index's OID and an underlying-table column number, return the
5350
* column's position in the index (NULL if not there)*/

‎src/backend/utils/adt/arrayfuncs.c

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6317,6 +6317,9 @@ array_unnest(PG_FUNCTION_ARGS)
63176317

63186318
/*
63196319
* Planner support function for array_unnest(anyarray)
6320+
*
6321+
* Note: this is now also used for information_schema._pg_expandarray(),
6322+
* which is simply a wrapper around array_unnest().
63206323
*/
63216324
Datum
63226325
array_unnest_support(PG_FUNCTION_ARGS)

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -57,6 +57,6 @@
5757
*/
5858

5959
/*yyyymmddN */
60-
#defineCATALOG_VERSION_NO202312251
60+
#defineCATALOG_VERSION_NO202312271
6161

6262
#endif

‎src/test/regress/expected/psql.out

Lines changed: 22 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -5293,26 +5293,30 @@ comment on function psql_df_plpgsql () is 'some comment';
52935293
rollback;
52945294
drop role regress_psql_user;
52955295
-- check \sf
5296-
\sf information_schema._pg_expandarray
5297-
CREATE OR REPLACE FUNCTION information_schema._pg_expandarray(anyarray, OUT x anyelement, OUT n integer)
5298-
RETURNSSETOF record
5296+
\sf information_schema._pg_index_position
5297+
CREATE OR REPLACE FUNCTION information_schema._pg_index_position(oid, smallint)
5298+
RETURNSinteger
52995299
LANGUAGE sql
5300-
IMMUTABLE PARALLEL SAFE STRICT
5301-
AS $function$select $1[s],
5302-
s operator(pg_catalog.-) pg_catalog.array_lower($1,1) operator(pg_catalog.+) 1
5303-
from pg_catalog.generate_series(pg_catalog.array_lower($1,1),
5304-
pg_catalog.array_upper($1,1),
5305-
1) as g(s)$function$
5306-
\sf+ information_schema._pg_expandarray
5307-
CREATE OR REPLACE FUNCTION information_schema._pg_expandarray(anyarray, OUT x anyelement, OUT n integer)
5308-
RETURNS SETOF record
5300+
STABLE STRICT
5301+
BEGIN ATOMIC
5302+
SELECT (ss.a).n AS n
5303+
FROM ( SELECT information_schema._pg_expandarray(pg_index.indkey) AS a
5304+
FROM pg_index
5305+
WHERE (pg_index.indexrelid = $1)) ss
5306+
WHERE ((ss.a).x = $2);
5307+
END
5308+
\sf+ information_schema._pg_index_position
5309+
CREATE OR REPLACE FUNCTION information_schema._pg_index_position(oid, smallint)
5310+
RETURNS integer
53095311
LANGUAGE sql
5310-
IMMUTABLE PARALLEL SAFE STRICT
5311-
1 AS $function$select $1[s],
5312-
2 s operator(pg_catalog.-) pg_catalog.array_lower($1,1) operator(pg_catalog.+) 1
5313-
3 from pg_catalog.generate_series(pg_catalog.array_lower($1,1),
5314-
4 pg_catalog.array_upper($1,1),
5315-
5 1) as g(s)$function$
5312+
STABLE STRICT
5313+
1 BEGIN ATOMIC
5314+
2 SELECT (ss.a).n AS n
5315+
3 FROM ( SELECT information_schema._pg_expandarray(pg_index.indkey) AS a
5316+
4 FROM pg_index
5317+
5 WHERE (pg_index.indexrelid = $1)) ss
5318+
6 WHERE ((ss.a).x = $2);
5319+
7 END
53165320
\sf+ interval_pl_time
53175321
CREATE OR REPLACE FUNCTION pg_catalog.interval_pl_time(interval, time without time zone)
53185322
RETURNS time without time zone

‎src/test/regress/sql/psql.sql

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1312,8 +1312,8 @@ rollback;
13121312
drop role regress_psql_user;
13131313

13141314
-- check \sf
1315-
\sfinformation_schema._pg_expandarray
1316-
\sf+information_schema._pg_expandarray
1315+
\sfinformation_schema._pg_index_position
1316+
\sf+information_schema._pg_index_position
13171317
\sf+ interval_pl_time
13181318
\sf ts_debug(text)
13191319
\sf+ ts_debug(text)

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp