Indexing Strategy and Design Questions

Direct answer

CREATE INDEX idx_customers_last_name ON customers (last_name);

Whether the optimizer actually uses this index for WHERE last_name = 'Smith' depends on two things it looks up in its own internal statistics, gathered by ANALYZE: how many distinct values last_name has (its cardinality), and how physically scattered matching rows are across the table (their correlation with storage order). With only a handful of distinct last names in a million-row table, a single value still matches a large share of rows, so the optimizer does not fall back to reading the index one row at a time; it builds a Bitmap Heap Scan instead, a middle-ground strategy that is still measurably cheaper than a full sequential scan, just not as dramatically cheaper as an index gets on a truly selective value.

Structured elaboration

What ANALYZE records, and why it drives the decision. ANALYZE samples the table and stores per-column statistics: an estimated distinct-value count (n_distinct), and a correlation value between -1 and 1 measuring how closely a column's sorted order lines up with the table's physical storage order. The optimizer's cost estimate for an index versus a sequential scan is built directly from these numbers, not from re-scanning the real data at planning time, which is precisely why stale statistics (a table that changed a lot since the last ANALYZE) can lead the optimizer to a wrong-for-the-current-data decision.

Reading the real statistics for this exact column, pulled from pg_stats (Postgres's system view exposing what ANALYZE recorded):

SELECT attname, n_distinct, correlation FROM pg_stats
WHERE tablename = 'customers' AND attname = 'last_name';

 attname   | n_distinct | correlation
-----------+------------+-------------
 last_name |         10 |  0.0968962

Only 10 distinct values across 1,000,000 rows: any single value matches roughly 100,000 rows on average. The correlation of about 0.097 (close to 0, meaning essentially uncorrelated) says that rows sharing the same last_name are NOT stored near each other on disk; they're scattered essentially at random across the table. Both facts push the optimizer away from a plain row-by-row index scan (which would mean one scattered, random page read per matching row, roughly 100,000 of them) and toward a bitmap strategy instead.

Why Bitmap Heap Scan specifically, not a plain Index Scan. A Bitmap Index Scan first builds an in-memory map of every matching row's page location by walking the index once, then a Bitmap Heap Scan visits each of those PAGES exactly once (not once per row), collecting all matching rows on a page together instead of jumping back to a page it already visited. That reordering pays off precisely when a query matches a large, physically scattered set of rows, avoiding the repeated-page-visit cost a plain index scan would otherwise pay.

Worked example

Forcing a plain sequential scan for a direct cost comparison against the optimizer's own freely-chosen plan:

SET enable_bitmapscan = off; SET enable_indexscan = off;
EXPLAIN SELECT * FROM customers WHERE last_name = 'Smith';

Seq Scan on customers  (cost=0.00..24483.00 rows=97867 width=60)
  Filter: (last_name = 'Smith'::text)

RESET enable_bitmapscan; RESET enable_indexscan;
EXPLAIN SELECT * FROM customers WHERE last_name = 'Smith';

Bitmap Heap Scan on customers  (cost=1094.89..14301.23 rows=97867 width=60)
  Recheck Cond: (last_name = 'Smith'::text)
  ->  Bitmap Index Scan on idx_customers_last_name  (cost=0.00..1070.43 rows=97867 width=0)
        Index Cond: (last_name = 'Smith'::text)

The optimizer's own cost estimate for the index-assisted plan (14,301) is roughly 42% cheaper than the forced sequential scan (24,483), which is exactly why it picks the bitmap plan on its own: real benefit, just a smaller margin than a highly selective column would give.

Now compare against a genuinely selective predicate on the SAME indexed column, adding a second, narrow condition:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM customers WHERE last_name = 'Smith' AND customer_id BETWEEN 500000 AND 500050;

Index Scan using customers_pkey on customers (cost=0.42..9.48 rows=5 width=60) (actual time=1.312..1.336 rows=3 loops=1)
  Index Cond: ((customer_id >= 500000) AND (customer_id <= 500050))
  Filter: (last_name = 'Smith'::text)
  Rows Removed by Filter: 48
Execution Time: 1.342 ms

Here the optimizer reached for the primary key instead (since the customer_id range is far more selective than last_name alone), and used last_name only as a secondary in-memory filter on the small resulting set. This confirms the underlying logic directly: the optimizer always picks whichever available access path narrows the result the most for the actual combination of predicates in front of it, not necessarily "the index named in the query."

Trade-offs & pitfalls

Don't assume EXPLAIN output is stable over time: if last_name's distribution shifts (a data migration adds many more distinct surnames, say), a re-run of ANALYZE can flip the chosen plan shape entirely, and that's the system working as intended, not a bug to chase. A common mistake is testing an index's usefulness right after creating it, on a small or synthetic dataset, and concluding it "works," without checking whether the SAME index earns its keep at the real cardinality and distribution the production table actually has; the numbers above make clear that low cardinality caps how much even a correctly-built index can help, no matter how well-formed the CREATE INDEX statement is.

Direct answer

A B-tree index on orders.customer_id lets the database jump straight to the rows for a given customer instead of scanning the whole table, which is a large win for point lookups (WHERE customer_id = ?). The same index makes every INSERT, and every UPDATE that touches customer_id, more expensive, because the database must also insert a new entry into the B-tree (a sorted, self-balancing tree structure) in the correct sorted position, not just append to the table. It also costs storage: in a 2,000,000-row orders table, a single-column B-tree index on an integer column measured 24 MB against a 100 MB table, roughly a quarter of the table's own size again.

Structured elaboration

Read side. A B-tree keeps its keys sorted and organized into a shallow tree, so an equality lookup takes on the order of log(row count) comparisons to find the first matching entry, then follows pointers to the matching rows. Without the index, the database has no choice but to read every row (a sequential scan) and evaluate the filter against each one, a cost that grows linearly with table size.

Write side. An INSERT into an indexed table is not just "append a row." The database also has to locate the correct position in the B-tree for the new key and insert an entry there, which occasionally causes a page split (the tree rebalancing itself to make room). Each additional index on the table means one more structure to update, synchronously, inside the same transaction as the row write. This is real, measurable I/O (input/output, meaning disk or cache page reads and writes) and CPU work, not just a theoretical cost.

Storage side. Every index is a second on-disk copy of the indexed columns (plus row pointers), sized independently of the table. A narrow, high-cardinality (many distinct values) integer column indexes relatively cheaply; wider or lower-cardinality columns, or indexes covering multiple columns, cost more.

OLTP vs. BI/reporting trade-off. Online transaction processing (OLTP) workloads (the app's live traffic: creating orders, updating order status) are write-heavy and latency-sensitive on individual rows, so they are the ones that pay the write tax on every index. BI/reporting workloads are read-heavy over large ranges and usually tolerate more indexes, or even a separate, denormalized copy of the data purpose-built for scanning, because they are not the ones absorbing the insert cost. The practical implication: an index that makes a dashboard query fast is not free just because the dashboard is happy. Someone is paying for it on the write path, and for a high-throughput OLTP table that cost is real.

Worked example

Point lookup on orders.customer_id, 2,000,000-row orders table, before any index:

EXPLAIN (ANALYZE, BUFFERS)
SELECT order_id, order_date, total_amount FROM orders WHERE customer_id = 424242;

Gather  (cost=1000.00..24156.07 rows=4 width=18) (actual time=10.296..22.292 rows=4 loops=1)
  Workers Planned: 2
  ->  Parallel Seq Scan on orders  (cost=0.00..23155.67 rows=2 width=18) (actual time=10.020..17.318 rows=1 loops=3)
        Filter: (customer_id = 424242)
        Rows Removed by Filter: 666665
Execution Time: 22.303 ms

After CREATE INDEX idx_orders_customer_id ON orders(customer_id); (measured: 24 MB, versus 100 MB for the table at that point):

Index Scan using idx_orders_customer_id on orders  (cost=0.43..19.73 rows=4 width=18) (actual time=0.016..0.021 rows=4 loops=1)
  Index Cond: (customer_id = 424242)
Execution Time: 0.025 ms

That is the read win: roughly 890x less execution time in this single run, driven by reading a handful of pages instead of scanning the whole table.

Now the write cost. Two identical staging tables were built, one with the same B-tree index on customer_id, one without, and 200,000 new rows were inserted into each with EXPLAIN (ANALYZE, BUFFERS) wrapped around the INSERT:

-- no index on customer_id:
Insert on orders_noidx  (actual time=187.357..187.358 rows=0 loops=1)
  Buffers: shared hit=405843 read=3 dirtied=1830 written=1822
Execution Time: 187.595 ms

-- same 200,000 rows, WITH the index on customer_id:
Insert on orders_idx  (actual time=359.504..359.505 rows=0 loops=1)
  Buffers: shared hit=1007046 read=1205 dirtied=4237 written=3024
Execution Time: 359.746 ms

The buffer count (pages touched) more than doubled (405,843 to 1,007,046, roughly 2.5x), the pages actually dirtied (modified, meaning they must eventually be written back to disk) also increased sharply (1,830 to 4,237, roughly 2.3x), and execution time rose by a comparable margin (187.6 ms to 359.7 ms, roughly 1.9x), because every one of the 200,000 inserts now had to write a table row AND maintain the B-tree. These three multiples are close but not identical (2.5x, 2.3x, 1.9x): "touched" counts every buffer access, including repeat visits to the same hot B-tree pages during the index insert, while "dirtied" only counts each page the first time it is modified in this statement, which is why it grows a little slower than the raw touch count. The buffer counts are the reliable signal here (they reflect real page-level work the engine did), and the timing, from a single, non-benchmarked run in one container, moved in the same direction, which is consistent, not a coincidence.

Trade-offs & pitfalls

The read win and the write cost are not symmetric with volume: the read win applies once per query, however many times it runs; the write cost applies to every single row written, forever, whether or not anyone ever runs the query the index was built for. For a high-throughput OLTP orders table, adding an index because "reporting wants it" is a decision that should weigh insert/update volume just as heavily as the reporting query's benefit; a common resolution is to serve reporting from a read replica or an OLAP (online analytical processing, the read-heavy, large-scan-oriented counterpart to OLTP) store instead of adding write-path cost to the primary. A related pitfall: indexing a column that changes frequently (as opposed to one that is only ever inserted and read) multiplies the write cost, since every UPDATE to that column means removing the old B-tree entry and inserting a new one.

Direct answer

For this join, filter, sort, and limit combined, propose two indexes: one on customers(country) (with customer_id as an included column) to make the join's build side (the smaller input a hash join loads into an in-memory hash table first, before streaming the other, larger side through it to probe for matches) cheap, and reason carefully about whether an index on orders(order_date) actually helps, because it might not. Column order and covering possibilities matter on both sides of the join independently, and, importantly, a real test shows the database correctly declining to use an index on the larger, less-selective side of this particular query, which is worth understanding rather than assuming more indexes always help.

Structured elaboration

Break the query into its access patterns. The query joins orders to customers on customer_id, filters customers.country = 'US' and orders.order_date >= '2025-01-01', then sorts by orders.total_amount DESC and takes the top 50. Each of those four operations (filter customers, filter orders, join, sort-and-limit) is a separate place an index can help, or fail to help.

The customers.country side. country has few distinct values, so filtering on it still returns a meaningful chunk of the table (in this dataset, roughly 20% of customers), but the query only needs customer_id out of the matched rows to feed the join, nothing else. That makes this a strong candidate for a covering index: CREATE INDEX ON customers(country) INCLUDE (customer_id). Since customer_id rides along in the index itself, the database never has to touch the underlying table rows for this side of the join at all (an "index-only scan").

The orders.order_date side, and why more indexes are not automatically better. It's tempting to also index orders(order_date, total_amount DESC) INCLUDE (customer_id) to speed up the filter-and-sort on the orders side. Building that exact index and re-running the query shows the optimizer choosing NOT to use it, sticking with a sequential scan on orders instead, because the order_date >= '2025-01-01' filter matches roughly 27% of the 2,000,000-row table: at that selectivity, reading the table in physical order and filtering as you go is cheaper than the random-access pattern of following an index for that many matches. This is real, verifiable behavior, not a hypothetical: the index exists in the schema, and the planner examined it and rejected it for this query.

Covering possibilities. A covering index is one that includes every column the query needs, so the database can answer entirely from the index without a second trip to the table's underlying storage. The country index above is a clean covering case, because the query only needs one extra column (customer_id) besides the filter column. Wider queries that select many columns are worse covering-index candidates, since the index would have to duplicate most of the table.

Worked example

Before any purpose-built index (only the original customer_id join-key index existed):

Limit (actual time=241.248..245.443 rows=50 loops=1)
  ->  Gather Merge
        ->  Sort
              Sort Key: o.total_amount DESC
              ->  Parallel Hash Join
                    Hash Cond: (o.customer_id = c.customer_id)
                    ->  Parallel Seq Scan on orders o (actual time=0.099..73.662 rows=179244 loops=3)
                          Filter: (order_date >= '2025-01-01'::date)
                    ->  Parallel Hash
                          ->  Parallel Bitmap Heap Scan on customers c (actual time=3.452..103.617 rows=66767 loops=3)
                                Recheck Cond: (country = 'US'::text)
Execution Time: 245.508 ms

After creating both idx_orders_date_amount ON orders(order_date, total_amount DESC) INCLUDE (customer_id) and idx_customers_country_covering ON customers(country) INCLUDE (customer_id):

Limit (actual time=65.363..67.081 rows=50 loops=1)
  ->  Gather Merge
        ->  Sort
              Sort Key: o.total_amount DESC
              ->  Parallel Hash Join
                    Hash Cond: (o.customer_id = c.customer_id)
                    ->  Parallel Seq Scan on orders o (actual time=0.017..24.289 rows=179244 loops=3)
                          Filter: (order_date >= '2025-01-01'::date)
                    ->  Parallel Hash
                          ->  Parallel Index Only Scan using idx_customers_country_covering on customers c (actual time=0.023..4.934 rows=66767 loops=3)
                                Index Cond: (country = 'US'::text)
                                Heap Fetches: 0
Execution Time: 67.108 ms

Note that orders is STILL a Parallel Seq Scan, unchanged, despite the new index existing: the optimizer checked it and declined it. The customers side switched from Bitmap Heap Scan (fetching table pages to recheck the filter and grab customer_id) to Index Only Scan with Heap Fetches: 0 (proof it never touched the table at all), which is where essentially all of the roughly 3.7x improvement in this run (245 ms to 67 ms) came from.

Trade-offs & pitfalls

The instinct to index every column that appears in a WHERE clause is wrong here in one concrete, demonstrated case: the orders.order_date index cost real storage (tens of megabytes) and ongoing write maintenance for zero query benefit, because 27% selectivity is not selective enough to beat a sequential scan on this table. Before shipping a new index, check EXPLAIN to confirm the planner actually chooses it, not just that the query got faster after you added several indexes at once, since you may be crediting the wrong one. A BETWEEN-style date-range variant of this same query pattern (checking a bounded window instead of an open-ended >=) can flip that selectivity calculation entirely, if the window is narrow enough, which is worth re-testing rather than assuming the same conclusion holds.

Direct answer

LOWER(email) = 'abc@example.com' prevents index usage because a plain B-tree index on email stores entries sorted by the raw column value, not by LOWER(email); the database has no way to know a sorted-by-email structure also happens to be sorted by LOWER(email), so it falls back to scanning every row and computing LOWER() on each one to check the filter. The fix that stays sargable (able to use an index seek instead of a full scan) is to build the index on the same expression the query filters on: CREATE INDEX ON customers (LOWER(email)), and then always query through LOWER(email) = LOWER($1), or switch the column to Postgres's citext type so case-insensitive comparison is native and no function wrapping is needed at query time at all.

Why the function wrapper breaks the index

A B-tree index is a sorted structure over the exact value(s) you told it to index. An index on email lets Postgres binary-search for email = 'X' in roughly O(log n) steps (Big-O notation: the cost grows only with the logarithm of the row count n, a small, predictable number of steps, not one step per row) because the tree is sorted by that literal value. LOWER(email) = 'X' asks a different question, "find rows where the lowercased version of email equals X", and the plain index has no ordering with respect to that transformed value; the fact that the raw email value happens to be indexed doesn't help, because the transformation could scramble the sort order arbitrarily. Postgres's planner has no choice but to evaluate LOWER(email) for every row and check it against the literal, which means visiting every row: a sequential scan.

Worked example

CREATE TABLE customers (customer_id int PRIMARY KEY, country text NOT NULL, email text);
-- 50,000 rows, each email unique, e.g. 'customer12345@example.com'
CREATE INDEX idx_customers_email ON customers (email);

EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, email FROM customers WHERE LOWER(email) = 'customer12345@example.com';

With only the plain index on email: Seq Scan on customers (cost=0.00..1157.00) actual time=10.045..22.073 rows=1, Filter: (lower(email) = ...), Rows Removed by Filter: 49999. The plain index on email exists and is valid, and the planner still ignores it completely for this query, exactly as the mechanism above predicts.

CREATE INDEX idx_customers_email_lower ON customers (LOWER(email));

After adding an expression index on LOWER(email): Index Scan using idx_customers_email_lower (cost=0.41..8.43) actual time=0.032..0.033 rows=1, Index Cond: (lower(email) = ...). Cost dropped from 1157 to 8.43, about 137x, because the index is now sorted by exactly the expression the query filters on.

An alternative that avoids needing an expression index or a LOWER() wrapper at query time at all: Postgres's citext extension (case-insensitive text), confirmed available and installable in a stock Postgres 16 instance with CREATE EXTENSION citext;. Changing the column's type to citext makes ordinary equality comparisons case-insensitive natively, so a plain index on that column works for email = 'Some@Example.com' without wrapping either side in a function.

Trade-offs and pitfalls

An expression index on LOWER(email) only helps queries that filter using the exact same expression; WHERE email ILIKE 'X' or WHERE UPPER(email) = 'X' will not use it, so every place in the codebase that does a case-insensitive email lookup needs to agree on the same normalized form, or you end up maintaining several expression indexes for the same underlying intent. citext avoids that coordination problem since the type itself is case-insensitive, but it changes the column's comparison semantics database-wide (sorting, grouping, and uniqueness constraints on that column all become case-insensitive too), which is usually what you want for an email column specifically, but is a real behavior change to review, not a drop-in swap to make casually on a column other logic depends on. Either fix also needs the application's write path updated: an expression index doesn't stop new rows being inserted with mixed-case email, it only makes lookups fast once you normalize the query side, so duplicate-prevention logic still needs to compare normalized values consistently.

Direct answer

A clustered index determines the physical storage order of the table's own rows: the table IS the index, so there can be at most one per table. A non-clustered (secondary) index is a separate structure that stores sorted key values plus a pointer back to wherever the row physically lives, so a table can have many. On an orders table where queries commonly filter by customer_id but the primary key is order_id, this matters directly: if order_id is the clustering key, rows for a given customer are scattered across the table in insertion order, and a customer_id lookup (served by a non-clustered index) has to bounce around many unrelated physical pages to collect matching rows, even with an index in place.

Structured elaboration

Physical layout. Clustering means "rows near each other in the index key are near each other on disk." That locality is what makes range scans and ordered retrieval on the clustering key cheap: a query for a range of the clustering key reads a contiguous run of pages. A non-clustered index buys you a fast way to FIND the right rows, but each row it points to can still be an isolated, expensive random-access read if the table is not physically ordered by anything related to the query.

Read performance. For a query filtering on the clustering key, a clustered structure is close to optimal: locate the start, then read forward. For a query filtering on a non-clustered key, you pay for the index lookup (cheap) plus one scattered heap read per matching row (potentially expensive if there are many matches), because the matches are not adjacent on disk. A realistic BI (business-intelligence) style filter makes this concrete: a report querying WHERE customer_id = ? AND order_date >= ? AND status = ? on this same orders table needs all three predicates satisfied together. If order_id is the clustering key, none of the three helps locate a contiguous physical range, so the query leans entirely on a well-chosen non-clustered composite index (leading with whichever of the three is most selective, typically customer_id) to narrow down candidates before the scattered-row-fetch cost applies; clustering the table itself on order_id does nothing to help this specific filter combination.

Insert/update cost. A clustered table has to keep new rows in roughly the right physical position relative to the clustering key, which is more disruptive on write than simply appending. A non-clustered index only has to maintain its own separate, smaller structure; the base table can still grow by simple append if nothing forces reordering.

Cross-database semantics differ, and the terminology is not interchangeable:

• SQL Server: a table can have at most one clustered index (verified against Microsoft's current documentation). Defining a PRIMARY KEY auto-creates a clustered index unless one already exists, in which case the primary key is enforced via a non-clustered index instead. A table with no clustered index at all is called a heap, and every non-clustered index's leaf entries store a "row locator": a direct pointer to the row on a heap, or the clustered index key itself if the table has a clustered index.
• MySQL, on InnoDB (MySQL's default storage engine): the primary key IS always the clustered index; there is no option to have a table without one. If you don't define a primary key, InnoDB picks the first NOT NULL UNIQUE index it finds, or, failing that, silently generates a hidden synthetic row-ID column to cluster on (verified against MySQL's current documentation). Every secondary index in InnoDB stores the primary key value (not a raw disk pointer) as its row locator, which is why a long or wide primary key inflates the size of every secondary index on the table, not just the primary key's own storage.
• Postgres: there is no persistently-maintained clustered index at all. Table storage (the "heap") is ordered by insertion/update history by default, independent of any index. Postgres does offer a one-time CLUSTER table USING index_name command that physically rewrites the table in that index's order, but nothing keeps it that way afterward; new inserts and updates go back to being unordered relative to the clustering key until you run CLUSTER again (and it takes an exclusive lock while it runs).

Alternative index structures worth naming here. Bitmap indexes (a real, persisted structure in engines like Oracle, distinct from Postgres's runtime bitmap SCAN strategy) target low-cardinality columns and trade concurrency for compactness. Inverted indexes (what Postgres's GIN, short for Generalized Inverted Index, implements) map each distinct value to the set of rows containing it, which is the right tool for multi-valued columns, not a substitute for the clustered/non-clustered choice on a single scalar column like customer_id.

Partitioned tables. When a table is partitioned (split into physically separate sub-tables, typically by date range or hash), clustering and secondary indexing both happen per-partition in Postgres and MySQL: each partition has its own local index copies, and a global index spanning all partitions is either unsupported or a distinct, heavier feature depending on engine and version. Practically: a CLUSTER-style physical reorg, or a clustered-index choice, has to be applied per partition, and a non-clustered index lookup that cannot be pruned to a single partition may have to probe several partitions' worth of local indexes.

Worked example

Demonstrating the physical-locality claim directly, using the orders(order_id, customer_id, ...) table: before any physical reordering, the two rows belonging to customer_id = 424242 sit on different heap pages, identified here by Postgres's internal row identifier (ctid, meaning "page number, row offset within page"):

SELECT customer_id, ctid, order_id FROM orders WHERE customer_id = 424242 ORDER BY order_id;

 customer_id |    ctid    | order_id
-------------+------------+----------
      424242 | (897,131)  |   117921
      424242 | (11994,76) |  1575279

Two different pages (897 and 11994) out of roughly 15,000 total. Then physically reordering the table by the customer_id index:

CLUSTER orders USING idx_orders_customer_id;

SELECT customer_id, ctid, order_id FROM orders WHERE customer_id = 424242 ORDER BY order_id;

 customer_id |   ctid    | order_id
-------------+-----------+----------
      424242 | (6454,47) |   117921
      424242 | (6454,48) |  1575279

Both rows now sit on the same physical page, one row apart. That is what "clustered" means concretely: rows sharing a key value become physical neighbors, which is exactly the locality a customer_id-filtered query benefits from, at the cost of a one-time, exclusive-lock reorganization that Postgres will not maintain automatically going forward.

Trade-offs & pitfalls

Picking order_id as the clustering key (the common default, since it is usually also the primary key and the insert order) optimizes for the wrong access pattern if most real queries filter by customer_id instead; the insert-time convenience of clustering on a monotonically increasing key is not the same thing as clustering on the key your queries actually use. In SQL Server or InnoDB, prefer clustering on (or choosing as primary key) whatever column dominates your range/lookup queries, not necessarily the surrogate ID, when write patterns allow it. In Postgres, remember that CLUSTER is a snapshot, not a standing guarantee: if customer_id locality matters enough to justify a physical reorg, you also need a plan (a maintenance window, or pg_repack for an online rewrite) to redo it periodically as the table churns, or you are back to scattered storage within weeks.