SQL traps · Chapter 14 of 16

An index the query cannot use

Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Time
  • 3 tasks
  • SQL
  • In the all labs plan

The first 2 chapters of SQL traps are free. This one comes with the all labs plan.

What you build: 3 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    repro.sql: a whole-table sign-in on purpose

    Write repro.sql. It runs in a fresh, empty database where login.sql has just made the table users, its index on email and the function find_login(p). Add a few thousand members with generate_series, and one more, rohan, who signed up as Rohan@MingleSphere.io. Run ANALYZE on users. End with an EXPLAIN of a sign-in for rohan typed in lower case, SELECT * FROM find_login('rohan@minglesphere.io'): its plan is your file's value, as text.

    The check runs your file twice, each time in a fresh database: beside the original login.sql the plan must read the whole table (a Seq Scan on users, with no Index Cond line), and beside a fixed login.sql it must use an index (an Index Cond line). With only a handful of rows reading them all is the right plan, so make enough members, or SET enable_seqscan = off before the EXPLAIN so the answer depends on the indexes alone. Run it with node run.mjs in the terminal.

  2. Task 2

    login.sql: sign-in finds its index

    Fix login.sql so a sign-in can use an index. find_login(p) must still find a member whatever case they signed up with and whatever case they type: stored as Ishaan.M@MingleSphere.io, typed as ishaan.m@minglesphere.io, in capitals or exactly as stored, it finds ishaan. An email nobody has finds nobody, and an underscore or a percent sign in what is typed is just a character, never a pattern. With enable_seqscan off, the plan of SELECT * FROM find_login(...) must have an Index Cond line.

    Keep the table users and its columns, and keep find_login's name, its argument and the rows it returns. Keep it a STABLE SQL function with one SELECT, so Postgres inlines it and EXPLAIN shows the query inside. You may add indexes. The check uses its own members, stored in mixed case, and a lookup that uses an index but cares about case is as wrong as the bug.

  3. Task 3

    login.test.sql: a test that would have caught it

    Write a SQL test file, login.test.sql, that would have caught this bug before the members table grew. Use the lab's ok(condition, name) and is(got, want, name): each call is one test. The check runs your file three times, each in a fresh database where a version of login.sql has just made the table: beside the original login.sql at least one test must fail; beside the fixed login.sql every test must pass and nothing may raise an error; beside a hotfix that compares email as stored (it uses the index, but cares about case) at least one test must fail.

    So add members with emails in mixed case and test both sides: a sign-in typed in another case than the member signed up with finds them, and, with enable_seqscan off, the plan of a sign-in has an Index Cond line. A small PL/pgSQL function that runs EXPLAIN and returns its lines as text lets ok read the plan. Run it with node run.mjs login.test.sql.

What you will learn

  • repro.sql: a whole-table sign-in on purpose
  • login.sql: sign-in finds its index
  • login.test.sql: a test that would have caught it

More in Time

Build "An index the query cannot use" yourself

A year after Chai aur SQL, MingleSphere sends a Sunday digest, pays its creators and shows investors a dashboard, and things break the way they break in real teams. Anya, the lead backend engineer, hands you each ticket. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

The first 2 chapters of SQL traps are free. This one comes with the all labs plan.