This is the lesson we have been announcing since module 5. When in 05-04 we were arguing about whether to denormalize BiblioRed's schema, the operational rule was blunt: indexes are the first thing to try before denormalizing, and you cannot decide on a denormalization without having read an execution plan first. Here we learn to do both.

The specific problem already has a name and a number. The listing of overdue loans by branch —the one the front desk staff open every morning to call the members who are running late— takes fourteen seconds. On the development laptop, with 3,000 test loans, it took 30 milliseconds. In production, with 2,841,077 rows in loans, it takes fourteen seconds and the person serving has a member standing there watching them.

Fourteen seconds is an interesting number because it sits in the worst possible place: it is too much to work with and too little for anybody to declare it a fault. It is simply "the system is slow". And out of that sentence come all the unnecessary redesigns in the world.

In this lesson we will see what an index is and how it manages to turn millions of comparisons into four disk reads; what an index costs, because they are not free; the types that exist and when to use each one; what to index and what not to; how to read an execution plan line by line; the antipatterns that disable an index without anybody noticing; and the complete case of those fourteen seconds, with its diagnosis, its solution and its measurement. At the end we will also have the correct order of intervention for a slow query, which is what closes the circle opened in 05-04.

1. What an index is: two analogies that work
2. How a B-tree works
3. The numbers: how many reads it really saves
4. The cost of an index: space, writes and planning
5. CREATE INDEX: syntax, unique indexes and CONCURRENTLY
6. Composite indexes and the leftmost prefix rule
7. Partial indexes
8. Expression indexes
9. An overview of index types in PostgreSQL
10. What to index and what not to
11. Reading an execution plan: EXPLAIN and EXPLAIN ANALYZE
12. The nodes that show up again and again
13. Cost, estimates and the alarm signal
14. Statistics and ANALYZE
15. Complete case: the fourteen seconds of the overdue listing
16. Antipatterns that disable an index
17. How you really optimize: the order of intervention
18. Maintenance and indexes outside PostgreSQL

Definition. An index is an auxiliary data structure, maintained automatically by the management system, that makes it possible to locate the rows satisfying a condition without scanning the whole table.

Two analogies, and both are from a library, which comes in very handy for BiblioRed.

The first: a book's index. To find out which pages talk about "normalization" in a 900-page manual, you do not read all 900. You go to the index at the back, look up the word —which is in alphabetical order, so you find it in seconds— and read "normalization: 412, 418-431, 507". Three pieces of information that take you straight to what you want.

The three elements of that analogy are exactly those of a database index:

The second: the library itself. BiblioRed's 40,000 copies are placed on the shelves in a specific order —by subject and shelf mark—. That order is an index: it lets you find a history book without walking through the four branches. But notice that there is only one possible physical order: the books cannot be simultaneously ordered by subject, by author and by year.

That is why libraries also have a catalog: cards ordered by author, others by title, others by subject. Each catalog is an additional index that does not change where the book is, it only adds one more way of finding it. And each one has to be updated when a new copy comes in.

That is exactly a table's situation: the data is in one order (the one imposed by the INSERTs), and each index is an additional catalog offering another way of reaching it, with its cost in space and maintenance.

95% of the indexes you will create are B-trees (in their B+ variant). It is the default type in PostgreSQL and in practically every management system. Understanding how it works explains almost all of its behavior.

A B-tree is a balanced tree in which:

• Each node contains ordered keys and pointers.
• The internal nodes only serve to steer the search: they say "if you are looking for something smaller than X, go this way".
• The leaf nodes contain the keys and the pointers to the real rows.
• The leaves are chained to each other, which allows range scans without going back up.
• The tree is balanced: all the leaves are at the same depth, so every lookup costs the same.

An index on loans(due_date), drawn at reduced scale:

To look up due_date = '2024-03-15':

1. At the root: 2024-03-15 is between 2023-04-01 and 2025-02-01 → go down through the middle internal node.
2. At the internal node: it is between 2023-11-01 and 2024-07-01 → go down to the corresponding leaf.
3. At the leaf: find the exact key and get the row's ctid.
4. Read that page of the table.

Four accesses. Over 2.8 million rows.

Every node of a B-tree occupies one disk page —8 KB in PostgreSQL— and many keys fit in that page. With a date (8 bytes) plus a pointer (6 bytes), around 500 entries fit per node in 8 KB. That number is called the branching factor.

With three levels you address 125 million rows. The 2.8 million rows of loans fit comfortably in three levels, and in practice the upper levels are permanently in the memory cache (the buffer manager from 01-04), so the real lookup costs one or two disk reads, not four.

The formula: the number of levels grows with the logarithm base 500 of the number of rows. Multiplying the data by 500 adds a single level. That is why an index stays fast as the table grows: it does not scale with the number of rows, it scales with their logarithm.

Let us put figures on loans, with 2,841,077 rows and about 120 bytes per row.

The difference is not 20%: it is four orders of magnitude. That is why the first reaction to a slow query must be to look for an index, not to redesign the schema.

But there is a fundamental piece of small print, and it explains many apparently strange planner decisions:

An index only pays off if the query returns a small fraction of the table. If it is going to return 40% of the rows, it is faster to scan the whole table sequentially than to make a million random jumps.

The reason is physical: reading 52,736 consecutive pages takes advantage of read-ahead by the system and the disk; reading 400,000 scattered pages takes advantage of nothing. The break-even point in PostgreSQL is usually between 5% and 10% of the table, and it is decided by the planner using the statistics from section 14.

That is why you will see plans with Seq Scan on indexed tables and think the index "is not being used". Often it is that the planner has calculated, rightly, that it is not worth it.

Here is why the answer to "should I index this column?" is not always yes.

Four indexes on a 412 MB table add up to 244 MB: more than half the size of the data. In a database with many indexes it is common for the indexes to take up more than the tables. That does not only cost disk: it costs cache memory, which is a much scarcer resource, and every index competes with the data for it.

This is the cost that really matters.

A typical measurement over a load of 100,000 loans:

More than three times as long. And loans is a table that is written to constantly during front desk hours.

That is where the standard practice for bulk loads comes from: drop the indexes, load, recreate them. Rebuilding an index from scratch over data that is already there is much faster than maintaining it row by row.

Every additional index is one more alternative the planner has to evaluate. With two or three indexes it is imperceptible; with fifteen on the same table, planning time starts to be noticeable in queries that run thousands of times a minute.

An index is not free. It is created for a specific query that runs often enough to justify its write cost, and it is dropped when that query stops existing.

And to know whether one is superfluous, PostgreSQL keeps count:

That idx_loans_loan_date with idx_scan = 0 is an index that only costs: 61 MB of disk, a tree to maintain on every INSERT, and zero benefit. A clear candidate for DROP INDEX, after checking that the statistics cover a representative period (do not drop it the day after restarting the server, or without looking at whether the annual report uses it).

The basic form:

Naming convention: idx_<table>_<columns>. It is not mandatory —PostgreSQL generates a name if you do not give one— but an index with no recognizable name is an index nobody will dare to drop three years from now.

Here it is worth clearing up a relationship that confuses a lot of people, and that links back to the constraints of 04-04:

Every UNIQUE constraint and every PRIMARY KEY is implemented internally with a unique index. When you declare the constraint, the index is created on its own.

The recommendation: use the UNIQUE constraint when you are expressing a business rule —it is documented in the schema, and other tables can reference it— and the unique index only when you need what the constraint cannot give: expressions or partial conditions.

Creating an index on a production table has a serious problem:

This statement takes a SHARE lock on loans for its entire run. With 2.8 million rows it can take a minute, and during that minute nobody can write to the table: the four front desks stand still. It is the locking mechanism of lesson 06-02 in action.

The alternative:

It takes considerably longer —it makes two passes over the table— but it does not block writes. Its conditions:

• It cannot be run inside an explicit transaction (remember the exceptions to transactional DDL from 06-01).
• If it fails halfway, it leaves an invalid index that has to be dropped and redone. Detecting them:

In production, always CONCURRENTLY. In a development environment or in a maintenance window, the normal form is faster.

An index can span several columns:

And here comes the rule that generates the most confusion in the whole lesson.

Leftmost prefix rule. A composite index on (A, B, C) can be used for queries that filter by A, by A and B, or by A, B and C. It cannot be used effectively for queries that filter only by B, only by C, or by B and C.

Because the index is ordered first by A, and within each value of A, by B. It is exactly the order of a phone book sorted by surname and then by first name:

• Looking up "Alsina, Marta" is immediate.
• Looking up every "Alsina" is immediate.
• Looking up every "Marta" in the book forces you to read the whole thing, because the Martas are scattered across every page.

An honest nuance: PostgreSQL can use a composite index without the prefix by scanning the whole index (an index scan with no start condition), if the index is much smaller than the table. But it is a desperate resort and much slower than a proper index. The practical rule still holds.

An applied example. These two BiblioRed queries:

A single index (member_id, loan_date) serves both: equality first, range afterwards. Also creating an index on (member_id) would be redundant and would only cost.

PostgreSQL lets you add columns to the index that do not take part in the search but do appear in the result:

It is for achieving an Index Only Scan (section 12): if all the columns the query needs are in the index, there is no need to read the table. It is a notable optimization in heavily repeated queries.

Partial index. An index that only includes the rows satisfying a WHERE condition. It is smaller, faster and cheaper to maintain.

It is probably PostgreSQL's most underused feature, and it is exactly what BiblioRed needs.

Look at the numbers:

Out of 2.8 million loans, only 42,017 are open: 1.5%. And every front desk query —a member's current loans, overdue by branch, return notices— filters by return_date IS NULL. The other 2.8 million rows are history nobody consults day to day.

Size comparison:

61 MB against less than 1 MB. An index 60 times smaller that:

• fits entirely in the memory cache and is queried practically without touching disk;
• is only updated when a loan is created or returned, not on every modification of historical rows;
• and answers the hot query just as well.

PostgreSQL will only use the partial index if it can prove that the query implies its condition. The index's condition must appear in the WHERE in a recognizable form:

Mental rule: if a frequent query always carries the same filter on a status, a flag or an IS NULL, that filter should be in the index definition, not just in the query.

An ordinary index on title is no use for searching LOWER(title), because the index stores the titles as they are written and the query asks for something else. The solution is to index the expression:

And the query must use exactly the same expression:

Other common cases:

Watch the double parentheses in the last example: when the expression is not a function call, it has to be wrapped in parentheses of its own.

And a warning: the function must be immutable (IMMUTABLE), that is, it must always return the same thing for the same input. That is why you cannot index now() nor a function that depends on the session's locale settings:

In section 16 we will see the dark side of this: a function applied in the WHERE on an indexed column disables the index, and it is one of the most frequent antipatterns.

B-tree solves almost everything, but it is worth knowing what is available and what for:

BiblioRed's web catalog lets you search by words in the title and the summary. With LIKE '%word%' no index can help (section 16). With full-text search, it can:

The generated column keeps the vector up to date without triggers —it is the technique from 04-04— and the GIN index makes it queryable in milliseconds.

A BRIN index does not store one entry per row, but a summary per block of pages: the minimum and maximum value of each range. It is tiny, and it works well when the physical order of the rows corresponds to the column's value, which is exactly what happens with a history table where rows are inserted chronologically.

61 MB against 48 kB. In exchange, it is less precise: it discards whole blocks, not rows, so you have to filter afterwards. For the annual report of loans by quarter it is perfect; for finding one specific loan, it is not.

Rule: if you do not know which to choose, it is B-tree. The other types answer very specific needs and you recognize them because B-tree cannot speed up the operator you need.

This surprises almost everybody, and it is one of the most frequent causes of unexplained slowness:

PostgreSQL creates an index automatically for the PRIMARY KEY and for UNIQUE constraints, but NOT for foreign keys. The index is on the referenced side (the parent's primary key), not on the referencing side.

Consequences of not indexing loans.member_id:

1. Every JOIN with members scans the whole of loans.
2. Every DELETE or UPDATE of the key in members scans the whole of loans to check the referential integrity of 02-06. Deleting a member from a 2.8-million-row table can take seconds.

Finding unindexed foreign keys:

Four unindexed foreign keys in BiblioRed. All four are immediate candidates.

Practical rule: index every foreign key, unless you have checked that the child table is small and is not used in JOINs.

members.active is a boolean. Out of 12,000 members, 10,900 are active: 91%.

The planner ignores the index, and it is right to: retrieving 91% of the rows via an index means jumping around almost the whole table in random order, which is slower than reading it straight through. The index exists, takes up space, is maintained on every write and is never used.

The same column with the selectivity inverted is useful, and that is why the partial index is the answer:

This second form is the good one. The active column contributes no selectivity, but it restricts the index to the interesting rows, and the indexed columns are the ones that are actually searched.

The execution plan is the strategy the optimizer —that box in the 01-04 diagram— has chosen to answer the query. Reading it is the central skill of this lesson: without it, optimizing is guessing.

Important warning. EXPLAIN ANALYZE on a DELETE deletes the rows. To analyze a write statement without side effects, wrap it in a transaction and roll it back:

BEGIN;
EXPLAIN ANALYZE DELETE FROM loans WHERE loan_id = 88301;
ROLLBACK;

The complete form it is worth always using:

A plan is a tree of nodes, and it is read from the inside out and from the bottom up:

• Each -> marks a level of nesting.
• The most indented nodes run first.
• Each node consumes the rows its children produce and hands rows to its parent.
• The first line is the last step: what is returned to the client.

The Bitmap Heap Scan deserves an explanation because it puzzles people. When the query is going to return, say, 40,000 rows out of 2.8 million, an Index Scan would make 40,000 random jumps across the disk. The bitmap solves the problem in two phases: first it collects all the addresses from the index, then it sorts them by physical position and reads the table from beginning to end visiting only the necessary pages. It is the middle ground between index and sequential scan, and seeing it usually means the planner is doing the right thing.

The most common alarm signal: a Nested Loop whose outer side returns many more rows than estimated. If the planner expected 5 and there are 50,000, it will run 50,000 lookups instead of 5. It is the number one cause of queries that "suddenly" go from milliseconds to minutes.

When you see external merge Disk: 84320kB, the sort has gone to disk. It is often fixed by raising work_mem for that query:

Every node carries two blocks of numbers:

Three essential warnings about cost:

1. Cost is not milliseconds. It is an arbitrary unit in which 1.0 is, by convention, reading one page sequentially. It is for comparing plans with each other, not for predicting time.
2. Costs are cumulative: a node's cost includes its children's. The query's total cost is the one on the first line.
3. With loops > 1, the times and the rows are PER ITERATION. A node with actual time=0.012..0.014 rows=1 loops=42017 did not take 0.014 ms: it took about 590 ms in total. It is the most frequent reading error and the one that makes people look for the problem in the wrong place.

Always compare the estimated rows= with the real rows= on every node. A difference of more than an order of magnitude is the most valuable diagnosis an execution plan gives you.

When the planner estimates 5 rows and there are 50,000, all its later decisions are badly founded: it chose Nested Loop because it believed it would iterate five times. And the plan is not bad because of the algorithm, it is bad because of the information.

The planner does not look at the data: it looks at a statistical summary of the data. If the summary is wrong, the plan is wrong.

What PostgreSQL stores about each column:

The autovacuum process runs ANALYZE automatically when a table accumulates enough changes (by default, 10% of the rows). You have to force it by hand in three situations:

1. After a bulk load. The statistics are from before the load.
2. After creating an expression index. It needs its own statistics for the expression.
3. Before measuring a plan, so as not to diagnose on the basis of stale information.

If a column has a very irregular distribution, you can ask for more detail:

The default value is 100 (100 frequent values and 100 histogram buckets). Raising it improves the estimates and makes ANALYZE a little more expensive.

The planner assumes the columns are independent. When they are not, it gets things badly wrong. A real BiblioRed example: copies.branch_id and copies.status are not independent —the East branch is small and lends little, so almost all its copies are available—.

It is a little-known and very effective tool when the symptom is "the estimate and the reality only diverge when I filter by two columns at once".

Let us go to the real problem, from beginning to end.

Execution Time: 14025.663 ms — The number that has to come down. 14 seconds.

Seq Scan on loans l ... (actual time=0.048..13755.902 rows=42017 loops=1) — Here is 98% of the time. The node takes 13.7 of the 14 seconds all by itself. Everything else is noise.

Rows Removed by Filter: 2799060 — The most eloquent line in the plan. PostgreSQL has read 2,841,077 rows and discarded 2,799,060. It has done 98.5% of the work for nothing.

Buffers: shared hit=1284 read=51452 — 51,452 pages read from disk (read) and only 1,284 found in cache (hit). That is 402 MB of disk for a query that returns 312 rows.

rows=41960 estimated against rows=42017 real — There is no statistics problem here: the estimate is excellent. The planner knew exactly what it was doing; it chose Seq Scan because it had no alternative. There is no index that helps.

Seq Scan on copies c ... Rows Removed by Filter: 30002 — The second problem, a much smaller one: it scans the 40,000 copies to keep the 9,998 belonging to branch 1. That is 29 ms, it is not the drama, but an index is missing there too.

Sort Method: quicksort Memory: 76kB — The sort is irrelevant: 312 rows in memory.

Hash Join — Correct. With 42,017 rows on one side and 9,998 on the other, with no indexes, the hash is the right choice.

A diagnosis is written in one sentence:

The query scans the 2.8 million loans to keep the 42,017 that are open and overdue —1.5%— because there is no index on return_date or on due_date. Secondarily, it scans the 40,000 copies for lack of an index on branch_id.

Notice what the problem is not: it is not the four JOINs, it is not the normalized schema, it is not the ORDER BY, it is not "the table is very big". It is a missing index.

Why partial? Because the front desk queries always carry return_date IS NULL, and this way the index goes from 61 MB to less than 1 MB, fits entirely in memory and is only touched when lending and returning, not on every modification of historical rows.

Fourteen seconds turned into forty milliseconds. Without touching the schema. Without denormalizing. Without a single new table. Two CREATE INDEX statements.

Two observations about the new plan:

• The Hash Join has become a Nested Loop. As access to loans got cheaper, the planner changed strategy: now it walks the 42,017 open loans and, for each one, looks up its copy by primary key. It is a perfect example that creating an index does not just speed up one node: it changes the whole plan.
• loops=42017 on the copies node. Remember section 13: that actual time=0.000..0.000 is per iteration. The node runs 42,017 times, and its real contribution is about 17 ms out of the 39. That is fine, but it is where we would look if we needed to keep coming down.

Suppose the front desk needed to get below 40 ms —which is doubtful, but it serves to close the circle from 05-04—. The next step would not be to create a summary table. It would be to notice that the query filters by branch and that the branch lives in copies, not in loans, which forces it to walk the 42,017 loans of all four branches to keep those of one.

The solution would be to duplicate branch_id in loans —technique 3 from 05-04, "duplicating an attribute to avoid a JOIN"— and index (branch_id, due_date) WHERE return_date IS NULL. That would bring the query to the order of a millisecond.

And notice the order in which we got there: measure, index, measure again, and only then consider touching the schema, with the exact number of what is gained. That is exactly the procedure 05-04 demanded and that the next section formalizes.

The index exists, it is the right one, and yet the plan shows Seq Scan. It is almost always one of these five.

The index stores dates; the query asks about years. They are different things.

The alternative, if rewriting were not possible, is an expression index on extract(year FROM loan_date). But rewriting is almost always better: the range also works for any other period.

A very frequent variant of the same mistake:

Note the nuance: here the function does not disappear, but it matches the index's, and that is why it works.

A B-tree sorts by the start of the string. Searching by what is in the middle forces you to look at everything, just like looking in a dictionary for every word containing "ap".

An important detail: for LIKE 'The Map%' to use the index when the database is not in the C collation, the index has to be created with the right operator class:

It is the same trap as antipattern 1 in disguise: member_id::text is a function over the column.

It happens a lot with badly configured drivers that send every parameter as text, and with columns that store numbers in VARCHAR —an additional reason to choose types properly, as 04-04 said—.

PostgreSQL sometimes resolves the OR with a BitmapOr over two indexes, and then there is no need to rewrite. When it does not, the UNION is the way out.

A related and very common case:

44 pages against 4, and Heap Fetches: 0 confirms the table was not touched. Besides, SELECT * transfers columns nobody uses, breaks applications when somebody adds a column, and makes it illegible what each query really needs.

Here we close the circle opened in 05-04.

You do not optimize what you have not measured. Find the guilty query, not the suspicious one. The tool is the pg_stat_statements extension:

Look at the previous table carefully, because it contains this section's whole lesson:

The second row is the surprising one. A 1.36 ms query looks perfect, but it runs 884,102 times and adds up to twenty minutes of server time. Bringing it down to 0.4 ms would save more than optimizing the quarterly report forty times over.

And the third teaches the opposite: a two-second report looks scandalous, but it runs 44 times a year. Optimizing it is wasted time, however ugly its SQL.

The metric that decides is total_exec_time, not mean_exec_time. Total cost = unit cost × frequency.

From least invasive to most. Do not move to the next rung without having exhausted the previous one and without having measured.

The first four rungs do not touch the data, cannot introduce inconsistency and can be undone in a minute. From the fifth onwards, each one adds a permanent maintenance obligation.

And this lesson's case confirms it: fourteen seconds solved on rung 3, with two reversible statements. The temptation to jump straight to rung 6 —"let's make an overdue loans table that gets refreshed every night"— would have cost a trigger, a nightly process, a risk of the figures drifting apart, data hours out of date and an endless argument about why the listing does not include the loan that has just fallen due. All of that to be slower than a 992 kB partial index.

Two techniques that are sometimes proposed as the solution to a slow query and that almost never are:

• Partitioning splits a big table into fragments by range or by list. It helps with maintenance (deleting a whole year means dropping a partition) and with queries that filter by the partition key. It does not replace an index.
• Replication spreads the read load across several servers, and it is the technique we saw in 03-01. It does not make a query faster: it allows more queries to run at the same time. Sending the monthly report to a read-only replica is an excellent idea; it will not make it take less time.

Neither of the two fixes a query that is slow for lack of an index. They only multiply the hardware needed to keep on doing it wrong.

With the MVCC of 06-02, indexes also accumulate dead entries and become bloated. A bloated index takes up more than it should and its lookups touch more pages.

Estimating the problem:

Rebuilding:

The CONCURRENTLY is essential: without it, REINDEX blocks writes to the table, with the effect of 06-02 on the four front desks.

Careful: rebuilding indexes is not a routine task. It is done when bloat has been verified, not "just in case".

The concept is identical; the syntax changes.

And the equivalent of EXPLAIN ANALYZE:

What you have to look at is the same idea as in PostgreSQL: COLLSCAN instead of IXSCAN is MongoDB's Seq Scan, and totalDocsExamined much bigger than nReturned is the equivalent of Rows Removed by Filter.

The important conclusion: indexes are not a relational peculiarity. They are the universal answer to the problem of finding a few items among many, and every data system, without exception, has its version.

Indexing everything "just in case". Every index costs space, writes, cache and planning time. Review idx_scan in pg_stat_user_indexes and drop the ones that have gone months without being used.

Not indexing the foreign keys. PostgreSQL does not do it for you. It is the most frequent cause of slow JOINs and of deletes that take seconds. Run the detection query from section 10 against your database today.

Creating the index in production without CONCURRENTLY. It blocks writes for the whole creation. On a big table, it is a self-inflicted outage.

Confusing cost with milliseconds. Cost is an internal unit for comparing plans. The time is in actual time, and only with ANALYZE.

Forgetting that with loops > 1 the times are per iteration. Always multiply by loops before deciding a node is innocent.

Optimizing the ugliest query instead of the most costly one. Sort by total_exec_time in pg_stat_statements and attack what is at the top, even if its SQL is impeccable.

Wrapping the column in a function and expecting the index to work. WHERE extract(year FROM date) = 2026 does not use the index on date. Rewrite it as a range.

Using EXPLAIN ANALYZE on an UPDATE or a DELETE in production without a transaction. It really runs it. BEGIN; ... ROLLBACK;.

Measuring with development data. The 3,000 loans on your laptop fit in memory and make every plan look good. Plans are only meaningful with realistic volume and statistics.

Creating an index and not measuring afterwards. Sometimes the planner still does not use it —because of selectivity, types or statistics— and you are left with the cost and no benefit. Always run EXPLAIN ANALYZE again.

Final tip: write the number down. "Before 14,025 ms, after 39 ms, with idx_loans_open." That sentence in the change log is worth more than any explanation, lets you check a year from now whether the index is still needed, and is the only thing that turns a hunch into an engineering decision.

These three queries are the most heavily run in BiblioRed's web catalog and front desk. For each one, state which index you would create, why in that column order, and whether it would be partial or not.

Interpret this execution plan. State: which node is the problematic one, how much real time it consumes, what the diagnosis is and what intervention you would propose (with the rung from section 17 it corresponds to).

These three BiblioRed queries use no index at all even though the right indexes exist. Identify each one's antipattern and rewrite it. The available indexes are: loans(loan_date), members(lower(email)), materials(title text_pattern_ops) and materials USING gin (search).

Solution 1

(a) A member's loan history

• Column order: member_id first because it is the equality condition and it is very selective (11,842 distinct values over 2.8 million rows). loan_date afterwards because it takes part in the ORDER BY.
• DESC in the index: it allows the ORDER BY ... DESC to be resolved by walking the index in its natural order, eliminating the Sort node. With LIMIT 20, the management system reads 20 entries and stops.
• Not partial: it is a history, and it explicitly queries loans that have already been returned. Restricting it to the open ones would break the use case.

Expected verification:

No Sort node: that is the sign that the index's order has been taken advantage of.

(b) Available copies of a material at a branch

• Order: material_id first because it is far more selective (thousands of materials against four branches). With only four branches, branch_id barely discards any rows and must not go first.
• Partial on status = 'available': it is the fixed condition of the web catalog's hot query, and status has low cardinality, so as an indexed column it would be useless (section 10). As an index condition it is perfect: it reduces it to the copies that are actually lendable and avoids maintaining it when the statuses of the others change.
• There is no need to include status among the columns: the index's condition already guarantees it.

(c) A member's pending fines

• Order: member_id (equality, selective) and issue_date (for the ORDER BY).
• Partial: of the 48,000 historical fines only about 300 are pending. The index goes from megabytes to kilobytes and is only touched when a fine is issued or collected.
• You could add INCLUDE (reason, amount) to get an Index Only Scan, since the query only asks for those columns. With 300 rows the gain is marginal, but it is the right reasoning.

Solution 2

Problematic node: the Index Scan using idx_loans_member on loans l.

Real time consumed: here is the trap. The node shows actual time=224.402..224.622 with loops=41. Those 224 ms are per iteration, so the total is 41 × 224.6 ≈ 9,209 ms, that is, practically the whole query's 9,215 ms. Anybody who reads "224 ms" and takes it as acceptable will look for the problem where it is not.

Diagnosis: the idx_loans_member index is being used for the join condition, but the return_date IS NULL filter is applied afterwards, over the rows already retrieved from the table. The Rows Removed by Filter: 218 line says so: for each of the 41 members, 219 loans are read from the table to keep 1. That is 8,979 page accesses, most of them from disk.

There is a second, smaller problem: the Seq Scan on members with lower(last_name) walks the 12,000 members (4 ms). It is not the drama, but it gives away a missing expression index.

Proposed intervention, rung 3 of section 17 (create an index):

With the partial index, each iteration of the loop returns the open loan directly without reading the 218 returned ones. The node would go from 224 ms to microseconds, and the whole query to the order of a millisecond.

Additional observation: you can also see that the estimate (rows=12) falls short of reality (rows=41) on the Seq Scan on members. It is not serious here, but if the difference grew, the Nested Loop would stop being the right choice. An ANALYZE after creating the expression index will improve that estimate too.

Solution 3

(a) A function over the column (antipattern 1)

to_char(loan_date, 'YYYY-MM') turns the date into text, so the index on loan_date cannot come into play.

A note about the upper bound: < '2026-08-01' is used and not <= '2026-07-31'. If the column were a TIMESTAMP instead of a DATE, <= '2026-07-31' would leave out the whole of the last day from 00:00:01 onwards. The half-open range is correct in both cases, and it is a habit that avoids silent errors.

(b) An expression index with a different expression (a variant of antipattern 1)

The index is on lower(email), but the query compares email as it is, and moreover with the value in upper case. They do not match.

Or, better still, normalizing the value in the application before sending it:

The WHERE expression must be identical to the index's. It is the rule that makes expression indexes work, and the one that breaks them when it is forgotten.

(c) LIKE with a leading wildcard (antipattern 2)

The text_pattern_ops index works for prefixes (LIKE 'time%'), not for substrings.

An added advantage: full-text search applies stemming, so "time" also finds "times", and it does not find false positives such as "sometimes" that LIKE '%time%' would return.

If the literal substring really were needed, the alternative is a trigram index:

The fourteen seconds no longer exist. And what matters is not that they are now forty milliseconds, but how we got there: by measuring, reading an execution plan, finding the line that said Rows Removed by Filter: 2799060, and creating two reversible indexes. Without touching the schema, without denormalizing, without a new table and without an architecture argument.

We have understood the mechanism from the inside. A B-tree with a branching factor of about 500 entries per node addresses 125 million rows in three levels, and that is the mathematical reason an index stays fast as the table grows: it does not scale with the number of rows, it scales with their logarithm. We have also seen the other side, which is more often forgotten: an index costs space, costs cache, triples the time of a bulk load and, if nobody uses it, only costs. That is why idx_scan = 0 in pg_stat_user_indexes is an invitation to DROP INDEX.

We have gone through the tools: unique indexes and their exact relationship with the UNIQUE constraints of 04-04; composite indexes and the leftmost prefix rule, which you understand once and for all with the phone book sorted by surname and first name; partial indexes, which in BiblioRed turned 61 MB into 992 kB because only 1.5% of loans are open; expression indexes, with the condition that the WHERE repeat the exact expression; and the overview of GIN for the catalog search, GiST for the event overlaps we already knew from 04-04, and BRIN for the date-ordered history, with its 48 kB against 61 MB.

We have learned to read a plan from the bottom up and from the inside out; to tell Seq Scan from Index Scan, from Index Only Scan and from Bitmap Heap Scan; to recognize when a Nested Loop is a good idea and when it is a catastrophe; not to confuse cost with milliseconds; to multiply by loops before absolving a node; and above all to look at the most valuable alarm signal of all: the distance between the estimated rows and the real ones, which almost always points to stale statistics or correlated columns.

And we have formalized the method. Measure with pg_stat_statements and attack the query with the most total time, not the worst-looking one —that 1.36 ms query that runs 884,102 times costs more than the two-second quarterly report—. Then, the order of intervention: rewrite → statistics → index → configuration → schema → denormalize → cache, without skipping rungs and measuring between one and the next. The first four cannot spoil the data; from the fifth onwards, each one adds a permanent obligation. This is exactly the commitment we made in 05-04, and now we have the tools to honor it.

One last thing remains, and it is the one that separates a database from an accident waiting to happen.

BiblioRed's system is now correct —the schema is normalized and transactions guarantee that operations happen in full— and it is fast. But there are still two questions from Vallmar's council department left unanswered since the first day of this module: who can look up the phone numbers and email addresses of the 12,000 members, and what exactly would happen if the disk died tonight.

Neither of the two is fixed with an index. Lesson 06-04, Security, Permissions and Backups, closes the module and the course's theoretical block: authentication and PostgreSQL's roles, with pg_hba.conf and why the trust method must never leave your laptop; authorization with GRANT and REVOKE, and the principle of least privilege applied to three concrete BiblioRed roles —front desk, management and web application—; views and row-level security so that each branch sees only its own; SQL injection, how it happens in the catalog search and why parameterized queries are the only defense that works; encryption in transit and at rest, and the correct handling of passwords; members' personal data, with what technology can contribute and what falls to a compliance professional; and the backup block, where the WAL log we studied in 06-01 will reappear turned into the tool that lets you recover the database at the instant before the disaster.