PDF page 34
This section explains new features related to SQL statements. #### 3.2.1 ALTER DEFAULT PRIVILEGE {#pg18-3-2-1-alter-default-privilege} Default permissions can now be set for large objects. The defaclobjtype column in the pg_default_acl catalog will output the L indicating a large object. [[0d6c477](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=0d6c4776647feeee26f3e29fff6a5edb222fa260)] **Example 31.** Default privileges for LARGE OBJECT ```text postgres=> ALTER DEFAULT PRIVILEGES GRANT SELECT ON LARGE OBJECTS TO PUBLIC ; ALTER DEFAULT PRIVILEGES ``` #### 3.2.2 ALTER SUBSCRIPTION {#pg18-3-2-2-alter-subscription} The two_phase option can now be altered in the ALTER SUBSCRIPTION statement; the SUBSCRIPTION object must be disabled once in order to alter the two_phase option. [[1462aad](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=1462aad2e4474ab61174f8ab00992cd3d6d57c7b)] **Example 32.** Modify the two\_phase option ```text postgres=# SELECT subname, subtwophasestate FROM pg_subscription ; subname | subtwophasestate ---------+------------------ sub1 | e (1 row) postgres=# ALTER SUBSCRIPTION sub1 DISABLE ; ALTER SUBSCRIPTION postgres=# ALTER SUBSCRIPTION sub1 SET (two_phase=off) ; ALTER SUBSCRIPTION postgres=# SELECT subname, subtwophasestate FROM pg_subscription ; subname | subtwophasestate ---------+------------------ sub1 | d (1 row) ```PDF page 35
#### 3.2.3 ALTER TABLE {#pg18-3-2-3-alter-table} The following syntax has been added to the ALTER TABLE statement. ##### ALTER TABLE ALTER CONSTRAINT {#pg18-p35-alter-table-alter-constraint} The SET INHERIT clause (or SET NO INHERIT clause) can be specified in the ALTER CONSTRAINT clause. [[f4e53e1](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=f4e53e10b6ce0eedeb98caa4356facb47c7bb9cb)] **Example 33.** Specifying the SET INHERIT clause ```text postgres=> CREATE TABLE data1(col1 INT PRIMARY KEY, col2 VARCHAR(10) NOT NULL) ; CREATE TABLE postgres=> ALTER TABLE data1 ALTER CONSTRAINT data1_col2_not_null NO INHERIT ; ALTER TABLE ``` ##### ALTER TABLE NOT NULL NOT VALID {#pg18-p35-alter-table-not-null-not-valid} The NOT VALID clause can be specified for NOT NULL constraints in an ALTER TABLE statement. [[a379061](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=a379061a22a8fdf421e1a457cc6af8503def6252)] **Example 34.** Specifying the NOT VALID clause ```text postgres=> ALTER TABLE data1 ADD CONSTRAINT nn_data1_col2 NOT NULL col2 NOT VALID ; ALTER TABLE postgres=> \d+ data1 Table "public.data1" Column | Type | Collation | Nullable | Default | Storage … --------+-----------------------+-----------+----------+---------+--------- … col1 | integer | | not null | | plain … col2 | character varying(10) | | not null | | extende … Indexes: "data1_pkey" PRIMARY KEY, btree (col1) Not-null constraints: "data1_col1_not_null" NOT NULL "col1" "nn_data1_col2" NOT NULL "col2" NOT VALID Access method: heap ```PDF page 36
##### ALTER TABLE ONLY DROP CONSTRAINT {#pg18-p36-alter-table-only-drop-constraint} ALTER TABLE DROP CONSTRAINT statements with ONLY clause for partitioned tables can now be executed. In previous versions, this would have resulted in an error. [[4dea33c](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=4dea33ce765d65d8807d343ca6535a3d067a63da)] **Example 35.** Specifying the ONLY clause ```text postgres=> CREATE TABLE part1(c1 INT PRIMARY KEY, c2 INT CHECK(c2 > 0)) PARTITION BY RANGE(c1) ; CREATE TABLE postgres=> CREATE TABLE part1v1 PARTITION OF part1 FOR VALUES FROM (0) TO (1000000) ; CREATE TABLE postgres=> ALTER TABLE ONLY part1 DROP CONSTRAINT part1_c2_check ; ALTER TABLE ``` #### 3.2.4 ANALYZE {#pg18-3-2-4-analyze} The following new features have been implemented for the ANALYZE statement. ##### ONLY clause {#pg18-p36-only-clause} In previous versions, executing an ANALYZE statement on a partition table would recursively collect statistics for the partition. By specifying the ONLY clause, statistics can be collected only for partition tables. [[62ddf7e](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=62ddf7ee9a399e0b9624412fc482ed7365e38958)] **Example 36.** Specifying the ONLY clause ```text postgres=> ANALYZE VERBOSE ONLY part1 ; INFO: analyzing "public.part1" inheritance tree INFO: "part1v1": scanned 5406 of 5406 pages, containing 500000 live rows and 0 dead rows; 30000 rows in sample, 500000 estimated total rows INFO: finished analyzing table "postgres.public.part1" avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s buffer usage: 5445 hits, 0 reads, 0 dirtied WAL usage: 5 records, 0 full page images, 1195 bytes, 0 buffers full system usage: CPU: user: 0.04 s, system: 0.00 s, elapsed: 0.04 s ANALYZE ```PDF page 37
##### Output of I/O statistics and WAL statistics {#pg18-p37-output-of-i-o-statistics-and-wal-statistics} The output of the ANALYZE VERBOSE statement now includes I/O and WAL statistics. The I/O statistics output is similar to the logs for the parameter log_autovacuum_min_duration setting. [[4c1b4cd](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=4c1b4cdb86a7a3a97349bb636f34a259abe0187d), [bb77752](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=bb7775234273268e8852068ee1a2eff9cd2a0020)] **Example 37.** Output of the ANALYZE VERBOSE statement ```text postgres=> ANALYZE VERBOSE data1 ; INFO: analyzing "public.data1" INFO: "data1": scanned 5406 of 5406 pages, containing 333333 live rows and 666667 dead rows; 30000 rows in sample, 333333 estimated total rows INFO: finished analyzing table "postgres.public.data1" avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s buffer usage: 5421 hits, 0 reads, 0 dirtied WAL usage: 4 records, 0 full page images, 928 bytes, 0 buffers full system usage: CPU: user: 0.02 s, system: 0.00 s, elapsed: 0.02 s ANALYZE ``` ```text Lines beginning with “INFO:” are I/O statistics and lines beginning with “WAL usage:” are WAL statistics. ``` ##### Output of delay time {#pg18-p37-output-of-delay-time} If the parameter track_cost_delay_timing is set to 'on', the ANALYZE VERBOSE statement will now output "delay time". The same information is also output in the log of automatic VACUUM. [[7720082](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=7720082ae53280857f1eacb86134a9a759290f40)] **Example 38.** Output of the ANALYZE VERBOSE statement ```text postgres=> ANALYZE VERBOSE data1 ; INFO: analyzing "public.data1" INFO: "data1": scanned 5406 of 5406 pages, containing 500000 live rows and 0 dead rows; 30000 rows in sample, 500000 estimated total rows INFO: finished analyzing table "postgres.public.data1" delay time: 0.000 ms I/O timings: read: 25.770 ms, write: 0.000 ms … WAL usage: 4 records, 1 full page images, 8240 bytes, 0 buffers full system usage: CPU: user: 0.01 s, system: 0.03 s, elapsed: 0.05 s ANALYZE ```PDF page 38
##### Output of WAL buffer full {#pg18-p38-output-of-wal-buffer-full} The WAL buffer full count is now output when the ANALYZE VERBOSE statement is executed. [[6a8a7ce](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=6a8a7ce476bd5f042999a04a0d767b38ea6faec0)] **Example 39.** Output of the ANALYZE VERBOSE statement ```text postgres=> ANALYZE VERBOSE data1 ; INFO: analyzing "public.data1" INFO: "data1": scanned 5406 of 5406 pages, containing 500000 live rows and 0 dead rows; 30000 rows in sample, 500000 estimated total rows INFO: finished analyzing table "postgres.public.data1" avg read rate: 0.000 MB/s, avg write rate: 0.300 MB/s buffer usage: 5425 hits, 0 reads, 1 dirtied WAL usage: 3 records, 1 full page images, 3544 bytes, 0 buffers full system usage: CPU: user: 0.02 s, system: 0.00 s, elapsed: 0.02 s ``` #### 3.2.5 COPY {#pg18-3-2-5-copy} The following features have been added to the COPY statement ##### COPY TO statement from a materialized view {#pg18-p38-copy-to-statement-from-a-materialized-view} The COPY TO statement can now specify the materialized view. [[534874f](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=534874fac0b34535c9a5ab9257d6574f78423578)] **Example 40.** COPY TO statement for materialized view ```text postgres=> CREATE MATERIALIZED VIEW mview1 AS SELECT col1, col2 FROM data1 ; SELECT 1000000 postgres=> COPY mview1 TO '/tmp/data1.csv' CSV ; COPY 1000000 ``` ##### Addition of the reject_limit option {#pg18-p38-addition-of-the-reject-limit-option} The COPY FROM statement now has a reject_limit option. This option allows the maximum number of errors to be specified when errors are ignored (on_error=ignore). If the number of errors exceeds the specified value, the COPY statement will fail. [[4ac2a9b](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=4ac2a9beceb10d44806d2cf157d5a931bdade39e), [a39297e](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=a39297ec026c556b1bf2963dde766db31a2c3d2d)]PDF page 39
**Example 41.** Specifying the REJECT\_LIMIT option ```text postgres=> \! cat data1.csv 1,data1 2,data2 ABC,data3 4,data4 DEF,data5 postgres=> COPY data1 FROM '/home/postgres/data1.csv' WITH (FORMAT csv, ON_ERROR ignore, REJECT_LIMIT 1, LOG_VERBOSITY verbose) ; NOTICE: skipping row due to data type incompatibility at line 3 for column "col1": "ABC" NOTICE: skipping row due to data type incompatibility at line 5 for column "col1": "DEF" ERROR: skipped more than REJECT_LIMIT (1) rows due to data type incompatibility CONTEXT: COPY data1, line 5, column col1: "DEF" ``` ##### Added value for log_verbosity option {#pg18-p39-added-value-for-log-verbosity-option} The log_verbosity option can now be 'silent' in the COPY statement. Specifying this value suppresses log output. [[e7834a1](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e7834a1a251d4a28245377f383ff20a657ba8262) #### 3.2.6 CREATE FOREIGN TABLE {#pg18-3-2-6-create-foreign-table} The LIKE clause now allows copying column definitions from other tables (or foreign tables). [[302cf157](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=302cf15759233e654512979286ce1a5c3b36625f)] **Example 42.** Specifying the LIKE Clause for External Tables ```text postgres=> CREATE TABLE data1(col1 INT PRIMARY KEY, col2 VARCHAR(10)) ; CREATE TABLE postgres=> CREATE FOREIGN TABLE data2(LIKE data1 EXCLUDING ALL) SERVER remsvr1 ; CREATE FOREIGN TABLE ``` ```sql The values that can be specified in the INCLUDING / EXCLUDING clause are partially different from those in the CREATE TABLE statement. ```PDF page 40
**Table 17.** Values that can be specified in the INCLUDING \/ EXCLUDING clause | Clause | CREATE TABLE statement | CREATE FOREIGN TABLE statement | | --- | --- | --- | | COMMENTS | Yes | Yes | | COMPRESSION | Yes | No | | CONSTRAINTS | Yes | Yes | | DEFAULTS | Yes | Yes | | GENERATED | Yes | Yes | | IDENTITY | Yes | No | | INDEXES | Yes | No | | STATISTICS | Yes | Yes | | STORAGE | Yes | No | | ALL | Yes | Yes | #### 3.2.7 CREATE INDEX {#pg18-3-2-7-create-index} Creation of GIN indexes is now processed in parallel with B-Tree and BRIN indexes. [[8492feb](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=8492feb98f6df3f0f03e84ed56f0d1cbb2ac514c)] [8492feb](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=8492feb98f6df3f0f03e84ed56f0d1cbb2ac514c) #### 3.2.8 CREATE TABLE {#pg18-3-2-8-create-table} The following enhancements have been implemented for the CREATE TABLE statement ##### WITHOUT OVERLAPS clause {#pg18-p40-without-overlaps-clause} It is now possible to specify a WITHOUT OVERLAPS clause that does not allow duplicate ranges for primary key constraints (PRIMARY KEY), unique constraints (UNIQUE) and foreign key constraints (FOREIGN KEY). At least two columns are required when specifying this clause for a primary key constraint. It can also be specified when adding a primary key in an ALTER TABLE statement. The column CONPERIOD has been added to the pg_constraint catalog to represent the ```sql WITHOUT OVERLAPS clause. The ON {UPDATE, DELETE} clause is not yet supported. ``` [[fc0438b](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=fc0438b4e80535419a4e54dba87642cdf84defda), [89f908a](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=89f908a6d0ac1337c868625008c9598487d184e7)] #### Syntax 5 - PRIMARY KEY clause {#pg18-syntax-5} ```sql PRIMARY KEY (column_name1, …, column_nameN WITHOUT OVERLAPS) ```PDF page 41
**Example 43.** WITHOUT OVERLAPS clause in the primary key ```text postgres=> CREATE TABLE temporal_range1 ( id int4range, valid_at daterange, CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) ) ; CREATE TABLE postgres=> \d temporal_range1 Table "public.temporal_range1" Column | Type | Collation | Nullable | Default ----------+-----------+-----------+----------+--------- id | int4range | | not null | valid_at | daterange | | not null | Indexes: "temporal_rng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) ``` ##### ENFORCED clause {#pg18-p41-enforced-clause} Allows the ENFORCED or NOT ENFORCED clause to be specified for CHECK constraints and foreign key constraints. If nothing is specified or the ENFORCED clause is specified, PostgreSQL will behave as before, with CHECK constraints operating at the appropriate time. If the NOT ENFORCED clause is specified, the CHECK constraint will not work. The column CONFORCED has been added to the pg_constraint catalog to indicate this specification. [[ca87c41](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=ca87c415e2fccf81cec6fd45698dde9fae0ab570), [eec0040](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=eec0040c4bcd650993bb058ebdf61ab94171fda4)]PDF page 42
**Example 44.** CHECK constraints that do not operate ```text postgres=> CREATE TABLE data1(col1 INT PRIMARY KEY, col2 INT CHECK (col2 < 10) NOT ENFORCED) ; CREATE TABLE postgres=> INSERT INTO data1 VALUES (100, 100) ; INSERT 0 1 postgres=> SELECT * FROM data1 ; col1 | col2 -------+------- 100 | 100 (1 row) postgres=> SELECT conenforced FROM pg_constraint WHERE conname='data1_col2_check' ; conenforced ------------- f (1 row) ``` ##### NOT VALID clause {#pg18-p42-not-valid-clause} Allows the NOT VALID clause to be specified as a foreign key for partition tables. [[b663b94](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b663b9436e7509b5e73c8c372539f067cd6e66c1)] **Example 45.** NOT VALID clause for foreign keys in partition tables ```text postgres=> CREATE TABLE fkdata1(col1 INT PRIMARY KEY, col2 VARCHAR(10)) ; CREATE TABLE postgres=> CREATE TABLE part1(col1 INT PRIMARY KEY, col2 VARCHAR(10)) PARTITION BY RANGE(col1) ; CREATE TABLE postgres=> ALTER TABLE part1 ADD FOREIGN KEY (col1) REFERENCES fkdata1 NOT VALID ; ALTER TABLE ```PDF page 43
##### GENERATED ALWAYS VIRTUAL clause {#pg18-p43-generated-always-virtual-clause} Allows the creation of auto-generated columns that do not hold the data. Specify a GENERATED ALWAYS VIRTUAL clause in the column definition. Previously, only GENERATED ALWAYS STORED clauses that retained actual data were supported. The VIRTUAL clause can be confirmed by the output of 'v' in the attgenerated column of the pg_attribute column. Indexes cannot be created on generated columns with VIRTUAL clause specified. [[83ea6c5](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=83ea6c54025bea67bcd4949a6d58d3fc11c3e21b)] **Example 46.** GENERATED ALWAYS VIRTUAL clause ```text postgres=> CREATE TABLE data1(col1 INT PRIMARY KEY, col2 INT GENERATED ALWAYS AS (col1 * 2) VIRTUAL, col3 VARCHAR(10)) ; CREATE TABLE postgres=> \d data1 Table "public.data1" Column | Type | Collation | Nullable | Default --------+-----------------------+-----------+----------+-------------------… col1 | integer | | not null | col2 | integer | | | generated always as (col1 * 2) col3 | character varying(10) | | | Indexes: "data1_pkey" PRIMARY KEY, btree (col1) postgres=> INSERT INTO data1(col1, col3) VALUES (100, 'data1') ; INSERT 0 1 postgres=> SELECT * FROM data1 ; col1 | col2 | col3 -------+-------+------- 100 | 200 | data1 (1 row) ``` ```sql If the generated value overflows the column definition, it is not checked in the INSERT or UPDATE statement. The error occurs when the corresponding record is retrieved. CHECK constraints are checked when executing INSERT and UPDATE statements. ```PDF page 44
**Example 47.** Behavior in case of overflow ```text postgres=> CREATE TABLE overflow1(col1 VARCHAR(3), col2 VARCHAR(3) GENERATED ALWAYS AS (col1 || 'X')) ; CREATE TABLE postgres=> INSERT INTO overflow1(col1) VALUES ('ABC') ; INSERT 0 1 postgres=> SELECT * FROM overflow1 ; ERROR: value too long for type character varying(3) ``` ```text User-defined types and user-defined functions cannot be used for the virtual columns. [0cd69b3] ``` **Example 48.** Using user\-defined functions ```text postgres=> CREATE FUNCTION udf1(p1 INT) RETURNS INT AS $$ SELECT p1*5 $$ IMMUTABLE LANGUAGE SQL ; CREATE FUNCTION postgres=> CREATE TABLE data1(c1 INT PRIMARY KEY, c2 INT GENERATED ALWAYS AS (udf1(c1)) VIRTUAL) ; ERROR: generation expression uses user-defined function LINE 1: ...1(c1 INT PRIMARY KEY, c2 INT GENERATED ALWAYS AS (udf1(c1)) ... ^ DETAIL: Virtual generated columns that make use of user-defined functions are not yet supported. ``` #### 3.2.9 EXPLAIN {#pg18-3-2-9-explain} The following features have been added to the EXPLAIN statement. ##### Disabled Nodes Output {#pg18-p44-disabled-nodes-output} The output of the EXPLAIN statement now includes information on the number of disabled nodes (Disabled Nodes:). For example, disabling some execution plans by setting the parameter enable_seqscan to 'false', which was previously handled by increasing the cost, is now clearly recognized as a disabled node in PostgreSQL 18. [[e222534](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e22253467942fdb100087787c3e1e3a8620c54b2), [c01743a](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c01743aa4866e13da2c54e44010abc6d5f986363), [161320b](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=161320b4b960ee4fe918959be6529ae9b106ea5a)]PDF page 45
**Example 49.** Disabling Nodes \(PostgreSQL 18\) ```text postgres=> SET enable_seqscan = off ; SET postgres=> EXPLAIN SELECT * FROM data1 ; QUERY PLAN ---------------------------------------------------------------- Seq Scan on data1 (cost=0.00..15406.00 rows=1000000 width=10) Disabled: true (2 rows) ``` **Example 50.** Disabling Nodes \(PostgreSQL 17\) ```text postgres=> SET enable_seqscan = off ; SET postgres=> EXPLAIN SELECT * FROM data1 ; QUERY PLAN ------------------------------------------------------------------------------ Seq Scan on data1 (cost=10000000000.00..10000015406.00 rows=1000000 width=10) (1 row) ``` **Example 51.** src\/backend\/optimizer\/path\/costsize\.c（PostgreSQL 17） ```text cost_seqscan(Path *path, PlannerInfo *root, RelOptInfo *baserel, ParamPathInfo *param_info) { … if (!enable_seqscan) startup_cost += disable_cost; … ``` ##### Storage Clause Output {#pg18-p45-storage-clause-output} Temporary storage information used during Window Aggregation and CTE Scan runs is now output as a Storage clause. [[95d6e9a](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=95d6e9af07d2e5af2fdd272e72b5b552bad3ea0a), [40708ac](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=40708acd65c112149493d7159ca5c5a9391c9d35)]PDF page 46
**Example 52.** Windows Aggregation temporary storage information ```text postgres=> EXPLAIN (ANALYZE, COSTS OFF, BUFFERS OFF) SELECT COUNT(*) OVER() FROM data1 ; QUERY PLAN ----------------------------------------------------------------------------- WindowAgg (actual time=152.260..208.545 rows=1000000.00 loops=1) Window: w1 AS () Storage: Disk Maximum Storage: 9766kB -> Index Only Scan using data1_pkey on data1 (actual time=0.024..60.216 … Heap Fetches: 0 Index Searches: 1 Planning Time: 0.059 ms Execution Time: 224.929 ms (8 rows) ``` **Example 53.** CTE Scan Temporary Storage Information ```text postgres=> EXPLAIN (ANALYZE, COSTS OFF, BUFFERS OFF) WITH cte1 AS MATERIALIZED (SELECT col1, col2 FROM data1) SELECT * FROM cte1 WHERE col1 < 10000 ; QUERY PLAN ------------------------------------------------------------------------------ CTE Scan on cte1 (actual time=0.008..112.987 rows=9999.00 loops=1) Filter: (col1 < 10000) Rows Removed by Filter: 990001 Storage: Disk Maximum Storage: 19528kB CTE cte1 -> Seq Scan on data1 (actual time=0.005..30.641 rows=1000000.00 loop … Disabled: true … ``` ##### Parallel Bitmap Heap Scan Statistics {#pg18-p46-parallel-bitmap-heap-scan-statistics} Statistics for Parallel Bitmap Heap Scan execution are now output for each worker node. [[5a1e6df](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5a1e6df3b84c91957f80b19edb497a5eec83c403)]PDF page 47
**Example 54.** Parallel Bitmap Heap Scan Detailed Information ```text postgres=> EXPLAIN (COSTS OFF, ANALYZE, VERBOSE) SELECT AVG(col1) FROM data1 WHERE col1 BETWEEN 1000000 AND 2000000 ; QUERY PLAN ------------------------------------------------------------------------------ Finalize Aggregate (actual time=98.946..99.991 rows=1 loops=1) Output: avg(col1) Buffers: shared hit=8125 read=16 -> Gather (actual time=98.801..99.981 rows=3 loops=1) … -> Parallel Bitmap Heap Scan on public.data1 (actual time=46… Output: col1, col2 Recheck Cond: ((data1.col1 >= 1000000) AND (data1.col1 … Heap Blocks: exact=1598 Buffers: shared hit=8125 read=16 … ``` ##### Record Count Output Format {#pg18-p47-record-count-output-format} Record counts (actual rows) are now output to two decimal places. [[ddb17e3](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=ddb17e387aa28d61521227377b00f997756b8a27), [95dbd827](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=95dbd827f2edc4d10bebd7e840a0bd6782cf69b7)] **Example 55.** Loop processing line count output ```text postgres=> EXPLAIN (ANALYZE, VERBOSE) SELECT * FROM data1 WHERE col1 < 1000 ; QUERY PLAN ------------------------------------------------------------------------------ Index Scan using data1_pkey on public.data1 (cost=0.42..39.77 rows=1048 width=10) (actual time=0.008..0.084 rows=999.00 loops=1) Output: col1, col2 Index Cond: (data1.col1 < 1000) Buffers: shared hit=11 Planning: Buffers: shared hit=4 Planning Time: 0.096 ms Execution Time: 0.115 ms (8 rows) ```PDF page 48
##### Index Searches {#pg18-p48-index-searches} The “Index Searches” item indicating the number of index searches is now output. [[0fbceae](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=0fbceae841cb5a31b13d3f284ac8fdd19822eceb)] **Example 56.** Index Search Count ```text postgres=> EXPLAIN (ANALYZE, COSTS OFF) SELECT COUNT(*) FROM data1 WHERE col1 IN (100, 1000, 10000) ; QUERY PLAN ------------------------------------------------------------------------------ Aggregate (actual time=0.021..0.021 rows=1.00 loops=1) Buffers: shared hit=10 -> Index Only Scan using data1_pkey on data1 (actual time=0.015..0.019 … Index Cond: (col1 = ANY ('{100,1000,10000}'::integer[])) Heap Fetches: 0 Index Searches: 3 … ``` ##### Window Function Output {#pg18-p48-window-function-output} The “Window:” clause is now output in the execution plan when using Window functions. [[8b1b342](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=8b1b342544b69b281ffd3aafe594aec629ec4d3c)] **Example 57.** Window function output ```text postgres=> EXPLAIN SELECT AVG(col1) OVER (PARTITION BY col2) FROM data1 ; QUERY PLAN --------------------------------------------------------------------------- WindowAgg (cost=130488.33..146238.34 rows=1000000 width=36) Window: w1 AS (PARTITION BY col2) -> Sort (cost=128738.34..131238.34 rows=1000000 width=8) Sort Key: col2 -> Seq Scan on data1 (cost=0.00..15406.00 rows=1000000 width=8) (5 rows) ```PDF page 49
#### 3.2.10 INSERT / UPDATE / DELETE / MERGE {#pg18-3-2-10-insert-update-delete-merge} The RETURNING clause of the update DML statement can now specify an OLD clause for preupdate information and a NEW clause for post-update information. The pre-update data (OLD) in the INSERT statement and the post-update data (NEW) in the DELETE statement will be NULL. [[80feb72](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=80feb727c869cc0b2e12bd1543bafa449be9c8e2)] #### Syntax 6 - RETURNING Clause {#pg18-syntax-6} ```sql RETURNING OLD.column1, NEW.column2 RETURNING OLD.*, NEW.* RETURNING WITH (OLD AS alias1, NEW AS alias2) alias1.column1, … ``` **Example 58.** Output of old and new data in RETURNING clause ```text postgres=> UPDATE data1 SET col2='after' WHERE col1=100 RETURNING WITH (OLD AS o, NEW AS n) o.col1, o.col2, n.col2 ; col1 | col2 | col2 -------+----------+--------- 100 | before | after (1 row) postgres=> \pset null null Null display is "null". postgres=> DELETE FROM data1 WHERE col1=200 RETURNING OLD.*, NEW.* ; col1 | col2 | col1 | col2 -------+----------+--------+-------- 200 | before | null | null (1 row) ``` #### 3.2.11 VACUUM {#pg18-3-2-11-vacuum} The following new features have been added to the VACUUM statement. ##### ONLY clause {#pg18-p49-only-clause} ONLY clause can now be specified for partitioned tables. No VACUUM processing is performed. [[62ddf7e](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=62ddf7ee9a399e0b9624412fc482ed7365e38958)]PDF page 50
**Example 59.** Execute the VACUUM ONLY statement ```text postgres=> VACUUM VERBOSE ONLY part1 ; WARNING: VACUUM ONLY of partitioned table "part1" has no effect VACUUM ``` ##### VERBOSE / Log output {#pg18-p50-verbose-log-output} When 'on' is specified for the parameter track_cost_delay_timing, 'delay time' is now output in the ANALYZE VERBOSE statement. The same information is also output to the log of automatic VACUUM. [[7720082](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=7720082ae53280857f1eacb86134a9a759290f40)] **Example 60.** Execute the VACUUM VERBOSE statement ```text postgres=> VACUUM VERBOSE data1 ; INFO: vacuuming "postgres.public.data1" INFO: finished vacuuming "postgres.public.data1": index scans: 0 pages: 0 removed, 5406 remain, 1 scanned (0.02% of total), 0 eagerly scanned tuples: 0 removed, 500000 remain, 0 are dead but not yet removable removable cutoff: 786, which was 0 XIDs old when operation ended frozen: 0 pages from table (0.00% of total) had 0 tuples frozen visibility map: 0 pages set all-visible, 0 pages set all-frozen (0 were all- visible) index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed delay time: 0.000 ms I/O timings: read: 0.000 ms, write: 0.000 ms avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s buffer usage: 13 hits, 0 reads, 0 dirtied WAL usage: 0 records, 0 full page images, 0 bytes system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s VACUUM ``` ##### Output WAL buffer full {#pg18-p50-output-wal-buffer-full} The WAL buffer full count is now output when the VACUUM VERBOSE statement is executed. [[6a8a7ce](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=6a8a7ce476bd5f042999a04a0d767b38ea6faec0)]PDF page 51
**Example 61.** Execute the VACUUM VERBOSE statement ```text postgres=> VACUUM VERBOSE data1 ; INFO: vacuuming "postgres.public.data1" INFO: finished vacuuming "postgres.public.data1": index scans: 0 pages: 0 removed, 5406 remain, 1 scanned (0.02% of total), 0 eagerly scanned tuples: 0 removed, 500000 remain, 0 are dead but not yet removable removable cutoff: 763, which was 0 XIDs old when operation ended frozen: 0 pages from table (0.00% of total) had 0 tuples frozen visibility map: 0 pages set all-visible, 0 pages set all-frozen (0 were all- visible) index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s buffer usage: 13 hits, 0 reads, 0 dirtied WAL usage: 0 records, 0 full page images, 0 bytes, 0 buffers full system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s VACUUM ``` ##### Release of end-of-table free block {#pg18-p51-release-of-end-of-table-free-block} The parameter vacuum_truncate has been added. This parameter determines the behavior when the ```sql table attribute vacuum_truncate or the TRUNCATE option of the VACUUM statement is not set. ``` [[0164a0f](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=0164a0f9ee12e0eff9e4c661358a272ecd65c2d4)] **Example 62.** Setting the vacuum\_truncate parameter ```text postgres=> SET vacuum_truncate = off ; SET postgres=> VACUUM data1 ; VACUUM ``` #### 3.2.12 Data Types {#pg18-3-2-12-data-types} The following enhancements have been implemented for PostgreSQL 18 data types.PDF page 52
##### Bytea and integer type casts {#pg18-p52-bytea-and-integer-type-casts} Bytea and integer type (int2, int4, int8) data can now be cast to each other. [[6da469b](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=6da469badaffec32f8a804181cca279561467378)] **Example 63.** Bytea and integer type casts ```text postgres=> SELECT '\x1122334455667788'::bytea::int8 ; int8 --------------------- 1234605616436508552 (1 row) postgres=> SELECT 1234567890::int8::bytea ; bytea -------------------- \x00000000499602d2 (1 row) ``` #### 3.2.13 Functions {#pg18-3-2-13-functions} The following functions have been added/enhanced. ##### Array_reverse function {#pg18-p52-array-reverse-function} The function array_reverse has been added, which returns an array with the first dimension of the array inverted. [[49d6c7d](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=49d6c7d8dabad8c59ba21afe72ff32e79f822291)] #### Syntax 7 - Array_reverse function {#pg18-syntax-7} ```sql anyarray array_reverse(anyarray) ```PDF page 53
**Example 64.** Execution of the array\_reverse function ```text postgres=> SELECT array_reverse(ARRAY['AB', 'CD', 'EF']) ; -[ RECORD 1 ]-+----------- array_reverse | {EF,CD,AB} postgres=> SELECT array_reverse(ARRAY[[1,2],[3,4],[5,6]]) ; -[ RECORD 1 ]-+-------------------- array_reverse | {{5,6},{3,4},{1,2}} ``` ##### Casefold function {#pg18-p53-casefold-function} The casefold function is almost identical to the lower function, but it performs lower case conversion according to collation. Its main purpose is to facilitate case-insensitive string comparisons. It can only be used when the database encoding is UTF-8. Some characters may change the character length. If the locale PG_UNICODE_FAST is used, ß (U+00DF) will be converted to 'ss'. [[bfc5992](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=bfc5992069cf00b189af83d96a83ae5ebb65e938)] **Example 65.** If the database collation is PG\_C\_UTF8 ```text postgres=> SELECT casefold('Σ') ; casefold ---------- σ (1 row) ``` **Example 66.** If the database collation is PG\_UNICODE\_FAST ```text postgres=> SELECT casefold('ß') ; casefold ---------- ss (1 row) ``` ##### Crc32 function {#pg18-p53-crc32-function} The functions crc32 to calculate CRC-32 and crc32c to calculate CRC-32C have been added. [[760162f](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=760162fedb4f7ee6f0167cc6acfadee6ccb6be66)]PDF page 54
#### Syntax 8 - Crc32 / Crc32c functions {#pg18-syntax-8} ```sql bigint crc32(bytea) bigint crc32c(bytes) ``` **Example 67.** Execution of the crc32 and crc32c functions ```text postgres=> SELECT crc32('ABC'::bytea) ; crc32 ------------ 2743272264 (1 row) postgres=> SELECT crc32c('ABC'::bytea) ; crc32c ------------ 2285480319 (1 row) ``` ##### Extract function {#pg18-p54-extract-function} The WEEK clause can now be specified for INTERVAL types. [[6be39d7](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=6be39d77a70df52d5a0f2eb414ef9901ccf17e5a)] **Example 68.** Execution of the extract function ```text postgres=> SELECT EXTRACT(WEEK FROM INTERVAL '13 days 24 hours') ; extract --------- 1 ``` ##### Gamma / lgamma function {#pg18-p54-gamma-lgamma-function} The functions gamma and lgamma have been added to calculate gamma and log gamma values. [[a3b6dfd](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=a3b6dfd410699a19209be3cb3c7a74e5ff1da830)] #### Syntax 9 - Gamma / Lgamma functions {#pg18-syntax-9} ```sql double precision gamma(double precision) double precision lgamma(double precision) ```PDF page 55
**Example 69.** Execution of the gamma \/ lgamma function ```text postgres=> SELECT gamma(2.5), lgamma(3.5) ; gamma | lgamma -------------------+-------------------- 1.329340388179137 | 1.2009736023470743 (1 row) ``` ##### Has_largeobject_privilege function {#pg18-p55-has-largeobject-privilege-function} The has_largeobject_privilege function has been added to check for the existence of privileges on large objects. [[4eada20](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=4eada203a5a871f893afe3eb3e07eea5de1aa642)] #### Syntax 10 - Has_largeobject_privilege function {#pg18-syntax-10} ```sql boolean has_largeobject_privilege(name, oid, text) boolean has_largeobject_privilege(oid, oid, text) boolean has_largeobject_privilege(oid, text) ``` **Example 70.** Execution of the has\_largeobject\_privilege function ```text postgres=> SELECT lo_create(1001) ; -[ RECORD 1 ]--- lo_create | 1001 postgres=> SELECT has_largeobject_privilege(1001, 'SELECT') ; -[ RECORD 1 ]-------------+-- has_largeobject_privilege | t ``` ##### Json[b]_strip_nulls function {#pg18-p55-json-b-strip-nulls-function} The json[b]_strip_nulls function now has a parameter strip_in_arrays to remove NULL values from an array. The default value is FALSE. [[4603903](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=4603903d294bbdd644afecf9b5970827db6d1ff5)] #### Syntax 11 - Json_strip_nulls / Jsonb_strip_nulls functions {#pg18-syntax-11} ```sql json json_strip_nulls(target json, strip_in_arrays boolean DEFAULT false) jsonb jsonb_strip_nulls(target jsonb, strip_in_arrays boolean DEFAULT false) ```PDF page 56
**Example 71.** Execution of the json\_strip\_nulls function ```text postgres=> SELECT json_strip_nulls('[1,2,null,4]', true) ; -[ RECORD 1 ]----+-------- json_strip_nulls | [1,2,4] ``` ##### Max / min function {#pg18-p56-max-min-function} Max and min functions can now be used for values of type bytea. [[2d24fd9](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=2d24fd942c7010247c5b7b54b416acf7f4592a4e)] #### Syntax 12 - Max / min functions {#pg18-syntax-12} ```sql bytea max(bytea) bytea min(bytea) ``` **Example 72.** Execution of the max \/ min function for bytea type ```text postgres=> CREATE TABLE bytea1(col1 bytea) ; CREATE TABLE postgres=> INSERT INTO bytea1 VALUES (decode('abcdef', 'hex')), (decode('123456', 'hex')) ; INSERT 0 2 postgres=> SELECT max(col1), min(col1) FROM bytea1 ; max | min ----------+---------- \xabcdef | \x123456 (1 row) ``` ##### Reverse function {#pg18-p56-reverse-function} The version corresponding to bytea type has been added. [[0697b23](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=0697b23906adabccba41fa7bf19909eb98bdf0dd)] #### Syntax 13 - Reverse function {#pg18-syntax-13} ```sql bytea reverse(bytea) ```PDF page 57
**Example 73.** Execution of the reverse function ```text postgres=> SELECT reverse('\xabcdef12'::bytea) ; reverse ------------ \x12efcdab (1 row) ``` ##### Pg_get_acl function {#pg18-p57-pg-get-acl-function} The function pg_get_acl has been added to retrieve the ACL settings of a database object. [[4564f1c](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=4564f1cebd437d93590027c9ff46ef60bc3286ae), [d898665](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=d898665bf7591514158954038ac43cea3240beaa)] #### Syntax 14 - Pg_get_acl function {#pg18-syntax-14} ```sql aclitem[] pg_get_acl(classid oid, objid oid, objsubid integer) ``` **Example 74.** Execution of the pg\_get\_acl function ```text postgres=> CREATE TABLE data1(col1 INT PRIMARY KEY, col2 VARCHAR(10)) ; CREATE TABLE postgres=> GRANT SELECT, MAINTAIN ON data1 TO user1 ; GRANT postgres=> SELECT UNNEST( pg_get_acl('pg_class'::regclass, 'data1'::regclass::oid, 0)) ; unnest -------------------- demo=arwdDxtm/demo user1=rm/demo (2 rows) ``` ##### Pg_get_sequence_data function {#pg18-p57-pg-get-sequence-data-function} The function pg_get_sequence_data, which returns a tuple of sequences, has been added. This function returns a NULL value if a sequence with no SELECT privilege is specified. This function is intended primarily for use in optimizing pg_dump commands. [[c8b06bb](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c8b06bb969bf26c01f10b835e59d0aff39b7f516), [bd15b7d](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=bd15b7db489deadb2d9af7f21d16a6ed4a09465b), [a83a944](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=a83a944e9fdd573802c82d961126ba07bfb65f98)]PDF page 58
#### Syntax 15 - Pg_get_sequence_data function {#pg18-syntax-15} ```sql record pg_get_sequence_data(sequence_oid regclass) ``` **Example 75.** Execution of the pg\_get\_sequence\_data function ```text postgres=> CREATE SEQUENCE seq1 START WITH 100 ; CREATE SEQUENCE postgres=> SELECT NEXTVAL('seq1') ; nextval --------- 100 (1 row) postgres=> SELECT * FROM pg_get_sequence_data('seq1') ; last_value | is_called ------------+----------- 100 | t (1 row) ``` ##### Pg_ls_summariesdir function {#pg18-p58-pg-ls-summariesdir-function} The function pg_ls_summariesdir has been added to PostgreSQL 18. This function provides a list of WAL summary files. The function can be executed by a user with the SUPERUSER attribute or a user authorized by the pg_maintain role. If the WAL summarize feature is disabled, no records are returned. [[4e1fad3](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=4e1fad37872e49a711adad5d9870516e5c71a375)] #### Syntax 16 - Pg_ls_summariesdir function {#pg18-syntax-16} ```sql SETOF record pg_ls_summariesdir() ``` **Table 18.** Column name and description of the return value of the pg\_ls\_summariesdir function | Column name | Data type | Description | | --- | --- | --- | | name | text | WAL Summary File Name | | size | bigint | Bytes in file | | modification | timestamp with time zone | Last Modified |PDF page 59
**Example 76.** Execution of the pg\_ls\_summariesdir function ```text postgres=# SELECT * FROM pg_ls_summariesdir() ; name | size | modification ---------…--------------------------------+-------+------------------------ 00000001…100002800000000010B2FC8.summary | 4808 | 2025-09-26 15:42:38+09 00000001…10B2FC800000000015416F8.summary | 4994 | 2025-09-26 15:42:38+09 00000001…15416F800000000015417F8.summary | 56 | 2025-09-26 15:42:38+09 00000001…15417F800000000015481A0.summary | 230 | 2025-09-26 15:42:38+09 00000001…15481A000000000015482A0.summary | 56 | 2025-09-26 15:42:38+09 (5 rows) ``` ##### Pg_stat_get_backend_wal function {#pg18-p59-pg-stat-get-backend-wal-function} The function pg_stat_get_backend_wal has been added to retrieve WAL output information for a specified backend process. The result of executing this function is similar to the pg_stat_wal view, but on a per-backend-process basis. [[76def4c](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=76def4cdd7c2b32d19e950a160f834392ea51744)] #### Syntax 17 - Pg_stats_get_backend_wal function {#pg18-syntax-17} ```sql record pg_stat_get_backend_wal(backend_pid integer) ``` **Table 19.** Column name and description of the return value of the pg\_stat\_get\_backend\_wal function | Column name | Data type | Description | | --- | --- | --- | | wal_records | bigint | Number of WAL records | | wal_fpi | bigint | Number of WAL full page images | | wal_bytes | numeric | Number of WAL bytes | | wal_buffers_full | bigint | Number of storage outputs due to WAL buffer full | | stats_reset | timestamp with time zone | Data reset time | **Example 77.** Execution of the pg\_stat\_get\_backend\_wal function ```text postgres=> SELECT * FROM pg_stat_get_backend_wal(pg_backend_pid()) ; wal_records | wal_fpi | wal_bytes | wal_buffers_full | stats_reset -------------+---------+-----------+------------------+------------- 4005580 | 28 | 262315709 | 28954 | (1 row) ```PDF page 60
##### Regular Expression Function {#pg18-p60-regular-expression-function} Argument names have been added to the regular expression function (regexp*). [[580f872](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=580f8727ca93b7b9a2ce49746b9cdbcb0a2b4a7e)] **Example 78.** Argument name of the regexp\_count function ```text postgres=> \df regexp_count List of functions Schema | Name | Result data type | Argument data ------------+--------------+------------------+------------------------------- pg_catalog | regexp_count | integer | string text, pattern text … pg_catalog | regexp_count | integer | string text, pattern text, … pg_catalog | regexp_count | integer | string text, pattern text, … (3 rows) ``` ##### To_number function and to_char function {#pg18-p60-to-number-function-and-to-char-function} The to_number function and to_char function now accept an RN indicating a Roman numeral as the format specification character. The case of the input string and the format specification character is ignored. [[172e6b3](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=172e6b3adb2e2749883ad0746172e4cf97139961)] **Example 79.** Specify Roman numerals in the to\_number function ```text postgres=> SELECT to_number('MMXXV', 'rn'), to_char(1560, 'RN') ; to_number | to_char -----------+----------------- 2025 | MDLX (1 row) ``` ##### Uuidv7／uuidv4 functions {#pg18-p60-uuidv7-uuidv4-functions} Uuidv7 function supporting UUID v7 and uuidv4 function supporting UUID v4 are added. uuidv7 is expected to suppress randomization of index storage pages by specifying the timestamp of function execution in the first part of uuidv7. The uuidv7 function is expected to suppress randomization of index storage pages by specifying a timestamp at the beginning of the function. [[78c5e14](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=78c5e141e9c139fc2ff36a220334e4aa25e1b0eb)] #### Syntax 18 - UUIDv4 / UUIDv7 function {#pg18-syntax-18} ```sql uuid uuidv7([shift interval]) uuid uuidv4() ```PDF page 61
**Example 80.** Execution of the uuidv7 function and the uuidv4 function ```text postgres=> SELECT uuidv7() FROM generate_series(1, 5) ; uuidv7 -------------------------------------- 0193b90c-0818-772e-8fe2-ea74b2446cb2 0193b90c-0818-775d-b20b-4551f631cd81 0193b90c-0818-7761-a496-972341cb066c 0193b90c-0818-7763-b66a-511858d491d7 0193b90c-0818-7766-b1b9-7a5c2d05ee74 (5 rows) postgres=> SELECT uuidv4() ; uuidv4 -------------------------------------- 9aaf5cc9-893e-46da-9690-dda84405ffe8 (1 row) ``` #### 3.2.14 PL/pgSQL {#pg18-3-2-14-pl-pgsql} The operator “=>” can now be used to specify parameters for named cursors. Previously, the operator “:=” was used. [[246dedc](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=246dedc5d028800b393920184e5e6319c6805253)] **Example 81.** Named cursor parameter specification ```sql CREATE FUNCTION func1(INTEGER) RETURNS TEXT AS $$ DECLARE cur1 CURSOR (v INTEGER) FOR SELECT col2 FROM data1 WHERE col1=v; val1 TEXT; BEGIN OPEN cur1 (v => $1); FETCH cur1 INTO val1; RETURN val1; END; $$ LANGUAGE plpgsql; ```PDF page 62
#### 3.2.15 Optimizer {#pg18-3-2-15-optimizer} Faster execution plans are now selected. ##### ANY conversion of OR conditions {#pg18-p62-any-conversion-of-or-conditions} OR conditions for indexed columns are now automatically converted to ANY clauses. [[d4378c0](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=d4378c0005e61b1bb78e88097ea6efcdddbe2d6e), [ae45691](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=ae4569161a27823793ca24825bbabce2a91a0bc9)] **Example 82.** Execution Plan of PostgreSQL 17 ```text postgres=> EXPLAIN SELECT * FROM data1 WHERE col1=1000 OR col1=2000 ; QUERY PLAN ------------------------------------------------------------------------------ Bitmap Heap Scan on data1 (cost=8.87..16.78 rows=2 width=10) Recheck Cond: ((col1 = 1000) OR (col1 = 2000)) -> BitmapOr (cost=8.87..8.87 rows=2 width=0) -> Bitmap Index Scan on data1_pkey (cost=0.00..4.43 rows=1 width=0) Index Cond: (col1 = 1000) -> Bitmap Index Scan on data1_pkey (cost=0.00..4.43 rows=1 width=0) Index Cond: (col1 = 2000) (7 rows) ``` **Example 83.** Execution Plan of PostgreSQL 18 ```text postgres=> EXPLAIN SELECT * FROM data1 WHERE col1=1000 OR col1=2000 ; QUERY PLAN -------------------------------------------------------------------------- Index Scan using data1_pkey on data1 (cost=0.42..12.88 rows=2 width=10) Index Cond: (col1 = ANY ('{1000,2000}'::integer[])) (2 rows) ``` ##### ANY Conversion of Join OR Conditions {#pg18-p62-any-conversion-of-join-or-conditions} When an OR is used in a join condition, it can now be converted to an ANY clause. [[627d634](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=627d63419e22054551327216d2b2de3e6977fade)]PDF page 63
**Example 84.** Execution Plan of PostgreSQL 17 ```text postgres=> EXPLAIN (COSTS OFF) SELECT COUNT(*) FROM data1 AS d1, data2 AS d2 WHERE d1.col1 = d2.col1 OR d1.col1 = d2.col2 ; QUERY PLAN ------------------------------------------------------------------------ Finalize Aggregate -> Gather Workers Planned: 2 -> Partial Aggregate -> Nested Loop -> Parallel Seq Scan on data1 d1 -> Bitmap Heap Scan on data2 d2 Recheck Cond: ((d1.col1 = col1) OR (d1.col1 = col2)) -> BitmapOr -> Bitmap Index Scan on data2_pkey Index Cond: (col1 = d1.col1) -> Bitmap Index Scan on idx1_data2 Index Cond: (co;2 = d1.col1) (13 rows) ``` **Example 85.** Execution Plan of PostgreSQL 18 ```text postgres=> EXPLAIN (COSTS OFF) SELECT COUNT(*) FROM data1 AS d1, data2 AS d2 WHERE d1.col1 = d2.col1 OR d1.col2 = d2.col2 ; QUERY PLAN ------------------------------------------------------------------------ Finalize Aggregate -> Gather Workers Planned: 2 -> Partial Aggregate -> Nested Loop -> Parallel Seq Scan on data2 d2 -> Index Only Scan using data1_pkey on data1 d1 Index Cond: (col1 = ANY (ARRAY[d2.col1, d2.col2])) (8 rows) ```PDF page 64
##### IN (VALUES) Clause Converted to ANY Clause {#pg18-p64-in-values-clause-converted-to-any-clause} The WHERE clause with IN (VALUES) is converted to an ANY clause. [[c0962a1](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c0962a113d1f2f94cb7222a7ca025a67e9ce3860)] **Example 86.** Execution Plan of PostgreSQL 17 ```text postgres=> EXPLAIN (COSTS OFF) SELECT * FROM data1 WHERE col1 IN (VALUES(100), (200)) ; QUERY PLAN ----------------------------------------------- Nested Loop -> Unique -> Sort Sort Key: "*VALUES*".column1 -> Values Scan on "*VALUES*" -> Index Scan using data1_pkey on data1 Index Cond: (col1 = "*VALUES*".column1) (7 rows) ``` **Example 87.** Execution Plan of PostgreSQL 18 ```text postgres=> EXPLAIN (COSTS OFF) SELECT * FROM data1 WHERE col1 IN (VALUES(100), (200)) ; QUERY PLAN --------------------------------------------------- Index Scan using data1_pkey on data1 Index Cond: (col1 = ANY ('{100,200}'::integer[])) (2 rows) ``` ##### Right Semi Join {#pg18-p64-right-semi-join} Right Semi Join is now available as the execution plan. It may be adopted for hash joins or merge joins. [[aa86129e](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=aa86129e19d704afb93cb84ab9638f33d266ee9d)]PDF page 65
**Example 88.** Adoption of Right Semi Join ```text postgres=> EXPLAIN (COSTS OFF, VERBOSE) SELECT COUNT(*) FROM tenk1 a WHERE (unique1, two) IN (SELECT unique1, ROW_NUMBER() OVER() FROM tenk1 b) ; QUERY PLAN ------------------------------------------------------------------------------ Aggregate Output: count(*) -> Hash Right Semi Join Hash Cond: ((b.unique1 = a.unique1) AND ((row_number() OVER (?))… -> WindowAgg Output: b.unique1, row_number() OVER (?) -> Seq Scan on public.tenk1 b Output: b.unique1 -> Hash Output: a.unique1, a.two -> Seq Scan on public.tenk1 a Output: a.unique1, a.two (12 rows) ``` ##### UNIQUE Constraints and GROUP BY {#pg18-p65-unique-constraints-and-group-by} When a column specified by a NOT NULL constraint and a UNIQUE index is included in a GROUP BY clause, non-target columns are now excluded from GROUP BY. The following example shows a col1 column with UNIQUE and NOT NULL constraints specified. [[bd10ec5](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=bd10ec529796a13670645e6acd640c6f290df020)] **Example 89.** Execution Plan of PostgreSQL 17 ```text postgres=> EXPLAIN (COSTS OFF) SELECT col1, col2 FROM data1 GROUP BY col1, col2 ; QUERY PLAN ------------------------- HashAggregate Group Key: col1, col2 -> Seq Scan on data1 (3 rows) ```PDF page 66
**Example 90.** Execution Plan of PostgreSQL 18 ```text postgres=> EXPLAIN (COSTS OFF) SELECT col1, col2 FROM data1 GROUP BY col1, col2 ; QUERY PLAN ------------------------- HashAggregate Group Key: col1 -> Seq Scan on data1 (3 rows) ``` ##### Optimization of the DISTINCT clause {#pg18-p66-optimization-of-the-distinct-clause} Since the order of columns specified in the DISTINCT clause is not meaningful, optimization has been implemented to reorder them as needed. A new parameter, enable_distinct_reordering, has been added to change this behavior. The default value of this parameter is ‘on’, which causes the optimization to occur; to revert to PostgreSQL 17 behavior, set this parameter to ‘off’. In the example below, column col1 is modified in the execution plan because it is a primary key, and the processing of column col2 is disabled. [[a8ccf4e](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=a8ccf4e93a7eeaae66007bbf78cf9183ceb1b371)] **Example 91.** Execution Plan of PostgreSQL 17 ```text postgres=> EXPLAIN (COSTS OFF) SELECT DISTINCT col2, col1 FROM data1 ; QUERY PLAN ------------------------- HashAggregate Group Key: col2, col1 -> Seq Scan on data1 (3 rows) ```PDF page 67
**Example 92.** Execution Plan of PostgreSQL 18 ```text postgres=> EXPLAIN (COSTS OFF) SELECT DISTINCT col2, col1 FROM data1 ; QUERY PLAN -------------------------------------------------- Unique -> Incremental Sort Sort Key: col1, col2 Presorted Key: col1 -> Index Scan using data1_pkey on data1 (5 rows) ``` ##### Self-join optimization {#pg18-p67-self-join-optimization} The optimization of replacing self-join operations with unjoin scans when the execution result is unaffected has been implemented. This behavior is controlled by the parameter enable_self_join_elimination. The default value is ‘on’ and the optimization is executed. In the following example, column col1 of table data1 is the primary key. [[fc069a3](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=fc069a3a6319b5bf40d2f0f1efceae1c9b7a68a8)] **Example 93.** Execution Plan of PostgreSQL 17 ```text postgres=> EXPLAIN (COSTS OFF) SELECT * FROM data1 d1, data1 d2 WHERE d1.col1 = d2.col1 ; QUERY PLAN ---------------------------------- Hash Join Hash Cond: (d1.col1 = d2.col1) -> Seq Scan on data1 d1 -> Hash -> Seq Scan on data1 d2 (5 rows) ```PDF page 68
**Example 94.** Execution Plan of PostgreSQL 18 ```text postgres=> EXPLAIN (COSTS OFF) SELECT * FROM data1 d1, data1 d2 WHERE d1.col1 = d2.col1 ; QUERY PLAN ---------------------- Seq Scan on data1 d2 (1 row) ``` ##### Index Skip Scan {#pg18-p68-index-skip-scan} For an index consisting of multiple columns, if a match condition other than the first column is specified, the execution plan that uses the corresponding index is now created. In the following example, a composite index is created for columns col3 and col2, in that order. **Example 95.** Execution Plan of PostgreSQL 17 ```text postgres=> EXPLAIN SELECT * FROM data2 WHERE col2=100 ; QUERY PLAN ---------------------------------------------------------- Seq Scan on data2 (cost=0.00..17906.00 rows=1 width=14) Filter: (col2 = 100) (2 rows) ``` **Example 96.** Execution Plan of PostgreSQL 18 ```text postgres=> EXPLAIN SELECT * FROM data2 WHERE col2=100 ; QUERY PLAN -------------------------------------------------------------------------- Index Scan using idx2_data2 on data2 (cost=0.42..12.88 rows=1 width=14) Index Cond: (col2 = 100) (2 rows) ``` ##### Transformation of HAVING clauses into WHERE clauses {#pg18-p68-transformation-of-having-clauses-into-where-clauses} The optimization of transforming some HAVING clauses used in GROUPING SETS clauses into WHERE clauses has been implemented. [[67a54b9](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=67a54b9e83d331eadd3a595e6c3bfec06288d2c4)]PDF page 69
**Example 97.** Execution Plan of PostgreSQL 17 ```text postgres=> EXPLAIN (COSTS OFF) SELECT col1, col2, COUNT(col1) FROM data1 GROUP BY GROUPING SETS ((col1, col2), (col1)) HAVING col1 > 1000 AND col2 > 1000 ; QUERY PLAN --------------------------------------------- GroupAggregate Group Key: col1, col2 Group Key: col1 Filter: ((col1 > 1000) AND (col2 > 1000)) -> Sort Sort Key: col1, col2 -> Seq Scan on data1 (7 rows) ``` **Example 98.** Execution Plan of PostgreSQL 18 ```text postgres=> EXPLAIN (COSTS OFF) SELECT col1, col2, COUNT(col1) FROM data1 GROUP BY GROUPING SETS ((col1, col2), (col1)) HAVING col1 > 1000 AND col2 > 1000 ; QUERY PLAN ------------------------------------- GroupAggregate Group Key: col1, col2 Group Key: col1 Filter: (col2 > 1000) -> Sort Sort Key: col1, col2 -> Seq Scan on data1 Filter: (col1 > 1000) (8 rows) ```