Indexes (GIN, GiST, SP-GiST, BRIN, B-tree, Hash, partial, covering)

# ======================================================================
# Indexes live in a model's `Meta.indexes`; migrations emit the `CREATE INDEX`
# DDL shown below (read live from `pg_indexes`). PostgreSQL has many index
# types: GIN for arrays/jsonb/full-text, GiST & SP-GiST for ranges, BRIN for
# huge time-ordered tables, B-tree for ordering, Hash for equality, plus partial
# (`condition=`) and covering (`include=`) indexes, and per-type options
# (`fastupdate`, `pages_per_range`, `fillfactor`).
# ======================================================================

# ----------------------------------------------------------------------
# indexes across the three tables (live from pg_indexes)
# ----------------------------------------------------------------------

# Django:
class Book(models.Model):
    class Meta:
        indexes = [
            GinIndex(fields=["tags"], fastupdate=False),          # arrays / jsonb (+option)
            GinIndex(fields=["search"]),                          # full-text
            GinIndex(OpClass(F("title"), name="gin_trgm_ops")),   # trigram
            BrinIndex(fields=["published"], pages_per_range=64),  # time-ordered (+option)
            BTreeIndex(fields=["pages"], fillfactor=90),          # ordering (+option)
            Index(fields=["price"], condition=Q(is_active=True)), # partial
            Index(fields=["author"], include=["title", "price"]), # covering
        ]

class Reservation(models.Model):
    class Meta:
        indexes = [
            GistIndex(fields=["during"]),                         # GiST for ranges
            SpGistIndex(fields=["during"]),                       # SP-GiST for ranges
        ]

class Server(models.Model):
    class Meta:
        indexes = [HashIndex(fields=["hostname"])]                # hash index

# SQL:
#   CREATE INDEX book_active_price ON public.examples_book USING btree (price) WHERE is_active;
#
#   CREATE INDEX book_author_cover ON public.examples_book USING btree (author_id) INCLUDE (title, price);
#
#   CREATE INDEX book_pages_btree ON public.examples_book USING btree (pages) WITH (fillfactor='90');
#
#   CREATE INDEX book_pub_brin ON public.examples_book USING brin (published) WITH (pages_per_range='64');
#
#   CREATE INDEX book_search_gin ON public.examples_book USING gin (search);
#
#   CREATE INDEX book_tags_gin ON public.examples_book USING gin (tags) WITH (fastupdate=off);
#
#   CREATE INDEX book_title_trgm ON public.examples_book USING gin (title gin_trgm_ops);
#
#   CREATE INDEX examples_book_author_id_ab830087 ON public.examples_book USING btree (author_id);
#
#   CREATE UNIQUE INDEX examples_book_pkey ON public.examples_book USING btree (id);
#
#   CREATE UNIQUE INDEX uq_book_title_author_ci ON public.examples_book USING btree (lower((title)::text), author_id);
#
#   CREATE UNIQUE INDEX examples_reservation_pkey ON public.examples_reservation USING btree (id);
#
#   CREATE INDEX exclude_overlapping_reservations ON public.examples_reservation USING gist (room, during);
#
#   CREATE INDEX resv_during_gist ON public.examples_reservation USING gist (during);
#
#   CREATE INDEX resv_during_spgist ON public.examples_reservation USING spgist (during);
#
#   CREATE UNIQUE INDEX examples_server_pkey ON public.examples_server USING btree (id);
#
#   CREATE INDEX server_host_hash ON public.examples_server USING hash (hostname)

# Result:
#   book_active_price
#   book_author_cover
#   book_pages_btree
#   book_pub_brin
#   book_search_gin
#   book_tags_gin
#   book_title_trgm
#   examples_book_author_id_ab830087
#   examples_book_pkey
#   uq_book_title_author_ci
#   examples_reservation_pkey
#   exclude_overlapping_reservations
#   resv_during_gist
#   resv_during_spgist
#   examples_server_pkey
#   server_host_hash