4 SQL Statements

postgres=> ALTER DEFAULT PRIVILEGES GRANT SELECT ON LARGE OBJECTS TO PUBLIC ;
ALTER DEFAULT PRIVILEGES

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)

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

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

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

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

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

Lines beginning with “INFO:” are I/O statistics and lines beginning with “WAL usage:” are WAL
statistics.

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

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

postgres=> CREATE MATERIALIZED VIEW mview1 AS SELECT col1, col2 FROM data1 ;
SELECT 1000000
postgres=> COPY mview1 TO '/tmp/data1.csv' CSV ;
COPY 1000000

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"

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

The values that can be specified in the INCLUDING / EXCLUDING clause are partially different
from those in the CREATE TABLE statement.

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)

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)

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

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)

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.

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)

User-defined types and user-defined functions cannot be used for the virtual columns. [0cd69b3]

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.

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)

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)

cost_seqscan(Path *path, PlannerInfo *root,
RelOptInfo *baserel, ParamPathInfo *param_info)
{
…
        if (!enable_seqscan)
                startup_cost += disable_cost;
…

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)

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
…

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
…

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)

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
…

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)

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)

postgres=> VACUUM VERBOSE ONLY part1 ;
WARNING:  VACUUM ONLY of partitioned table "part1" has no effect
VACUUM

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

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

postgres=> SET vacuum_truncate = off ;
SET
postgres=> VACUUM data1 ;
VACUUM

postgres=> SELECT '\x1122334455667788'::bytea::int8 ;
        int8
---------------------
 1234605616436508552
(1 row)
postgres=> SELECT 1234567890::int8::bytea ;
       bytea
--------------------
 \x00000000499602d2
(1 row)

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

postgres=> SELECT casefold('Σ') ;
 casefold
----------
 σ
(1 row)

postgres=> SELECT casefold('ß') ;
 casefold
----------
 ss
(1 row)

postgres=> SELECT crc32('ABC'::bytea) ;
   crc32
------------
 2743272264
(1 row)
postgres=> SELECT crc32c('ABC'::bytea) ;
   crc32c
------------
 2285480319
(1 row)

postgres=> SELECT EXTRACT(WEEK FROM INTERVAL '13 days 24 hours') ;
 extract
---------
       1

postgres=> SELECT gamma(2.5), lgamma(3.5) ;
       gamma       |       lgamma
-------------------+--------------------
 1.329340388179137 | 1.2009736023470743
(1 row)

postgres=> SELECT lo_create(1001) ;
-[ RECORD 1 ]---
lo_create | 1001
postgres=> SELECT has_largeobject_privilege(1001, 'SELECT') ;
-[ RECORD 1 ]-------------+--
has_largeobject_privilege | t

postgres=> SELECT json_strip_nulls('[1,2,null,4]', true) ;
-[ RECORD 1 ]----+--------
json_strip_nulls | [1,2,4]

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)

postgres=> SELECT reverse('\xabcdef12'::bytea) ;
  reverse
------------
 \x12efcdab
(1 row)

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)

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)

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)

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)

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)

postgres=> SELECT to_number('MMXXV', 'rn'), to_char(1560, 'RN') ;
 to_number |     to_char
-----------+-----------------
      2025 |            MDLX
(1 row)

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)

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;

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)

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)

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)

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)

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)

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)

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)

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)

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)

postgres=> EXPLAIN (COSTS OFF) SELECT DISTINCT col2, col1 FROM data1 ;
       QUERY PLAN
-------------------------
 HashAggregate
   Group Key: col2, col1
   ->  Seq Scan on data1
(3 rows)

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)

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)

postgres=> EXPLAIN (COSTS OFF) SELECT * FROM data1 d1, data1 d2 WHERE
d1.col1 = d2.col1 ;
      QUERY PLAN
----------------------
 Seq Scan on data1 d2
(1 row)

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)

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)

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)

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)