{
  "$type": "site.standard.document",
  "bskyPostRef": {
    "cid": "bafyreieceu3cbe6jftf6uzurlzh26ajevqnoknhhkdrohbgf3ikzwcvmqu",
    "uri": "at://did:plc:25rdn5elo5izoxrmtis34zuk/app.bsky.feed.post/3mptgphtuy6l2"
  },
  "coverImage": {
    "$type": "blob",
    "ref": {
      "$link": "bafkreifta57hafqzdq7sruzqjuybwz5h7w4qmidi7zksqdyhnnxu66lpku"
    },
    "mimeType": "image/webp",
    "size": 73282
  },
  "path": "/gpuneet/database-indexing-and-query-optimization-for-python-developers-206i",
  "publishedAt": "2026-07-04T15:29:37.000Z",
  "site": "https://dev.to",
  "tags": [
    "python",
    "database",
    "performance",
    "sql",
    "previous post"
  ],
  "textContent": "##  Introduction\n\nFixing N+1 queries with `select_related`/`prefetch_related` or `selectinload` (see the previous post) gets you down to a small, sane number of queries per request. The next bottleneck is what each query costs once the table has millions of rows — and that is almost always about indexing.\n\nAn index turns \"scan every row\" into \"look it up directly.\" Skip it, and a query that's instant in development takes seconds once real data volume shows up in production.\n\n##  How Indexes Work: The B-Tree Intuition\n\nWithout an index, a `WHERE` clause forces a **sequential scan** : the database reads every row and checks the condition — `O(n)`, cost grows linearly with table size.\n\nAn index is a separate, sorted structure (almost always a **B-tree**) mapping column values to row locations. Because it's sorted and balanced, finding a value is a tree walk: `O(log n)`. On a 10-million-row table, that's the difference between reading 10 million rows and roughly 23 tree nodes.\n\nThis isn't free:\n\n  * **Writes get slower** — every `INSERT`/`UPDATE`/`DELETE` on an indexed column also updates the index.\n  * **Storage grows** — each index is a sorted copy of (part of) the data.\n\n\n\nAn index trades write cost and storage for read speed. Indexing a column you rarely filter or sort on is pure cost, no benefit.\n\n##  Reading Query Plans: EXPLAIN ANALYZE\n\nPostgres' `EXPLAIN ANALYZE` shows what the planner actually did, not an estimate.\n\n**Before an index** , filtering `orders` by `customer_id`:\n\n\n\n    EXPLAIN ANALYZE\n    SELECT * FROM orders WHERE customer_id = 48291;\n\n\n\n    Seq Scan on orders  (cost=0.00..21453.00 rows=42 width=96) (actual time=0.021..118.442 rows=41 loops=1)\n      Filter: (customer_id = 48291)\n      Rows Removed by Filter: 1199959\n    Planning Time: 0.112 ms\n    Execution Time: 118.471 ms\n\n\n`Seq Scan` means Postgres read all ~1.2 million rows and discarded all but 41. `actual time` is real elapsed time — 118ms for one lookup.\n\n**After** `CREATE INDEX idx_orders_customer_id ON orders (customer_id);`:\n\n\n\n    Index Scan using idx_orders_customer_id on orders  (cost=0.42..8.53 rows=42 width=96) (actual time=0.018..0.041 rows=41 loops=1)\n      Index Cond: (customer_id = 48291)\n    Planning Time: 0.098 ms\n    Execution Time: 0.061 ms\n\n\n`Index Scan` walks the B-tree straight to the matching rows: 118ms down to 0.06ms, roughly 1,900x — and the gap only widens as the table grows. What to check in any plan:\n\n  * **Seq Scan vs Index Scan / Index Only Scan** — the single biggest signal.\n  * **`rows` (estimate) vs `actual … rows`** — a large gap means stale table statistics (run `ANALYZE`).\n  * **`cost=startup..total`** — the planner's internal unit, useful for comparing plans, not wall-clock time.\n  * **`Rows Removed by Filter`** — a high number on a Seq Scan is a strong signal an index would help.\n\n\n\n##  Composite Indexes and Column Order\n\nAn index on `(customer_id, status)` is not the same as `(status, customer_id)`. B-tree indexes are searchable by their **leftmost prefix** : sorted first by the first column, then by the second within each value of the first.\n\n\n\n    CREATE INDEX idx_orders_customer_status ON orders (customer_id, status);\n\n\nUsable for:\n\n\n\n    WHERE customer_id = 48291                          -- uses leftmost column\n    WHERE customer_id = 48291 AND status = 'SHIPPED'    -- uses both columns\n\n\n**Not** usable (as an index scan on this index) for:\n\n\n\n    WHERE status = 'SHIPPED'   -- status alone isn't a leftmost prefix\n\n\nOrder columns by how they're actually queried: the column used alone or most selectively goes first.\n\n###  Covering Indexes and Index-Only Scans\n\nIf an index includes every column a query needs, Postgres can answer straight from the index — an **Index Only Scan** :\n\n\n\n    CREATE INDEX idx_orders_customer_status_total\n        ON orders (customer_id, status) INCLUDE (total_amount);\n\n\n\n    EXPLAIN ANALYZE\n    SELECT status, total_amount FROM orders WHERE customer_id = 48291;\n\n\n\n    Index Only Scan using idx_orders_customer_status_total on orders\n      (cost=0.42..4.65 rows=41 width=13) (actual time=0.015..0.028 rows=41 loops=1)\n      Index Cond: (customer_id = 48291)\n      Heap Fetches: 0\n\n\n`Heap Fetches: 0` confirms the table itself was never touched.\n\n###  When an Index Is NOT Used\n\nThe planner will happily ignore an index that exists. Common reasons:\n\n  * **A function wraps the column** : `WHERE UPPER(email) = 'X'` can't use a plain index on `email` (a **functional index** , `CREATE INDEX ON users (UPPER(email))`, fixes this).\n  * **A leading wildcard** : `WHERE name LIKE '%smith'` can't use a standard B-tree — the sorted prefix is useless without a known prefix. `LIKE 'smith%'` can.\n  * **Low selectivity** : if 90% of rows match, a Seq Scan genuinely costs less than following an index and fetching almost every row anyway — the planner correctly skips the index.\n  * **Implicit type casts** : comparing a text column to an integer literal, or a timestamp column to an unparsed string, can force a scan. Match types explicitly.\n\n\n\n##  Selectivity and Cardinality\n\n**Selectivity** is the fraction of rows a condition matches; **cardinality** is the number of distinct values in a column. An index on a boolean `is_active` column has cardinality 2 — following it typically still means fetching close to half the table, which costs more than scanning it outright. Indexes pay off on **high-cardinality, selective** columns (customer IDs, emails, timestamps), not on flags. If you must filter efficiently on a low-cardinality column, pair it in a composite index behind a selective column, or use a **partial index** (`CREATE INDEX ... WHERE is_active = true` for a rare status).\n\n##  Pagination: Keyset vs OFFSET\n\nThe earlier ORM post flagged that slicing a queryset after a `prefetch_related`/join doesn't page cleanly. The same physics show up at the SQL level with `OFFSET`.\n\n\n\n    -- Deep page: Postgres must still generate and discard the first 100,000 rows\n    SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 100000;\n\n\n`OFFSET` can't skip rows using the index alone — it still walks past them. **Keyset (seek) pagination** remembers the last row seen and uses an indexed condition instead of an offset:\n\n\n\n    -- Keyset: uses the index on id directly, same cost on page 1 or page 5,000\n    SELECT * FROM orders WHERE id > :last_seen_id ORDER BY id LIMIT 20;\n\n\nDjango and SQLAlchemy both express this as an ordinary indexed filter:\n\n\n\n    # Django: keyset pagination instead of Paginator's OFFSET-based pages\n    Order.objects.filter(id__gt=last_seen_id).order_by(\"id\")[:20]\n\n    # SQLAlchemy\n    session.query(Order).filter(Order.id > last_seen_id).order_by(Order.id).limit(20)\n\n\nThis requires (and benefits from) an index on the ordering column — exactly the `idx_orders_customer_id`-style index above.\n\n##  The Django and SQLAlchemy Angle\n\nNeither ORM invents the right indexes for you — that's still informed by the plans above, not by the model definition alone.\n\n###  Django — `Meta.indexes` and `db_index`\n\n\n    class Order(models.Model):\n        customer_id = models.IntegerField(db_index=True)\n        status = models.CharField(max_length=20)\n        total_amount = models.DecimalField(max_digits=10, decimal_places=2)\n\n        class Meta:\n            indexes = [\n                models.Index(fields=[\"customer_id\", \"status\"], name=\"idx_orders_customer_status\"),\n            ]\n\n\n`db_index=True` is fine for a single column; for composite/covering indexes use `Meta.indexes`. Either way, this only takes effect through a **migration** — Django generates one when you run `makemigrations`, and that migration file (not the model) is what actually creates the index:\n\n\n\n    # orders/migrations/0007_add_customer_status_index.py\n    operations = [\n        migrations.AddIndex(\n            model_name=\"order\",\n            index=models.Index(fields=[\"customer_id\", \"status\"], name=\"idx_orders_customer_status\"),\n        ),\n    ]\n\n\nReview the generated SQL with `python manage.py sqlmigrate orders 0007` before applying it in production, and consider Postgres' `CREATE INDEX CONCURRENTLY` for large existing tables (Django's `AddIndexConcurrently` on Postgres, run outside an atomic migration).\n\n###  SQLAlchemy — `Index(...)` / `index=True` + Alembic\n\n\n    from sqlalchemy import Column, Integer, String, Numeric, Index\n\n    class Order(Base):\n        __tablename__ = \"orders\"\n        id = Column(Integer, primary_key=True)\n        customer_id = Column(Integer, index=True)\n        status = Column(String(20))\n        total_amount = Column(Numeric(10, 2))\n\n        __table_args__ = (\n            Index(\"idx_orders_customer_status\", \"customer_id\", \"status\"),\n        )\n\n\nThe model declares intent; the actual DDL ships through **Alembic** :\n\n\n\n    # alembic/versions/xxxx_add_customer_status_index.py\n    def upgrade():\n        op.create_index(\n            \"idx_orders_customer_status\", \"orders\", [\"customer_id\", \"status\"]\n        )\n\n    def downgrade():\n        op.drop_index(\"idx_orders_customer_status\", table_name=\"orders\")\n\n\n###  Spotting a plan-hostile query\n\nLog or inspect the generated SQL rather than guessing:\n\n\n\n    # Django: see queries actually executed (DEBUG=True)\n    from django.db import connection\n    print(connection.queries[-1][\"sql\"])\n\n    # SQLAlchemy: echo generated SQL, or inspect a specific query's plan\n    engine = create_engine(url, echo=True)\n\n    result = session.execute(\n        text(\"EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = :cid\"),\n        {\"cid\": 48291},\n    )\n    for row in result:\n        print(row)\n\n\nA query that looks fine in Python can compile to a function-wrapped predicate that defeats an index:\n\n\n\n    Order.objects.filter(status__iexact=\"shipped\")   # Django: often compiles to UPPER(status) = 'SHIPPED'\n\n\nThat's the \"function on the column\" case above. Either add a functional index (`CREATE INDEX ON orders (UPPER(status))`) or normalize `status` on write so the query can compare it directly.\n\n##  Anti-Patterns\n\n  * **Indexing every column \"just in case.\"** Each index slows every write on that table and adds storage; index only what real queries filter, join, or sort on.\n  * **Redundant indexes.** A single-column index on `customer_id` is redundant once `(customer_id, status)` exists — the composite index already serves lookups on `customer_id` alone.\n  * **Not indexing foreign keys used in joins.** Postgres does **not** automatically index FK columns; Django adds one for `ForeignKey` fields by default, but a raw SQLAlchemy `ForeignKey` column does not get an index unless you add `index=True` or an explicit `Index`.\n  * **Trusting the model definition alone in production.** Neither `db_index=True` nor SQLAlchemy's `index=True` does anything until a migration actually runs the DDL.\n\n\n\n##  Practical Checklist\n\nSituation | Action\n---|---\nQuery does a Seq Scan on a large table with a selective filter | Add an index on the filtered column(s)\nFiltering/sorting on two columns together | Composite index, most selective / most-used-alone column first\nQuery reads only indexed columns | Add `INCLUDE` columns for an Index Only Scan\n`.filter(x__iexact=...)` or leading `%wildcard%` | Functional index or rewrite the predicate\nBoolean/low-cardinality filter | Partial index, or pair behind a selective column\nDeep pagination (`OFFSET 100000`) | Keyset pagination on an indexed column\nFK column used in joins (SQLAlchemy) | Explicitly set `index=True` — it isn't automatic\nSchema managed by Django/SQLAlchemy models | Ship the DDL via a migration (Django migration / Alembic), not just the model\n\n##  Final Thoughts\n\nIndexes are not a checkbox on a model — they're a targeted trade of write cost and storage for read speed, and the only way to know if the trade pays off is to read the actual query plan. Learn to read `EXPLAIN ANALYZE`, order composite indexes by how you really query, watch for the handful of things that silently defeat an index, and keep the DDL in migrations where it belongs. The ORM writes the SQL; the query plan tells you the truth about it.",
  "title": "Database Indexing and Query Optimization for Python Developers"
}