Postgres with Drizzle · Chapter 29 of 40
Speed lanes
By the end of this chapter you can create the index a slow query needs (plain, partial or on an expression), prove with EXPLAIN ANALYZE that Postgres uses it, and list a table's indexes. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Performance & Superpowers
- 5 tasks
- PostgreSQL
- In the all labs plan
The first 2 chapters of Postgres with Drizzle are free. This one comes with the all labs plan.
Why you need this
MingleSphere's login looks members up by email, the notification bell asks for unread notifications, and the moderators' page finds posts by their likes. With a few thousand rows every query was instant. With millions, Rohan's dashboard shows the same queries taking seconds.
Without an index, Postgres has one way to answer WHERE email = ...: read every row of the table and test each one, a sequential scan. No query you can write in Drizzle or SQL changes that. An index is a second, sorted copy of one column (or one expression) that points back to the rows, so Postgres can jump to the answer instead.
What you build: 5 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
createBTreeIndex: an index on post content
Write createBTreeIndex(). It creates a B-tree index named idx_posts_content on the content column of posts (if it does not exist yet), then checks that pg_indexes lists it and reads its kind from pg_am.
Return an object with created (true when pg_indexes has a row for idx_posts_content) and indexType (the amname from pg_am for that index, the string btree).
- Task 2
createPartialIndex: an index on active members' emails
Write createPartialIndex(). It creates an index named idx_active_users on the email column of users, covering only the members who are not deleted (deleted_at is null), if it does not exist yet. Then it checks that pg_indexes lists it.
Return an object with created: true when pg_indexes has a row for idx_active_users. Afterwards: the index's definition in pg_indexes includes the where condition.
- Task 3
createExpressionIndex: an index on the lowercased username
Write createExpressionIndex(). It creates an index named idx_users_lower_username on lower(username) of users (not on username itself), if it does not exist yet. Then it checks that pg_indexes lists it.
Return an object with created: true when pg_indexes has a row for idx_users_lower_username. Afterwards: the index's definition indexes lower(username).
- Task 4
explainAnalyzeQuery: a plan with real timing
Write explainAnalyzeQuery(). It inserts ten members, user0 to user9 (a username and an email each), then runs EXPLAIN (ANALYZE, FORMAT JSON) for selecting all columns of the member whose username is user5.
Return an object with plan (the QUERY PLAN value of the result's first row, an array) and executionTime (the Execution Time of the plan's first element: a number of milliseconds, not a string).
- Task 5
listTableIndexes: every index on a table
Write listTableIndexes(tableName). tableName is a string, a table's name (the check passes users). It reads that table's indexes from pg_indexes.
Return result.rows: objects with indexname and indexdef, ordered by indexname.
What you will learn
- CREATE INDEX: a B-tree on a column, and how to find it in pg_indexes and pg_am.
- Partial indexes: only the rows that pass a WHERE.
- Expression indexes: an index on lower(...) and other expressions, used only by queries with the same expression.
- EXPLAIN ANALYZE: the plan Postgres chose, with real timings.
More in Performance & Superpowers
Build "Speed lanes" yourself
MingleSphereQL grows up: its backend talks to Postgres through Drizzle. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of Postgres with Drizzle are free. This one comes with the all labs plan.