4 SQL Statements

postgres=# CREATE PUBLICATION pub1 FOR ALL TABLES ;
CREATE PUBLICATION
postgres=# ALTER PUBLICATION pub1 SET ALL TABLES EXCEPT (TABLE data2) ;
ALTER PUBLICATION
postgres=# CREATE PUBLICATION pub2 ;
CREATE PUBLICATION
postgres=# ALTER PUBLICATION pub2 SET ALL SEQUENCES ;
ALTER PUBLICATION

postgres=# ALTER SUBSCRIPTION sub1 REFRESH SEQUENCES ;
ALTER SUBSCRIPTION

postgres=> CREATE TABLE data1(col1 INT, col2 INT GENERATED ALWAYS AS
(col1 * 100) CHECK (col2 < 10000)) ;
CREATE TABLE
postgres=> ALTER TABLE data1 ALTER COLUMN col2 SET EXPRESSION AS
(col1 * 200) ;
ALTER TABLE

postgres=> CREATE TABLE data1(col1 INT PRIMARY KEY, col2 INT) ;
CREATE TABLE
postgres=> ALTER TABLE data1 ADD CONSTRAINT data1_col2_check1
CHECK(col2 < 1000) ;
ALTER TABLE
postgres=> INSERT INTO data1 SELECT g1, g1 FROM generate_series(1, 999) g1 ;
INSERT 0 999
postgres=> ALTER TABLE data1 ALTER CONSTRAINT data1_col2_check1 NOT ENFORCED ;
ALTER TABLE
postgres=> INSERT INTO data1 VALUES (1000, 1000) ;
INSERT 0 1
postgres=> ALTER TABLE data1 ALTER CONSTRAINT data1_col2_check1 ENFORCED ;
ERROR:  check constraint "data1_col2_check1" of relation "data1" is violated by
some row
postgres=> DELETE FROM data1 WHERE col2=1000 ;
DELETE 1
postgres=> ALTER TABLE data1 ALTER CONSTRAINT data1_col2_check1 ENFORCED ;
ALTER TABLE

postgres=> ALTER TABLE data1 SET (autovacuum_parallel_workers = 4) ;
ALTER TABLE

postgres=# CHECKPOINT (MODE FAST) ;
CHECKPOINT
postgres=# CHECKPOINT (MODE SPREAD) ;
CHECKPOINT

Depending on the options specified, the output log will change as follows.

postgres=# CHECKPOINT (FLUSH_UNLOGGED) ;
CHECKPOINT

Depending on the options specified, the output log will change as follows.

postgres=# \d data1
               Table "public.data1"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 col1   | integer |           |          |
 col2   | integer |           |          |
postgres=# COPY data1 FROM stdin WITH (format 'csv', on_error 'set_null') ;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> 100,100
>> 200,bad_data
>> 300,300
>> \.
NOTICE:  in 1 row, columns were set to null due to data type incompatibility
COPY 3
postgres=# SELECT * FROM data1 ;
 col1 | col2
------+------
  100 |  100
  200 | null
  300 |  300
(3 rows)

postgres=# \d part1
                Partitioned table "public.part1"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 col    | integer               |           | not null |
 col2   | character varying(10) |           |          |
Partition key: RANGE (c1)
Indexes:
    "part1_pkey" PRIMARY KEY, btree (col1)
Number of partitions: 2 (Use \d+ to list them.)
postgres=# COPY part1 TO '/tmp/part1.csv' CSV ;
COPY 2000000

postgres=# COPY data1 FROM stdin WITH (FORMAT CSV, HEADER 2) ;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> HEADER1
>> HEADER2
>> 100,data1
>> 200,data2
>> \.
COPY 2

postgres=# COPY data1 TO stdout WITH (FORMAT json) ;
{"col1":1,"col2":"data1"}
{"col1":2,"col2":"data2"}
{"col1":3,"col2":"data3"}
{"col1":4,"col2":"data4"}
{"col1":5,"col2":"data5"}
postgres=# COPY data1 TO stdout WITH (FORMAT json, FORCE_ARRAY) ;
[
 {"col1":1,"col2":"data1"}
,{"col1":2,"col2":"data2"}
,{"col1":3,"col2":"data3"}
,{"col1":4,"col2":"data4"}
,{"col1":5,"col2":"data5"}
]

postgres=# CREATE PUBLICATION pub1 FOR ALL SEQUENCES ;
CREATE PUBLICATION
postgres=# \dRp
                                                 List of publications
 Name |  Owner   | All tables | All sequences | Inserts | Updates | Deletes…
------+----------+------------+---------------+---------+---------+--------…
 pub1 | postgres | f          | t             | t       | t       | t      …
(1 row)

No tables can be added to a publication that specifies the ALL SEQUENCES clause.

postgres=# ALTER PUBLICATION pub1 ADD TABLE data1 ;
ERROR:  publication "pub1" is defined as FOR ALL SEQUENCES
DETAIL:  Tables or sequences cannot be added to or dropped from FOR ALL SEQUENCES
publications.

postgres=# CREATE SCHEMA schema1
             CREATE TABLE data1(col1 INT PRIMARY KEY, col2 VARCHAR(10))
  CREATE COLLATION col1 (LOCALE="C")
            CREATE DOMAIN d1 AS INT ;
CREATE SCHEMA

The following CREATE clauses can be specified:

 postgres=> CREATE TABLE data1(col1 INT, col2 INT GENERATED ALWAYS AS
(col1 + 100) VIRTUAL) ;
CREATE TABLE
postgres=> CREATE STATISTICS data1_stat1 ON (col2) FROM data1 ;
CREATE STATISTICS

postgres=> SELECT * FROM range1 ORDER BY col1 ;
          col1           | col2
-------------------------+------
 [2026-04-01,2026-05-01) |  100
 [2026-05-01,2026-06-01) |  200
(2 rows)

If the FOR PORTION OF clause is set to "2026/04/10-2026/04/20", the tuples to be updated are
selected (tuples where col2=100). These tuples are split into the following three tuples.
• 2026/04/01-2026/04/10 (The first half of the original tuple range)
• 2026/04/10-2026/04/20 (Tuple to be updated; updated using a SET clause)
• 2026/04/20-2026/05/01 (The second half of the original tuple range)

postgres=> UPDATE range1 FOR PORTION OF col1 FROM '2026-04-10' TO '2026-04-20'
SET col2=300 ;
UPDATE 1
postgres=> SELECT * FROM range1 ORDER BY col1 ;
          col1           | col2
-------------------------+------
 [2026-04-01,2026-04-10) |  100
 [2026-04-10,2026-04-20) |  300
 [2026-04-20,2026-05-01) |  100
 [2026-05-01,2026-06-01) |  200
(4 rows)

postgres=> EXPLAIN (ANALYZE, IO, COSTS OFF) SELECT COUNT(*) FROM data1 ;
                                  QUERY PLAN
------------------------------------------------------------------------------
 Aggregate (actual time=425.023..425.024 rows=1.00 loops=1)
   Buffers: shared hit=1099 read=52956
   ->  Seq Scan on data1 (actual time=0.416..235.808 rows=10000000.00 loops=1)
         Prefetch: avg=70.84 max=91 capacity=94
         I/O: count=3314 waits=7 size=15.98 in-progress=4.54
         Buffers: shared hit=1099 read=52956
 Planning Time: 0.059 ms
 Execution Time: 425.056 ms
(8 rows)

postgres=> EXPLAIN SELECT * FROM data1 AS d1 INNER JOIN data1 AS d2
ON d1.col1 = d2.col2::numeric AND d1.col2::numeric = d2.col1 ;
                                         QUERY PLAN
-----------------------------------------------------------------------------
 Nested Loop  (cost=0.15..10.49 rows=1 width=36)
   ->  Seq Scan on data1 d1  (cost=0.00..1.40 rows=40 width=18)
   ->  Memoize  (cost=0.15..0.41 rows=1 width=18)
         Cache Key: d1.col1, (d1.col2)::numeric
         Cache Mode: logical
         Estimates: capacity=20 distinct keys=20 lookups=40 hit percent=50.00%
         ->  Index Only Scan using idx1_data1 on data1 d2  (cost=0.14..0.40…
               Index Cond: (col1 = (d1.col2)::numeric)
               Filter: (d1.col1 = (col2)::numeric)
(9 rows)

postgres=> EXPLAIN (ANALYZE, WAL) UPDATE data1 SET col2='update'
WHERE col1 < 100 ;
                                   QUERY PLAN
------------------------------------------------------------------------------
 Update on data1  (cost=0.00..1.50 rows=0 width=0) (actual time=0.225..0.225…
   Buffers: shared hit=121 read=1 dirtied=3
   WAL: records=80 fpi=2 bytes=9790 fpi bytes=3656
   ->  Seq Scan on data1  (cost=0.00..1.50 rows=40 width=38) (actual time=0.…
         Filter: (col1 < '100'::numeric)
         Buffers: shared hit=1
 Planning Time: 0.281 ms
 Execution Time: 0.363 ms
(8 rows)

postgres=> EXPLAIN (COSTS OFF) SELECT COUNT(*) FROM data1 WHERE col1 =
(SELECT MAX(col1) FROM data1) ;
                                    QUERY PLAN
------------------------------------------------------------------------------
 Aggregate
   InitPlan expr_1
     ->  Result
           Replaces: MinMaxAggregate
           InitPlan minmax_1
             ->  Limit
                   ->  Index Only Scan Backward using data1_pkey on data1
data1_1
   ->  Index Only Scan using data1_pkey on data1
         Index Cond: (col1 = (InitPlan expr_1).col1)
(9 rows)

postgres=>  EXPLAIN (COSTS OFF) SELECT COUNT(*) FROM data1 WHERE col1 =
(SELECT MAX(col1) FROM data1) ;
                                    QUERY PLAN
------------------------------------------------------------------------------
 Aggregate
   InitPlan 2
     ->  Result
           InitPlan 1
             ->  Limit
                   ->  Index Only Scan Backward using data1_pkey on data1
data1_1
   ->  Index Only Scan using data1_pkey on data1
         Index Cond: (col1 = (InitPlan 2).col1)
(8 rows)

postgres=> CREATE TABLE data1(col1 INT PRIMARY KEY, col2 VARCHAR(10)) ;
CREATE TABLE
postgres=> GRANT SELECT ON data1 TO demo2 WITH GRANT OPTION ;
GRANT
postgres=> \connect postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# GRANT SELECT ON data1 TO demo3 GRANTED BY demo2 ;
GRANT
postgres=# \dp+
                             Access privileges
 Schema | Name  | Type  | Access privileges  | Column privileges | Policies
--------+-------+-------+--------------------+-------------------+----------
 public | data1 | table | demo=arwdDxtm/demo+|                   |
        |       |       | demo2=r*/demo     +|                   |
        |       |       | demo3=r/demo2      |                   |
(1 row)

postgres=> INSERT INTO data1 VALUES (100, 'coflict100') ON CONFLICT (col1)
DO SELECT FOR UPDATE RETURNING * ;
col1 |    col2
------+------------
  100 | coflict100
(1 row)
INSERT 0 1

postgres=> REPACK (ANALYZE, VERBOSE) data1 ;
INFO:  repacking "public.data1" in physical order
INFO:  "public.data1": found 1666667 removable, 3333333 nonremovable row
versions in 27028 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU: user: 0.58 s, system: 0.20 s, elapsed: 0.94 s.
INFO:  analyzing "public.data1"
INFO:  "data1": scanned 18019 of 18019 pages, containing 3333333 live rows and
0 dead rows; 30000 rows in sample, 3333333 estimated total rows
INFO:  finished analyzing table "postgres.public.data1"
avg read rate: 1582.075 MB/s, avg write rate: 0.088 MB/s
buffer usage: 23 hits, 18023 reads, 1 dirtied
WAL usage: 3 records, 1 full page images, 2165 bytes, 1560 full page image
bytes, 0 buffers full
system usage: CPU: user: 0.06 s, system: 0.01 s, elapsed: 0.08 s
REPACK

If the CONCURRENTY option is specified in the REPACK statement, the SHARE UPDATE
EXCLUSIVE lock is used on the table. Update transactions during the REPACK statement execution
generate update changes via the logical decoding feature. These update changes are ultimately applied
to the new table. During the execution of a REPACK statement, a temporary logical replication slot
named “repack_{ID}” is automatically generated internally. The number of replication slots reserved
for REPACK is determined by the max_repack_replication_slots parameter.

postgres=> SELECT col1, col2 FROM data1 GROUP BY ALL ORDER BY 1 ;
 col1 | col2
------+-------
    1 | data1
    2 | data2
    3 | data3
    4 | data4
(4 rows)

postgres=> SELECT (SELECT col1),
 (SELECT col2),
 (SELECT GROUPING(col1, col2)),
 (SELECT SUM(t1.col3)),
 (SELECT max(t2.col3))
FROM
gstest1 t1 FULL JOIN gstest2 t2 USING (col1, col2)
GROUP BY GROUPING SETS ((col1, col2), ()) ;
 col1 | col2 | grouping | sum | max
------+------+----------+-----+-----
      |      |        3 | 271 |   2
    1 |    1 |        0 | 147 |   2
    1 |    3 |        0 |  14 |
    3 |    4 |        0 |  17 |
    3 |    3 |        0 |  16 |
    1 |    2 |        0 |  25 |   2
    2 |    2 |        0 |     |   2
    4 |    1 |        0 |  37 |
    2 |    3 |        0 |  15 |
(9 rows)

postgres=> SHOW search_path ;
   search_path
-----------------
 "$user", public
(1 row)
postgres=> SET search_path TO NULL ;
SET
postgres=> SHOW search_path ;
 search_path
-------------
(1 row)

postgres=> VACUUM (VERBOSE) data1 ;
INFO:  vacuuming "postgres.public.data1"
…
WAL usage: 185293 records, 92311 full page images, 613464473 bytes, 576198816
full page image bytes, 1416
memory usage: dead item storage 2.58 MB accumulated across 1 reset (limit 64.00
MB each)
system usage: CPU: user: 0.33 s, system: 0.95 s, elapsed: 32.05 s
VACUUM

postgres=> VACUUM (PARALLEL 2, VERBOSE) data1 ;
INFO:  vacuuming "postgres.public.data1"
INFO:  launched 1 parallel vacuum worker for index vacuuming (planned: 2)
INFO:  finished vacuuming "postgres.public.data1": index scans: 1
…
item identifiers removed
parallel workers: index vacuum: 1 planned, 1 launched in total
index "data1_pkey": pages: 27421 in total, 0 newly deleted, 0 currently deleted,
0 reusable
…
system usage: CPU: user: 0.11 s, system: 1.35 s, elapsed: 5.66 s
VACUUM

postgres=> WAIT FOR LSN '0/0301EF70' ;
 status
---------
 success
(1 row)
postgres=> WAIT FOR LSN '0/0301FF00' WITH (TIMEOUT '10s') ;
ERROR:  timed out while waiting for target LSN 0/0301FF00 to be replayed;
current standby_replay LSN 0/0301EF70
postgres=> WAIT FOR LSN '0/0301FF00' WITH (TIMEOUT '10s', NO_THROW) ;
 status
---------
 timeout
(1 row)

The WAIT FOR LSN statement returns the following value.

postgres=> SELECT LEAD(col1) IGNORE NULLS OVER (), LAG(col1) RESPECT NULLS OVER
() FROM data1 ;
 lead  |  lag
-------+-------
     2 |
     3 |     1
     4 |     2
     5 |     3
     6 |     4
…

postgres=> SELECT encode('\x69b73eff', 'base64url'),
decode('abc-_w', 'base64url') ;
 encode |   decode
--------+------------
 abc-_w | \x69b73eff
(1 row)
postgres=> SELECT encode('\x1122334455', 'base32hex'),
decode('24H36H2L', 'base32hex') ;
  encode  |    decode
----------+--------------
 24H36H2L | \x1122334455
(1 row)

postgres=> SELECT error_on_null(NULL::INT) ;
ERROR:  null value not allowed

postgres=> SELECT random('2024-09-09'::date, '2025-09-09'::date),
random('2024-09-09'::timestamptz, '2025-09-09'::timestamptz) ;
   random   |            random
------------+-------------------------------
 2025-02-04 | 2025-02-16 07:24:48.518771+09
(1 row)

postgres=> SELECT range_minus_multi(numrange(1.0, 3.0), numrange(1.5, 2.0)) ;
 range_minus_multi
-------------------
 [1.0,1.5)
 [2.0,3.0)
(2 rows)
postgres=> SELECT multirange_minus_multi(nummultirange(numrange(1,4)),
 nummultirange(numrange(2,3))) ;
 multirange_minus_multi
------------------------
 {[1,2),[3,4)}
(1 row)

postgres=> SELECT ctid, tid_block(ctid), tid_offset(ctid), col1 FROM data1 ;
 ctid  | tid_block | tid_offset | col1
-------+-----------+------------+------
 (0,1) |         0 |          1 |  100
 (0,2) |         0 |          2 |  200
(2 rows)

postgres=> SELECT width_bucket('NaN', 3.0, 4.0, 888) ;
 width_bucket
--------------
          889
(1 row)

postgres=> SELECT pg_get_database_ddl('postgres', 'pretty', 'true') ;
            pg_get_database_ddl
--------------------------------------------
 CREATE DATABASE postgres                  +
     WITH TEMPLATE = template0             +
     ENCODING = 'UTF8'                     +
     LOCALE_PROVIDER = libc                +
     LOCALE = 'C';
 ALTER DATABASE postgres OWNER TO postgres;
(2 rows)

postgres=> CREATE STATISTICS data1_stat1 ON col1,col2 FROM data1 ;
CREATE STATISTICS
postgres=> SELECT pg_clear_extended_stats('public', 'data1', 'public',
'data1_stat1', true) ;
-[ RECORD 1 ]-----------+-
pg_clear_extended_stats |

postgres=# SELECT * FROM pg_get_multixact_stats() ;
 num_mxids | num_members | members_size | oldest_multixact
-----------+-------------+--------------+------------------
         1 |           2 |            0 |                1
(1 row)

postgres=> SELECT * FROM pg_get_sequence_data('seq01') ;
 last_value | is_called |  page_lsn
------------+-----------+------------
          4 | t         | 3/0701C0A0
(1 row)

postgres=> SELECT * FROM
pg_get_publication_tables('{pub1}', 'data1'::regclass::oid) ;
 pubid | relid | attrs | qual
-------+-------+-------+------
 16405 | 16399 | 1 2   |
(1 row)

postgres=> \dfS pg_replication_origin_session_setup
List of functions
-[ RECORD 1 ]-------+--------------------------------------
Schema              | pg_catalog
Name                | pg_replication_origin_session_setup
Result data type    | void
Argument data types | node_name text, pid integer DEFAULT 0
Type                | func

postgres=> CREATE STATISTICS stat1_data1 ON col1,col2 FROM data1 ;
CREATE STATISTICS
postgres=> SELECT pg_restore_extended_stats(
  'schemaname', 'public',
  'relname', 'data1',
  'statistics_schemaname', 'public',
  'statistics_name', 'stat1_data1',
  'inherited', false,
  'n_distinct', '[{"attributes" : [1,2], "ndistinct" : 4}]'::pg_ndistinct) ;
-[ RECORD 1 ]-------------+--
pg_restore_extended_stats | t

postgres=> SELECT jsonb_path_query('"xTRIMx"'::jsonb, '$.btrim("x")') ;
-[ RECORD 1 ]----+-------
jsonb_path_query | "TRIM"
postgres=> SELECT jsonb_path_query('"LOWER"'::jsonb, '$.lower()') ;
-[ RECORD 1 ]----+--------
jsonb_path_query | "lower"
postgres=> SELECT jsonb_path_query('"upper"'::jsonb, '$.upper()') ;
-[ RECORD 1 ]----+--------
jsonb_path_query | "UPPER"
postgres=> SELECT jsonb_path_query('"iNITCaP"'::jsonb, '$.initcap()') ;
-[ RECORD 1 ]----+----------
jsonb_path_query | "Initcap"
postgres=> SELECT jsonb_path_query('"REpLACE"'::jsonb, '$.replace("p","P")') ;
-[ RECORD 1 ]----+----------
jsonb_path_query | "REPLACE"
postgres=> SELECT jsonb_path_query('"test@data"'::jsonb,
'$.split_part("@",2)') ;
-[ RECORD 1 ]----+-------
jsonb_path_query | "data"

postgres=> CREATE DOMAIN jd1 AS jsonb CHECK ((VALUE ->'val')::text <> '3') ;
CREATE DOMAIN
postgres=> SELECT '{"val": 1}'::jd1 IS JSON ;
 ?column?
----------
 t
(1 row)
postgres=> SELECT '{"val": 3}'::jd1 IS JSON ;
ERROR:  value for domain jd1 violates check constraint "jd1_check"

postgres=> SELECT * FROM pg_shdepend WHERE
dbid = current_database()::regdatabase ;
 dbid | classid | objid | objsubid | refclassid | refobjid | deptype
------+---------+-------+----------+------------+----------+---------
    5 |    2615 |  2200 |        0 |       1260 |    16384 | a
(1 row)

postgres=> SELECT 9999999999::oid8 ;
    oid8
------------
 9999999999
(1 row)

postgres=> SELECT '\x019a2f859ced7225b99d9c55044a2563'::bytea::uuid ;
                 uuid
--------------------------------------
 019a2f85-9ced-7225-b99d-9c55044a2563
(1 row)
postgres=> SELECT '5b35380a-7143-4912-9b55-f322699c6770'::uuid::bytea ;
               bytea
------------------------------------
 \x5b35380a714349129b55f322699c6770
(1 row)

postgres=> CREATE TABLE data1(col1 INET, col2 CIDR) ;
CREATE TABLE
postgres=> CREATE INDEX idx1_data1 ON data1 USING gist (col1) ;
CREATE INDEX
postgres=> CREATE INDEX idx2_data1 ON data1 USING gist (col2) ;
CREATE INDEX

postgres=> DO $$
DECLARE
   EXECUTE INT;
BEGIN
   EXECUTE := 100;
END;
$$;
DO
postgres=>

postgres=> EXPLAIN (VERBOSE, COSTS OFF) SELECT t1.col1, avg(t2.col3)
FROM eager1 AS t1 JOIN eager2 AS t2 ON t1.col2 = t2.col2
GROUP BY t1.col1 ORDER BY t1.col1 ;
                      QUERY PLAN
-------------------------------------------------------
 GroupAggregate
   Output: t1.col1, avg(t2.col3)
   Group Key: t1.col1
   ->  Sort
         Output: t1.col1, t2.col3
         Sort Key: t1.col1
         ->  Hash Join
               Output: t1.col1, t2.col3
               Hash Cond: (t2.col2 = t1.col2)
               ->  Seq Scan on public.eager2 t2
                     Output: t2.col1, t2.col2, t2.col3
               ->  Hash
                     Output: t1.col1, t1.col2
                     ->  Seq Scan on public.eager1 t1
                           Output: t1.col1, t1.col2
(15 rows)

postgres=> EXPLAIN (VERBOSE, COSTS OFF) SELECT t1.col1, avg(t2.col3)
FROM eager1 AS t1 JOIN eager2 AS t2 ON t1.col2 = t2.col2
GROUP BY t1.col1 ORDER BY t1.col1 ;
                            QUERY PLAN
-------------------------------------------------------------------
 Finalize GroupAggregate
   Output: t1.col1, avg(t2.col3)
   Group Key: t1.col1
   ->  Sort
         Output: t1.col1, (PARTIAL avg(t2.col3))
         Sort Key: t1.col1
         ->  Hash Join
               Output: t1.col1, (PARTIAL avg(t2.col3))
               Hash Cond: (t1.col2 = t2.col2)
               ->  Seq Scan on public.eager1 t1
                     Output: t1.col1, t1.col2, t1.col3
               ->  Hash
                     Output: t2.col2, (PARTIAL avg(t2.col3))
                     ->  Partial HashAggregate
                           Output: t2.col2, PARTIAL avg(t2.col3)
                           Group Key: t2.col2
                           ->  Seq Scan on public.eager2 t2
                                 Output: t2.col1, t2.col2, t2.col3
(18 rows)

postgres=> EXPLAIN (COSTS OFF, BUFFERS OFF)
SELECT COUNT(*) FROM data1 d1 LEFT JOIN
LATERAL (SELECT DISTINCT ON (c1) c1, c2, d1.c1 AS x FROM data1 d2) d2
ON d1.c1 + 1 = d2.c1 WHERE d2.c1 IS NULL ;
                      QUERY PLAN
------------------------------------------------------
 Aggregate
   ->  Nested Loop Anti Join
         ->  Seq Scan on data1 d1
         ->  Subquery Scan on d2
               Filter: ((d1.c1 + 1) = d2.c1)
               ->  Unique
                     ->  Sort
                           Sort Key: d2_1.c1
                           ->  Seq Scan on data1 d2_1
(9 rows)

postgres=> EXPLAIN (COSTS OFF, BUFFERS OFF)
SELECT COUNT(*) FROM data1 d1 LEFT JOIN
LATERAL (SELECT DISTINCT ON (c1) c1, c2, d1.c1 AS x FROM data1 d2) d2
ON d1.c1 + 1 = d2.c1 WHERE d2.c1 IS NULL ;
                         QUERY PLAN
------------------------------------------------------------
 Aggregate
   ->  Nested Loop Anti Join
         ->  Seq Scan on data1 d1
         ->  Memoize
               Cache Key: (d1.c1 + 1), d1.c1
               Cache Mode: binary
               ->  Subquery Scan on d2
                     Filter: ((d1.c1 + 1) = d2.c1)
                     ->  Unique
                           ->  Sort
                                 Sort Key: d2_1.c1
                                 ->  Seq Scan on data1 d2_1
(12 rows)

postgres=> EXPLAIN (VERBOSE) SELECT COUNT(col1) FROM data1 ;
                                 QUERY PLAN
----------------------------------------------------------------------------
 Aggregate  (cost=17906.00..17906.01 rows=1 width=8)
   Output: count(col1)
   ->  Seq Scan on public.data1  (cost=0.00..15406.00 rows=1000000 width=4)
         Output: col1, col2
(4 rows)

postgres=> EXPLAIN (VERBOSE) SELECT COUNT(col1) FROM data1 ;
                                 QUERY PLAN
----------------------------------------------------------------------------
 Aggregate  (cost=17906.00..17906.01 rows=1 width=8)
   Output: count(*)
   ->  Seq Scan on public.data1  (cost=0.00..15406.00 rows=1000000 width=0)
         Output: col1, col2
(4 rows)

postgres=> EXPLAIN SELECT * FROM data1 WHERE ctid > '(10,0)' AND ctid < '(30,1)';
                           QUERY PLAN
-----------------------------------------------------------------
 Tid Range Scan on data1  (cost=0.01..61.00 rows=3700 width=10)
   TID Cond: ((ctid > '(10,0)'::tid) AND (ctid < '(30,1)'::tid))
(2 rows)

postgres=> EXPLAIN SELECT * FROM data1 WHERE ctid > '(10,0)' AND ctid < '(30,1)';
                                  QUERY PLAN
------------------------------------------------------------------------------
 Gather  (cost=0.01..33.25 rows=3700 width=10)
   Workers Planned: 4
   ->  Parallel Tid Range Scan on data1  (cost=0.01..33.25 rows=925 width=10)
         TID Cond: ((ctid > '(10,0)'::tid) AND (ctid < '(30,1)'::tid))
(4 rows)

postgres=> CREATE INDEX idx1_data1 ON data1 USING hash (col2) WHERE col2=1000 ;
CREATE INDEX
postgres=> EXPLAIN SELECT * FROM data1 WHERE col2=2000 ;
                        QUERY PLAN
----------------------------------------------------------
 Seq Scan on data1  (cost=0.00..17906.00 rows=1 width=14)
   Filter: (col2 = 2000)
(2 rows)

postgres=> CREATE INDEX idx1_data1 ON data1 USING hash (col2) WHERE col2=1000 ;
CREATE INDEX
postgres=> EXPLAIN SELECT * FROM data1 WHERE col2=1000 ;
                               QUERY PLAN
-------------------------------------------------------------------------
 Index Scan using idx1_data1 on data1  (cost=0.00..8.02 rows=1 width=14)
   Index Cond: (col2 = 1000)
(2 rows)

postgres=> EXPLAIN SELECT * FROM data1 WHERE col1 IS DISTINCT FROM col1 ;
                          QUERY PLAN
---------------------------------------------------------------
 Seq Scan on data1  (cost=0.00..17906.00 rows=995000 width=10)
   Filter: (col1 IS DISTINCT FROM col1)
(2 rows)

postgres=> EXPLAIN SELECT * FROM data1 WHERE col1 IS DISTINCT FROM col1 ;
                          QUERY PLAN
---------------------------------------------------------------
 Seq Scan on data1  (cost=0.00..17906.00 rows=995000 width=10)
   Filter: (col1 <> col1)
(2 rows)

postgres=> EXPLAIN SELECT * FROM data1 WHERE col1 NOT IN (SELECT col1 FROM
 data1 WHERE col1 < 10000) ;
                                QUERY PLAN
--------------------------------------------------------------------------
 Seq Scan on data1  (cost=194.99..364.97 rows=5000 width=8)
   Filter: (NOT (ANY (col1 = (hashed SubPlan 1).col1)))
   SubPlan 1
     ->  Seq Scan on data1 data1_1  (cost=0.00..169.99 rows=9999 width=4)
           Filter: (col1 < 10000)
(5 rows)

postgres=> EXPLAIN SELECT * FROM data1 WHERE col1 NOT IN (SELECT col1 FROM
data1 WHERE col1 < 10000) ;
                                 QUERY PLAN
----------------------------------------------------------------------------
 Hash Anti Join  (cost=29.98..48.71 rows=1 width=8)
   Hash Cond: (data1.col1 = data1_1.col1)
   ->  Seq Scan on data1  (cost=0.00..14.99 rows=999 width=8)
   ->  Hash  (cost=17.49..17.49 rows=999 width=4)
         ->  Seq Scan on data1 data1_1  (cost=0.00..17.49 rows=999 width=4)
               Filter: (col1 < 10000)
(6 rows)

postgres=> EXPLAIN (COSTS OFF) SELECT * FROM data1 WHERE
col1=(SELECT MAX(col1) FROM data1) ;
                                    QUERY PLAN
----------------------------------------------------------------------------…
 Index Scan using data1_pkey on data1
   Index Cond: (col1 = (InitPlan expr_1).col1)
   InitPlan expr_1
     ->  Result
           Replaces: MinMaxAggregate
           InitPlan minmax_1
             ->  Limit
                   ->  Index Only Scan Backward using data1_pkey on data1 …
(8 rows)