GIN, GiST, SP-GiST, BRIN, hash and bloom

opclass_session.py

"""Index access methods on PostgreSQL 18.6: opclass.sql."""

from pg_session import Server

# The same plan on every run: autovacuum off, no parallel workers or JIT, statistics from every row, and UTC.
SETTINGS = {"autovacuum": "off", "max_parallel_workers_per_gather": 0, "jit": "off",
            "default_statistics_target": 1000, "timezone": "'UTC'"}

with Server(settings=SETTINGS) as pg:
    pg.up()
    pg.psql("opclass.sql", errors=0)

$ psql -X -a -f opclass.sql
-- The index access methods of this server, and which operators each one can serve for jsonb.
SELECT amname, amtype FROM pg_am WHERE amtype = 'i' ORDER BY amname;
 amname | amtype 
--------+--------
 brin   | i
 btree  | i
 gin    | i
 gist   | i
 hash   | i
 spgist | i
(6 rows)

SELECT am.amname AS method, opc.opcname AS operator_class, opc.opcdefault AS is_default,
       string_agg(DISTINCT op.oprname, ' ' ORDER BY op.oprname) AS operators
FROM pg_opclass AS opc
JOIN pg_am AS am ON am.oid = opc.opcmethod
JOIN pg_amop AS amop ON amop.amopfamily = opc.opcfamily
JOIN pg_operator AS op ON op.oid = amop.amopopr
WHERE opc.opcintype = 'jsonb'::regtype
GROUP BY am.amname, opc.opcname, opc.opcdefault ORDER BY 1, 2;
 method | operator_class | is_default |    operators     
--------+----------------+------------+------------------
 btree  | jsonb_ops      | t          | < <= = > >=
 gin    | jsonb_ops      | t          | ? ?& ?| @> @? @@
 gin    | jsonb_path_ops | f          | @> @? @@
 hash   | jsonb_ops      | t          | =
(4 rows)

-- A B-tree on a jsonb column cannot serve @>, whatever the data: the operator is not in its class.
CREATE TABLE docs (id int PRIMARY KEY, body jsonb NOT NULL);
CREATE TABLE
INSERT INTO docs SELECT n, jsonb_build_object('kind', CASE WHEN n % 100 = 0 THEN 'refund' ELSE 'note' END, 'n', n)
FROM generate_series(1, 50000) AS n;
INSERT 0 50000
CREATE INDEX docs_body_btree ON docs (body);
CREATE INDEX
VACUUM ANALYZE docs;
VACUUM
EXPLAIN (COSTS OFF) SELECT count(*) FROM docs WHERE body @> '{"kind": "refund"}';
                      QUERY PLAN                       
-------------------------------------------------------
 Aggregate
   ->  Seq Scan on docs
         Filter: (body @> '{"kind": "refund"}'::jsonb)
(3 rows)

opclass.sql

-- The index access methods of this server, and which operators each one can serve for jsonb.
SELECT amname, amtype FROM pg_am WHERE amtype = 'i' ORDER BY amname;
SELECT am.amname AS method, opc.opcname AS operator_class, opc.opcdefault AS is_default,
       string_agg(DISTINCT op.oprname, ' ' ORDER BY op.oprname) AS operators
FROM pg_opclass AS opc
JOIN pg_am AS am ON am.oid = opc.opcmethod
JOIN pg_amop AS amop ON amop.amopfamily = opc.opcfamily
JOIN pg_operator AS op ON op.oid = amop.amopopr
WHERE opc.opcintype = 'jsonb'::regtype
GROUP BY am.amname, opc.opcname, opc.opcdefault ORDER BY 1, 2;
-- A B-tree on a jsonb column cannot serve @>, whatever the data: the operator is not in its class.
CREATE TABLE docs (id int PRIMARY KEY, body jsonb NOT NULL);
INSERT INTO docs SELECT n, jsonb_build_object('kind', CASE WHEN n % 100 = 0 THEN 'refund' ELSE 'note' END, 'n', n)
FROM generate_series(1, 50000) AS n;
CREATE INDEX docs_body_btree ON docs (body);
VACUUM ANALYZE docs;
EXPLAIN (COSTS OFF) SELECT count(*) FROM docs WHERE body @> '{"kind": "refund"}';

pg_session.py

"""Throwaway PostgreSQL 18.6 servers for the examples of this lesson.

Every session creates a cluster of its own with initdb in a new folder inside the examples folder, starts it, runs
psql scripts and server programs on it, prints what they printed, and stops and deletes it again, also when something
fails. The server is the pinned PostgreSQL 18.6 from the mise installations next to the Python that runs this file.
It listens only on 127.0.0.1, an address no other computer can reach, on a port the operating system reports as
unused, with no Unix socket, and it asks for a password (scram-sha-256) made up for the run and never printed.
learn:verify's run sandbox cannot read the home folder, where mise keeps PostgreSQL, so these sessions are recorded
with LEARN_VERIFY_NETWORK=allow; they read only this folder and the toolchain and connect to nothing but 127.0.0.1.

The output is exactly what the programs printed, with four substitutions so that a record never shows the computer
it ran on and is the same on every run: the run's folder is shown as ".", the folder of PostgreSQL's programs is left
out of their paths, the operating-system user's name is shown as "learner", and the dots pg_ctl prints while it waits
(one about every tenth of a second) as three. The sessions never print the port. A session stops with status 1 when
a command fails that should have worked, or reports a different number of errors than the session expects, so a run
that went wrong is never recorded as if it had worked.
"""

import os
import pwd
import re
import secrets
import shlex
import shutil
import socket
import subprocess
import sys
import tempfile
import textwrap
import time
from pathlib import Path

PG_VERSION = "18.6"
# .../installs/python/3.14.8/bin/python3 -> .../installs
INSTALLS = Path(sys.executable).resolve().parents[3]
BIN = INSTALLS / "postgres" / PG_VERSION / "bin"
OS_USER = pwd.getpwuid(os.geteuid()).pw_name


def free_port():
    """A TCP port on 127.0.0.1 that nothing listens on now."""
    with socket.socket() as probe:
        probe.bind(("127.0.0.1", 0))
        return probe.getsockname()[1]


class Server:
    """One throwaway cluster: initdb, settings, start; stopped and deleted when the with block ends."""

    def __init__(self, settings=None, initdb=(), name="data"):
        self.settings = dict(settings or {})
        self.initdb_args = list(initdb)
        self.name = name
        self.running = []

    def __enter__(self):
        if not (BIN / "postgres").exists():
            sys.exit(f"PostgreSQL {PG_VERSION} was not found next to this Python")
        self.base = Path(tempfile.mkdtemp(prefix="pg", dir=".")).resolve()
        self.port = free_port()
        self.password = secrets.token_urlsafe(24)
        (self.base / "pwfile").write_text(self.password + "\n")
        self.env = {**os.environ, "PGHOST": "127.0.0.1", "PGPORT": str(self.port), "PGUSER": "postgres",
                    "PGPASSWORD": self.password, "PGDATABASE": "postgres", "PGCONNECT_TIMEOUT": "10"}
        for key in ("PGSERVICE", "PGSERVICEFILE", "PGOPTIONS", "PSQLRC"):
            self.env.pop(key, None)
        for script in Path(".").glob("*.sql"):
            shutil.copy(script, self.base / script.name)
        for data in Path(".").glob("*.csv"):
            shutil.copy(data, self.base / data.name)
        return self

    def __exit__(self, *exc):
        for folder, _port in reversed(self.running):
            self._call(["pg_ctl", "-D", folder, "-m", "fast", "-w", "-t", "60", "stop"])
        self.running = []
        shutil.rmtree(self.base, ignore_errors=True)

    # ---- the cluster ---------------------------------------------------------------------------------------------
    def initdb(self, show=False):
        args = ["initdb", "-D", self.name, "-U", "postgres", "--auth=scram-sha-256", "--pwfile=pwfile",
                *self.initdb_args]
        if not show:
            args.append("--no-instructions")
        self._expect(args, show=show)
        with open(self.base / self.name / "postgresql.conf", "a") as conf:
            conf.write("\n# Added by the session: 127.0.0.1 only, no Unix socket.\n")
            conf.write(f"listen_addresses = '127.0.0.1'\nport = {self.port}\nunix_socket_directories = ''\n")
            for key, value in self.settings.items():
                conf.write(f"{key} = {value}\n")

    def start(self, show=False, folder=None, port=None):
        folder = folder or self.name
        self._expect(["pg_ctl", "-D", folder, "-l", f"{folder}.log", "-w", "-t", "60", "start"], show=show)
        self.running.append((folder, port or self.port))

    def up(self):
        """initdb and start, quietly."""
        self.initdb()
        self.start()
        return self

    def standby(self, folder="standby"):
        """A streaming standby of this server, made with pg_basebackup -R, started on a port of its own."""
        self._expect(["pg_basebackup", "-D", folder, "-R", "-X", "stream", "-c", "fast"])
        port = free_port()
        with open(self.base / folder / "postgresql.conf", "a") as conf:
            conf.write(f"port = {port}\n")
        self.start(folder=folder, port=port)
        for _ in range(300):
            if self.query("SELECT count(*) FROM pg_stat_replication WHERE state = 'streaming'") == "1":
                return port
            time.sleep(0.1)
        sys.exit("the standby did not start streaming")

    # ---- running things ------------------------------------------------------------------------------------------
    def _call(self, args, env=None):
        args = [str(a) for a in args]
        done = subprocess.run([str(BIN / args[0]), *args[1:]], stdout=subprocess.PIPE, stderr=subprocess.STDOUT,
                              text=True, cwd=self.base, env=env or self.env)
        return done.returncode, self.clean(done.stdout)

    def _expect(self, args, show=False, status=0):
        code, out = self._call(args)
        if show:
            _show(args, out)
        if code != status:
            if not show:
                print(out)
            sys.exit(f"{args[0]} ended with status {code}, not {status}")
        return out

    def clean(self, text):
        """The run's folder as ".", the user as learner, pg_ctl's waiting dots as three (see the docstring)."""
        text = text.replace(str(BIN) + "/", "").replace(str(self.base) + "/", "").replace(str(self.base), ".")
        text = re.sub(r"(waiting for server to (?:start|shut down))\.+", r"\1...", text)
        return text.replace(f'"{OS_USER}"', '"learner"')

    def wait_for(self, sql, value):
        """Waits (unprinted) until a query returns the value, for example after a reload."""
        for _ in range(300):
            if self.query(sql) == value:
                return
            time.sleep(0.1)
        sys.exit(f"{sql} never returned {value}")

    def show_file(self, path):
        """Prints a text file of the cluster, as `cat` would."""
        print(f"$ cat {path}")
        print((self.base / path).read_text().rstrip("\n"))
        print()

    def query(self, sql, port=None):
        """One value from one query, unprinted (the session uses it to wait or to decide)."""
        env = {**self.env, "PGPORT": str(port or self.port)}
        code, out = self._call(["psql", "-X", "-At", "-c", sql], env=env)
        if code != 0:
            sys.exit(f"the query failed: {out}")
        return out.strip()

    def silent(self, sql):
        """Runs a statement without printing it (used only to give a role the run's password)."""
        self.query(sql)

    def psql(self, script, errors=0, status=None, user=None, db=None, port=None, flags=("-a",), note=None):
        """Runs a psql script with -X (no ~/.psqlrc) and the given flags, and prints the command and its output.
        `errors` is how many ERROR lines the script must print; `status` the exit status it must end with."""
        env = {**self.env, "PGPORT": str(port or self.port)}
        if user:
            env["PGUSER"] = user
        if db:
            env["PGDATABASE"] = db
        args = ["psql", "-X", *flags, "-f", script]
        code, out = self._call(args, env=env)
        shown = ["psql", "-X", *flags, "-f", script]
        if user:
            shown[1:1] = ["-U", user]
        if db:
            shown[1:1] = ["-d", db]
        if note:
            print(f"-- {note}")
        _show(shown, out)
        found = sum(1 for line in out.splitlines() if "ERROR:" in line or ": error:" in line)
        expected_status = 0 if status is None else status
        if found != errors or code != expected_status:
            sys.exit(f"{script}: {found} errors and status {code}; the session expects {errors} and {expected_status}")

    def sessions(self, count):
        """Opens `count` idle psql connections (each one a client backend) until the with block ends."""
        return _Idle(self, count)

    def shell(self, *args, status=0):
        """Runs one of PostgreSQL's programs and prints the command and its output."""
        return self._expect(list(args), show=True, status=status)

    def listing(self, folder, prefix=""):
        """Prints the names in a folder of the cluster (sorted, folders with a slash), as `ls -p` would list them."""
        path = self.base / folder
        names = sorted(n + ("/" if (path / n).is_dir() else "") for n in os.listdir(path) if n.startswith(prefix))
        print(f"-- in {folder}" + (f", the names that start with {prefix}" if prefix else "") + ":")
        print(textwrap.fill("  ".join(names), width=78, initial_indent="   ", subsequent_indent="   ",
                            break_on_hyphens=False))
        print()
        return names


def _arg(a):
    """A command-line argument as a reader would type it: double quotes around text with single quotes in it."""
    a = str(a)
    if re.fullmatch(r"[\w@%+=:,./-]+", a):
        return a
    if not re.search(r"[\"$`\\!]", a):
        return f'"{a}"'
    return shlex.quote(a)


def _show(args, out):
    print("$ " + " ".join(_arg(a) for a in args))
    if out.strip():
        print(out.rstrip("\n"))
    print()


class _Idle:
    """Idle psql connections, closed again when the with block ends."""

    def __init__(self, server, count):
        self.server, self.count, self.procs = server, count, []

    def __enter__(self):
        before = int(self.server.query("SELECT count(*) FROM pg_stat_activity WHERE backend_type = 'client backend'"))
        for _ in range(self.count):
            self.procs.append(subprocess.Popen([str(BIN / "psql"), "-X", "-q"], stdin=subprocess.PIPE,
                                               stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL,
                                               env=self.server.env, cwd=self.server.base))
        sql = "SELECT count(*) FROM pg_stat_activity WHERE backend_type = 'client backend'"
        for _ in range(300):
            # the query's own connection is one more client backend
            if int(self.server.query(sql)) == before + self.count:
                return self
            time.sleep(0.1)
        sys.exit("the idle connections did not open")

    def __exit__(self, *exc):
        for p in self.procs:
            p.stdin.close()
            p.wait(timeout=30)

gin_session.py

"""Index access methods on PostgreSQL 18.6: gin.sql."""

from pg_session import Server

# The same plan on every run: autovacuum off, no parallel workers or JIT, statistics from every row, and UTC.
SETTINGS = {"autovacuum": "off", "max_parallel_workers_per_gather": 0, "jit": "off",
            "default_statistics_target": 1000, "timezone": "'UTC'"}

with Server(settings=SETTINGS) as pg:
    pg.up()
    pg.psql("gin.sql", errors=0)

$ psql -X -a -f gin.sql
-- 50,000 documents with tags and a jsonb body; 1 in 100 is a refund.
CREATE TABLE docs (id int PRIMARY KEY, tags text[] NOT NULL, body jsonb NOT NULL);
CREATE TABLE
INSERT INTO docs
SELECT n, ARRAY['t' || (n % 50), 't' || (n % 7)],
       jsonb_build_object('kind', CASE WHEN n % 100 = 0 THEN 'refund' ELSE 'note' END, 'n', n)
FROM generate_series(1, 50000) AS n;
INSERT 0 50000
CREATE INDEX docs_tags ON docs USING gin (tags);
CREATE INDEX
CREATE INDEX docs_body ON docs USING gin (body jsonb_path_ops);
CREATE INDEX
VACUUM ANALYZE docs;
VACUUM
-- An array containing two tags: GIN looks up each tag and intersects the two lists of rows.
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF) SELECT count(*) FROM docs WHERE tags @> ARRAY['t3', 't5'];
                                                  QUERY PLAN                                                   
---------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=779.44..779.45 rows=1 width=8) (actual rows=1.00 loops=1)
   Buffers: shared hit=298
   ->  Bitmap Heap Scan on docs  (cost=28.16..776.24 rows=1280 width=0) (actual rows=286.00 loops=1)
         Recheck Cond: (tags @> '{t3,t5}'::text[])
         Heap Blocks: exact=286
         Buffers: shared hit=298
         ->  Bitmap Index Scan on docs_tags  (cost=0.00..27.84 rows=1280 width=0) (actual rows=286.00 loops=1)
               Index Cond: (tags @> '{t3,t5}'::text[])
               Index Searches: 1
               Buffers: shared hit=12
 Planning:
   Buffers: shared hit=22 read=2
(12 rows)

-- A jsonb containment test through the jsonb_path_ops class.
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF) SELECT count(*) FROM docs WHERE body @> '{"kind": "refund"}';
                                                   QUERY PLAN                                                    
-----------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1270.70..1270.71 rows=1 width=8) (actual rows=1.00 loops=1)
   Buffers: shared hit=503
   ->  Bitmap Heap Scan on docs  (cost=181.08..1208.26 rows=24975 width=0) (actual rows=500.00 loops=1)
         Recheck Cond: (body @> '{"kind": "refund"}'::jsonb)
         Heap Blocks: exact=500
         Buffers: shared hit=503
         ->  Bitmap Index Scan on docs_body  (cost=0.00..174.83 rows=24975 width=0) (actual rows=500.00 loops=1)
               Index Cond: (body @> '{"kind": "refund"}'::jsonb)
               Index Searches: 1
               Buffers: shared hit=3
 Planning:
   Buffers: shared hit=13
(12 rows)

-- The ->> operator is not in any GIN class for jsonb: the same question asked this way scans the table.
EXPLAIN (COSTS OFF) SELECT count(*) FROM docs WHERE body ->> 'kind' = 'refund';
                         QUERY PLAN                         
------------------------------------------------------------
 Aggregate
   ->  Seq Scan on docs
         Filter: ((body ->> 'kind'::text) = 'refund'::text)
(3 rows)

SELECT relname, pg_size_pretty(pg_relation_size(oid)) AS size FROM pg_class
WHERE relname IN ('docs', 'docs_tags', 'docs_body') ORDER BY relname;
  relname  |  size   
-----------+---------
 docs      | 5720 kB
 docs_body | 2800 kB
 docs_tags | 296 kB
(3 rows)

gin.sql

-- 50,000 documents with tags and a jsonb body; 1 in 100 is a refund.
CREATE TABLE docs (id int PRIMARY KEY, tags text[] NOT NULL, body jsonb NOT NULL);
INSERT INTO docs
SELECT n, ARRAY['t' || (n % 50), 't' || (n % 7)],
       jsonb_build_object('kind', CASE WHEN n % 100 = 0 THEN 'refund' ELSE 'note' END, 'n', n)
FROM generate_series(1, 50000) AS n;
CREATE INDEX docs_tags ON docs USING gin (tags);
CREATE INDEX docs_body ON docs USING gin (body jsonb_path_ops);
VACUUM ANALYZE docs;
-- An array containing two tags: GIN looks up each tag and intersects the two lists of rows.
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF) SELECT count(*) FROM docs WHERE tags @> ARRAY['t3', 't5'];
-- A jsonb containment test through the jsonb_path_ops class.
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF) SELECT count(*) FROM docs WHERE body @> '{"kind": "refund"}';
-- The ->> operator is not in any GIN class for jsonb: the same question asked this way scans the table.
EXPLAIN (COSTS OFF) SELECT count(*) FROM docs WHERE body ->> 'kind' = 'refund';
SELECT relname, pg_size_pretty(pg_relation_size(oid)) AS size FROM pg_class
WHERE relname IN ('docs', 'docs_tags', 'docs_body') ORDER BY relname;

gist_session.py

"""Index access methods on PostgreSQL 18.6: gist.sql."""

from pg_session import Server

# The same plan on every run: autovacuum off, no parallel workers or JIT, statistics from every row, and UTC.
SETTINGS = {"autovacuum": "off", "max_parallel_workers_per_gather": 0, "jit": "off",
            "default_statistics_target": 1000, "timezone": "'UTC'"}

with Server(settings=SETTINGS) as pg:
    pg.up()
    pg.psql("gist.sql", errors=1)

$ psql -X -a -f gist.sql
-- GiST with btree_gist: no two bookings of the same room may overlap in time.
CREATE EXTENSION btree_gist;
CREATE EXTENSION
CREATE TABLE bookings (id int PRIMARY KEY, room int NOT NULL, during tstzrange NOT NULL,
                       EXCLUDE USING gist (room WITH =, during WITH &&));
CREATE TABLE
INSERT INTO bookings VALUES (1, 101, '[2026-03-02 10:00+00, 2026-03-02 11:00+00)');
INSERT 0 1
INSERT INTO bookings VALUES (2, 102, '[2026-03-02 10:30+00, 2026-03-02 12:00+00)');
INSERT 0 1
INSERT INTO bookings VALUES (3, 101, '[2026-03-02 10:30+00, 2026-03-02 12:00+00)');
psql:gist.sql:7: ERROR:  conflicting key value violates exclusion constraint "bookings_room_during_excl"
DETAIL:  Key (room, during)=(101, ["2026-03-02 10:30:00+00","2026-03-02 12:00:00+00")) conflicts with existing key (room, during)=(101, ["2026-03-02 10:00:00+00","2026-03-02 11:00:00+00")).
-- SP-GiST on 100,000 points: the five nearest to (500, 5), straight from the index.
CREATE TABLE places (id int PRIMARY KEY, p point NOT NULL);
CREATE TABLE
INSERT INTO places SELECT n, point(n % 1000, n / 1000) FROM generate_series(1, 100000) AS n;
INSERT 0 100000
CREATE INDEX places_p ON places USING spgist (p);
CREATE INDEX
VACUUM ANALYZE places;
VACUUM
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF) SELECT id, p FROM places ORDER BY p <-> point(500, 5) LIMIT 5;
                                                   QUERY PLAN                                                    
-----------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.28..0.63 rows=5 width=28) (actual rows=5.00 loops=1)
   Buffers: shared hit=9
   ->  Index Scan using places_p on places  (cost=0.28..6976.28 rows=100000 width=28) (actual rows=5.00 loops=1)
         Order By: (p <-> '(500,5)'::point)
         Index Searches: 1
         Buffers: shared hit=9
 Planning:
   Buffers: shared hit=42 read=2
(8 rows)

SELECT id, p, round((p <-> point(500, 5))::numeric, 2) AS distance FROM places ORDER BY p <-> point(500, 5) LIMIT 5;
  id  |    p    | distance 
------+---------+----------
 5500 | (500,5) |     0.00
 4500 | (500,4) |     1.00
 5501 | (501,5) |     1.00
 5499 | (499,5) |     1.00
 6500 | (500,6) |     1.00
(5 rows)

gist.sql

-- GiST with btree_gist: no two bookings of the same room may overlap in time.
CREATE EXTENSION btree_gist;
CREATE TABLE bookings (id int PRIMARY KEY, room int NOT NULL, during tstzrange NOT NULL,
                       EXCLUDE USING gist (room WITH =, during WITH &&));
INSERT INTO bookings VALUES (1, 101, '[2026-03-02 10:00+00, 2026-03-02 11:00+00)');
INSERT INTO bookings VALUES (2, 102, '[2026-03-02 10:30+00, 2026-03-02 12:00+00)');
INSERT INTO bookings VALUES (3, 101, '[2026-03-02 10:30+00, 2026-03-02 12:00+00)');
-- SP-GiST on 100,000 points: the five nearest to (500, 5), straight from the index.
CREATE TABLE places (id int PRIMARY KEY, p point NOT NULL);
INSERT INTO places SELECT n, point(n % 1000, n / 1000) FROM generate_series(1, 100000) AS n;
CREATE INDEX places_p ON places USING spgist (p);
VACUUM ANALYZE places;
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF) SELECT id, p FROM places ORDER BY p <-> point(500, 5) LIMIT 5;
SELECT id, p, round((p <-> point(500, 5))::numeric, 2) AS distance FROM places ORDER BY p <-> point(500, 5) LIMIT 5;

brin_session.py

"""Index access methods on PostgreSQL 18.6: brin.sql."""

from pg_session import Server

# The same plan on every run: autovacuum off, no parallel workers or JIT, statistics from every row, and UTC.
SETTINGS = {"autovacuum": "off", "max_parallel_workers_per_gather": 0, "jit": "off",
            "default_statistics_target": 1000, "timezone": "'UTC'"}

with Server(settings=SETTINGS) as pg:
    pg.up()
    pg.psql("brin.sql", errors=0)

$ psql -X -a -f brin.sql
-- 300,000 readings, one a minute, so the time column follows the physical order of the table.
CREATE TABLE readings (id bigint GENERATED ALWAYS AS IDENTITY, at timestamptz NOT NULL, sensor int NOT NULL,
                       value numeric(6,2) NOT NULL);
CREATE TABLE
INSERT INTO readings (at, sensor, value)
SELECT timestamptz '2026-01-01 00:00+00' + n * interval '1 minute', n % 20, (n % 1000) / 10.0
FROM generate_series(1, 300000) AS n;
INSERT 0 300000
CREATE INDEX readings_at_brin ON readings USING brin (at);
CREATE INDEX
CREATE INDEX readings_at_btree ON readings (at);
CREATE INDEX
VACUUM ANALYZE readings;
VACUUM
SELECT relname, pg_size_pretty(pg_relation_size(oid)) AS size FROM pg_class
WHERE relname IN ('readings', 'readings_at_brin', 'readings_at_btree') ORDER BY relname;
      relname      |  size   
-------------------+---------
 readings          | 17 MB
 readings_at_brin  | 24 kB
 readings_at_btree | 6600 kB
(3 rows)

-- BRIN helps only when values follow the physical order: correlation near 1 or -1.
SELECT attname, round(correlation::numeric, 3) AS correlation FROM pg_stats
WHERE tablename = 'readings' AND attname IN ('at', 'sensor') ORDER BY attname;
 attname | correlation 
---------+-------------
 at      |       1.000
 sensor  |       0.050
(2 rows)

-- One day of readings through the BRIN index alone: whole block ranges, then a recheck of every row in them.
DROP INDEX readings_at_btree;
DROP INDEX
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM readings WHERE at >= timestamptz '2026-03-01 00:00+00' AND at < timestamptz '2026-03-02 00:00+00';
                                                                      QUERY PLAN                                                                      
------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=2472.00..2472.01 rows=1 width=8) (actual rows=1.00 loops=1)
   Buffers: shared hit=133
   ->  Bitmap Heap Scan on readings  (cost=12.39..2468.40 rows=1440 width=0) (actual rows=1440.00 loops=1)
         Recheck Cond: ((at >= '2026-03-01 00:00:00+00'::timestamp with time zone) AND (at < '2026-03-02 00:00:00+00'::timestamp with time zone))
         Rows Removed by Index Recheck: 15968
         Heap Blocks: lossy=128
         Buffers: shared hit=133
         ->  Bitmap Index Scan on readings_at_brin  (cost=0.00..12.03 rows=16667 width=0) (actual rows=1280.00 loops=1)
               Index Cond: ((at >= '2026-03-01 00:00:00+00'::timestamp with time zone) AND (at < '2026-03-02 00:00:00+00'::timestamp with time zone))
               Index Searches: 1
               Buffers: shared hit=5
 Planning:
   Buffers: shared hit=21
(13 rows)

brin.sql

-- 300,000 readings, one a minute, so the time column follows the physical order of the table.
CREATE TABLE readings (id bigint GENERATED ALWAYS AS IDENTITY, at timestamptz NOT NULL, sensor int NOT NULL,
                       value numeric(6,2) NOT NULL);
INSERT INTO readings (at, sensor, value)
SELECT timestamptz '2026-01-01 00:00+00' + n * interval '1 minute', n % 20, (n % 1000) / 10.0
FROM generate_series(1, 300000) AS n;
CREATE INDEX readings_at_brin ON readings USING brin (at);
CREATE INDEX readings_at_btree ON readings (at);
VACUUM ANALYZE readings;
SELECT relname, pg_size_pretty(pg_relation_size(oid)) AS size FROM pg_class
WHERE relname IN ('readings', 'readings_at_brin', 'readings_at_btree') ORDER BY relname;
-- BRIN helps only when values follow the physical order: correlation near 1 or -1.
SELECT attname, round(correlation::numeric, 3) AS correlation FROM pg_stats
WHERE tablename = 'readings' AND attname IN ('at', 'sensor') ORDER BY attname;
-- One day of readings through the BRIN index alone: whole block ranges, then a recheck of every row in them.
DROP INDEX readings_at_btree;
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM readings WHERE at >= timestamptz '2026-03-01 00:00+00' AND at < timestamptz '2026-03-02 00:00+00';

hashbloom_session.py

"""Index access methods on PostgreSQL 18.6: hashbloom.sql."""

from pg_session import Server

# The same plan on every run: autovacuum off, no parallel workers or JIT, statistics from every row, and UTC.
SETTINGS = {"autovacuum": "off", "max_parallel_workers_per_gather": 0, "jit": "off",
            "default_statistics_target": 1000, "timezone": "'UTC'"}

with Server(settings=SETTINGS) as pg:
    pg.up()
    pg.psql("hashbloom.sql", errors=0)

$ psql -X -a -f hashbloom.sql
-- 100,000 tokens: a hash index and a B-tree on the same column.
CREATE TABLE tokens (token text NOT NULL);
CREATE TABLE
INSERT INTO tokens SELECT md5(n::text) FROM generate_series(1, 100000) AS n;
INSERT 0 100000
CREATE INDEX tokens_hash ON tokens USING hash (token);
CREATE INDEX
CREATE INDEX tokens_btree ON tokens (token);
CREATE INDEX
VACUUM ANALYZE tokens;
VACUUM
SELECT relname, pg_size_pretty(pg_relation_size(oid)) AS size FROM pg_class
WHERE relname IN ('tokens_hash', 'tokens_btree') ORDER BY relname;
   relname    |  size   
--------------+---------
 tokens_btree | 5792 kB
 tokens_hash  | 4112 kB
(2 rows)

DROP INDEX tokens_btree;
DROP INDEX
EXPLAIN (COSTS OFF) SELECT * FROM tokens WHERE token = 'c4ca4238a0b923820dcc509a6f75849b';
                            QUERY PLAN                            
------------------------------------------------------------------
 Index Scan using tokens_hash on tokens
   Index Cond: (token = 'c4ca4238a0b923820dcc509a6f75849b'::text)
(2 rows)

EXPLAIN (COSTS OFF) SELECT * FROM tokens WHERE token > 'ffff';
            QUERY PLAN            
----------------------------------
 Seq Scan on tokens
   Filter: (token > 'ffff'::text)
(2 rows)

-- bloom (a contrib module): one index on six columns for equality on any combination of them.
CREATE EXTENSION bloom;
CREATE EXTENSION
CREATE TABLE trips (a int, b int, c int, d int, e int, f int);
CREATE TABLE
INSERT INTO trips SELECT n % 97, n % 89, n % 83, n % 79, n % 73, n % 71 FROM generate_series(1, 200000) AS n;
INSERT 0 200000
CREATE INDEX trips_bloom ON trips USING bloom (a, b, c, d, e, f);
CREATE INDEX
VACUUM ANALYZE trips;
VACUUM
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF) SELECT count(*) FROM trips WHERE c = 5 AND e = 9;
                                                   QUERY PLAN                                                    
-----------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=3692.65..3692.66 rows=1 width=8) (actual rows=1.00 loops=1)
   Buffers: shared hit=507
   ->  Bitmap Heap Scan on trips  (cost=3576.01..3692.57 rows=33 width=0) (actual rows=33.00 loops=1)
         Recheck Cond: ((c = 5) AND (e = 9))
         Rows Removed by Index Recheck: 84
         Heap Blocks: exact=114
         Buffers: shared hit=507
         ->  Bitmap Index Scan on trips_bloom  (cost=0.00..3576.00 rows=33 width=0) (actual rows=117.00 loops=1)
               Index Cond: ((c = 5) AND (e = 9))
               Index Searches: 1
               Buffers: shared hit=393
 Planning:
   Buffers: shared hit=19
(13 rows)

SELECT relname, pg_size_pretty(pg_relation_size(oid)) AS size FROM pg_class
WHERE relname IN ('trips', 'trips_bloom') ORDER BY relname;
   relname   |   size   
-------------+----------
 trips       | 10192 kB
 trips_bloom | 3152 kB
(2 rows)

hashbloom.sql

-- 100,000 tokens: a hash index and a B-tree on the same column.
CREATE TABLE tokens (token text NOT NULL);
INSERT INTO tokens SELECT md5(n::text) FROM generate_series(1, 100000) AS n;
CREATE INDEX tokens_hash ON tokens USING hash (token);
CREATE INDEX tokens_btree ON tokens (token);
VACUUM ANALYZE tokens;
SELECT relname, pg_size_pretty(pg_relation_size(oid)) AS size FROM pg_class
WHERE relname IN ('tokens_hash', 'tokens_btree') ORDER BY relname;
DROP INDEX tokens_btree;
EXPLAIN (COSTS OFF) SELECT * FROM tokens WHERE token = 'c4ca4238a0b923820dcc509a6f75849b';
EXPLAIN (COSTS OFF) SELECT * FROM tokens WHERE token > 'ffff';
-- bloom (a contrib module): one index on six columns for equality on any combination of them.
CREATE EXTENSION bloom;
CREATE TABLE trips (a int, b int, c int, d int, e int, f int);
INSERT INTO trips SELECT n % 97, n % 89, n % 83, n % 79, n % 73, n % 71 FROM generate_series(1, 200000) AS n;
CREATE INDEX trips_bloom ON trips USING bloom (a, b, c, d, e, f);
VACUUM ANALYZE trips;
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF) SELECT count(*) FROM trips WHERE c = 5 AND e = 9;
SELECT relname, pg_size_pretty(pg_relation_size(oid)) AS size FROM pg_class
WHERE relname IN ('trips', 'trips_bloom') ORDER BY relname;