Index Basics for Go Developers and How to Read EXPLAIN

Queries that were fine in development and slow in production almost always have the same cause: the database reads far more than it needs to. An index is the tool that fixes it, and EXPLAIN is how you see whether the database used one. This guide explains what an index is, how to read a plan, and walks through the common ways indexes help and the common ways they are silently ignored, with every plan produced from Go 1.25.6 against PostgreSQL 18.6 on a table of a million orders. It is the starting point for the query performance articles in the database cornerstone, and it feeds finding N plus one queries and slow query logging.

What an index is

A table is stored as pages of rows in no useful order. To find the rows for customer 777, PostgreSQL without an index reads every page and checks every row: a sequential scan. A B-tree index is a second, sorted structure that maps column values to row locations, like the index at the back of a book. The database descends the tree in a few steps, finds the matching entries and fetches those rows directly. The work no longer depends on the table's size, only on the number of matches.

The price is paid on writes and on disk. Every index is updated by every insert and by updates that change its columns, and it takes space. The sizes from the lab are at the end of this article. An index you do not use is pure cost.

Reading a plan

EXPLAIN shows the plan the planner chose, and EXPLAIN ANALYZE runs the query and adds what actually happened. Add BUFFERS to see how many 8kB pages each node touched, which is the most stable measure of work, since times vary with caches and load. The lab wraps this in a function that prints the main access node, the buffers and the execution time for each query:

package main

import (
	"context"
	"database/sql"
	"fmt"
	"log"
	"regexp"
	"strings"

	_ "github.com/jackc/pgx/v5/stdlib"
)

const dsn = "postgres://postgres:[email protected]:55432/lab?sslmode=disable"

var db *sql.DB

func must(q string) {
	if _, err := db.Exec(q); err != nil {
		log.Fatalf("%s: %v", q, err)
	}
}

var timing = regexp.MustCompile(`Execution Time: ([0-9.]+) ms`)

// show prints the access method the planner chose, the buffers it touched and how long the query took.
func show(label, q string) {
	rows, err := db.QueryContext(context.Background(), "explain (analyze, buffers, costs off, timing off, summary on) "+q)
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()
	var node, buffers, exec string
	for rows.Next() {
		var line string
		_ = rows.Scan(&line)
		t := strings.TrimSpace(line)
		switch {
		case node == "" && (strings.Contains(t, "Scan") || strings.Contains(t, "Sort")):
			node = strings.TrimPrefix(t, "-> ")
		case buffers == "" && strings.HasPrefix(t, "Buffers:"):
			buffers = strings.TrimPrefix(t, "Buffers: ")
		case timing.MatchString(t):
			exec = timing.FindStringSubmatch(t)[1] + " ms"
		}
	}
	fmt.Printf("%-34s %s\n    buffers: %s | execution: %s\n", label, node, buffers, exec)
}

func main() {
	var err error
	db, err = sql.Open("pgx", dsn)
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
	for _, q := range []string{
		"drop table if exists orders625",
		"create table orders625 (id bigserial primary key, customer_id int not null, status text not null, email text not null, created_at timestamptz not null, total int not null)",
		`insert into orders625 (customer_id, status, email, created_at, total)
		 select (random() * 50000)::int, (array['new','paid','shipped','cancelled'])[1 + (random() * 3)::int],
		        'user' || (random() * 50000)::int || '@Example.test', now() - (random() * 365 || ' days')::interval, (random() * 1000)::int
		 from generate_series(1, 1000000)`,
		"vacuum analyze orders625",
	} {
		must(q)
	}

	fmt.Println("== one customer, no index")
	show("where customer_id = 777", "select * from orders625 where customer_id = 777")
	must("create index orders625_customer on orders625 (customer_id)")
	must("analyze orders625")
	fmt.Println("== with an index on customer_id")
	show("where customer_id = 777", "select * from orders625 where customer_id = 777")

	fmt.Println("== a function on the column hides it from the index")
	must("create index orders625_email on orders625 (email)")
	must("analyze orders625")
	show("where email = '[email protected]'", "select id from orders625 where email = '[email protected]'")
	show("where lower(email) = 'user5@...'", "select id from orders625 where lower(email) = '[email protected]'")
	must("create index orders625_email_lower on orders625 (lower(email))")
	must("analyze orders625")
	show("same, with a lower(email) index", "select id from orders625 where lower(email) = '[email protected]'")

	fmt.Println("== column order in a composite index")
	must("create index orders625_status_created on orders625 (status, created_at)")
	must("analyze orders625")
	show("status and a date range", "select sum(total) from orders625 where status = 'paid' and created_at > now() - interval '3 days'")
	show("the date range alone", "select sum(total) from orders625 where created_at > now() - interval '3 days'")

	fmt.Println("== a covering index and an index only scan")
	show("customer total, heap needed", "select total from orders625 where customer_id = 777")
	must("create index orders625_customer_total on orders625 (customer_id) include (total)")
	must("vacuum analyze orders625")
	show("customer total, covering index", "select total from orders625 where customer_id = 777")

	fmt.Println("== a partial index for a small slice")
	show("open orders, no partial index", "select count(*) from orders625 where status = 'new' and customer_id = 4242")
	must("create index orders625_new on orders625 (customer_id) where status = 'new'")
	must("analyze orders625")
	show("open orders, partial index", "select count(*) from orders625 where status = 'new' and customer_id = 4242")

	fmt.Println("== LIKE: a prefix needs the right operator class, a leading wildcard needs a different index")
	show("like 'user12%', plain index", "select count(*) from orders625 where email like 'user12%'")
	must("create index orders625_email_prefix on orders625 (email text_pattern_ops)")
	must("analyze orders625")
	show("like 'user12%', text_pattern_ops", "select count(*) from orders625 where email like 'user12%'")
	show("like '%[email protected]'", "select count(*) from orders625 where email like '%[email protected]'")

	var size string
	_ = db.QueryRow("select pg_size_pretty(pg_table_size('orders625'))").Scan(&size)
	fmt.Println("== sizes: table", size)
	rows, _ := db.Query("select indexrelname, pg_size_pretty(pg_relation_size(indexrelid)) from pg_stat_user_indexes where relname = 'orders625' order by pg_relation_size(indexrelid) desc")
	defer rows.Close()
	for rows.Next() {
		var n, s string
		_ = rows.Scan(&n, &s)
		fmt.Printf("   %-30s %s\n", n, s)
	}
}

== one customer, no index
where customer_id = 777             Parallel Seq Scan on orders625 (actual rows=3.67 loops=3)
    buffers: shared hit=9379 read=1453 written=131 | execution: 32.563 ms
== with an index on customer_id
where customer_id = 777            Bitmap Heap Scan on orders625 (actual rows=11.00 loops=1)
    buffers: shared hit=11 read=3 | execution: 0.134 ms
== a function on the column hides it from the index
where email = '[email protected]' Bitmap Heap Scan on orders625 (actual rows=23.00 loops=1)
    buffers: shared hit=22 read=4 | execution: 0.245 ms
where lower(email) = 'user5@...'    Parallel Seq Scan on orders625 (actual rows=7.67 loops=3)
    buffers: shared hit=10550 read=282 | execution: 99.781 ms
same, with a lower(email) index    Bitmap Heap Scan on orders625 (actual rows=23.00 loops=1)
    buffers: shared hit=23 read=3 | execution: 0.127 ms
== column order in a composite index
status and a date range             Bitmap Heap Scan on orders625 (actual rows=2748.00 loops=1)
    buffers: shared hit=2426 read=14 | execution: 4.089 ms
the date range alone                Bitmap Heap Scan on orders625 (actual rows=8266.00 loops=1)
    buffers: shared hit=5796 read=33 | execution: 8.959 ms
== a covering index and an index only scan
customer total, heap needed        Bitmap Heap Scan on orders625 (actual rows=11.00 loops=1)
    buffers: shared hit=14 | execution: 0.048 ms
customer total, covering index     Index Only Scan using orders625_customer_total on orders625 (actual rows=11.00 loops=1)
    buffers: shared hit=1 read=3 | execution: 0.083 ms
== a partial index for a small slice
open orders, no partial index       Bitmap Heap Scan on orders625 (actual rows=3.00 loops=1)
    buffers: shared hit=16 read=2 | execution: 0.183 ms
open orders, partial index          Index Only Scan using orders625_new on orders625 (actual rows=3.00 loops=1)
    buffers: shared hit=1 read=2 | execution: 0.100 ms
== LIKE: a prefix needs the right operator class, a leading wildcard needs a different index
like 'user12%', plain index         Parallel Index Only Scan using orders625_email on orders625 (actual rows=7390.00 loops=3)
    buffers: shared hit=5 read=1079 | execution: 38.080 ms
like 'user12%', text_pattern_ops    Index Only Scan using orders625_email_prefix on orders625 (actual rows=22170.00 loops=1)
    buffers: shared hit=1 read=27 | execution: 3.142 ms
like '%[email protected]'             Parallel Index Only Scan using orders625_email_prefix on orders625 (actual rows=0.00 loops=3)
    buffers: shared hit=30 read=1054 | execution: 57.466 ms
== sizes: table 85 MB
   orders625_status_created       32 MB
   orders625_customer_total       21 MB
   orders625_pkey                 21 MB
   orders625_email_lower          8704 kB
   orders625_email                8704 kB
   orders625_email_prefix         8704 kB
   orders625_customer             7800 kB
   orders625_new                  2280 kB

A plan is a tree, read from the innermost, most indented node outward. Each node says how it gets rows (Seq Scan, Index Scan, Bitmap Heap Scan, Index Only Scan), how many rows it produced (actual rows), how many times it ran (loops) and, with BUFFERS, how many pages it touched (shared hit from memory, read from disk). Compare the estimate with the actual rows: a large gap means stale statistics, and analyze fixes that. The program runs vacuum analyze after building each index for that reason.

One lookup, with and without an index

The table has a million orders with a customer_id between 0 and 50,000, so each customer has about 20 orders.

• No index: Parallel Seq Scan on orders625, 9,379 buffers hit and 1,453 read, 32.563ms. PostgreSQL used worker processes to scan the whole table in parallel and found 11 rows.
• With create index on orders625 (customer_id): Bitmap Heap Scan, 14 buffers, 0.134ms. It found the 11 rows by touching 14 pages instead of 10,832.

That is the index's whole value: 243 times faster here, and the gap grows with the table. The planner chose a bitmap scan, which collects the matching row locations from the index and then reads the table pages in order, rather than a plain Index Scan. Both use the index; the choice depends on how many rows match.

An index helps when a query is selective: it returns a small fraction of the table. If a query returns 30 percent of the rows, the planner will rightly prefer a sequential scan, because jumping around the table for that many rows is slower than reading it straight through. An index on a column with few distinct values, such as a boolean or a status with four values, is rarely used for the same reason.

Functions on the column hide the index

The second block is the most common reason "I added an index and nothing changed".

• where email = '[email protected]' used the index on email: Bitmap Heap Scan, 0.245ms.
• where lower(email) = '[email protected]' ignored it. Parallel Seq Scan, 99.781ms. The index is sorted by email, not by lower(email), so it cannot answer a question about the lowercase value.
• After create index on orders625 (lower(email)), the same query took 0.127ms.

Any expression on the indexed column, lower(x), date(created_at), x + 1, a cast, defeats a plain index. Either rewrite the query to compare the raw column (a range on created_at instead of date(created_at) = ...) or create an expression index matching the expression exactly. Comparing a column to a value of a different type has the same effect: a text parameter compared to an integer column forces a cast on every row, which is one reason to pass parameters with the type the column has.

Composite indexes and column order

An index on (status, created_at) is sorted by status, then by date within each status. It answers where status = 'paid' and created_at > ... with a narrow range of the index. The lab queried sum(total) so the heap had to be visited:

• Both columns in the query: Bitmap Heap Scan, 4.089ms, 2,440 buffers.
• The date range alone: still a Bitmap Heap Scan through the same index, 8.959ms, 5,829 buffers.

The textbook rule says the second query cannot use the index because its first column is missing. In PostgreSQL 18 it can: skip scan lets the planner step through each distinct value of a leading column with few distinct values, here the four statuses, and search the second column within each. It worked, and it cost more than twice as much. The rule still holds as a design guide. Put the columns you test with equality first, then the range column, and index for the queries you run most; do not count on skip scan for a column with thousands of distinct values, where it is not chosen.

Covering and partial indexes

Covering. The query select total from orders625 where customer_id = 777 needs a column the index does not hold, so every match is fetched from the table. With create index ... (customer_id) include (total) the index carries total as payload and the plan becomes an Index Only Scan: 4 buffers against 14. The times, 0.083ms and 0.048ms, are both tiny and the covering one was not faster here, because everything was cached. The saving is in pages touched, which turns into time when the table is not in memory. An index only scan also needs a recently vacuumed table to skip the heap, which is why the lab runs vacuum first. The cost is size: the covering index is 21MB against 7.8MB for the plain one.

Partial. If a query is always about a small slice, index only that slice. create index ... (customer_id) where status = 'new' is 2,280 kB, compared with 7,800 kB for the full index on the same column, and the query on open orders became an Index Only Scan at 0.100ms against 0.183ms. Partial indexes also enforce rules, like the unique live email in soft delete. The query's where clause must imply the index's predicate, or the planner cannot use it.

LIKE needs the right index

Text search with like is the third place indexes disappear:

• like 'user12%' with the ordinary index on email: Parallel Index Only Scan reading 1,084 buffers, 38.080ms. It used the index, but by scanning all of it, because a default collation does not sort in a way a prefix pattern can use as a range.
• After create index ... (email text_pattern_ops): Index Only Scan reading 28 buffers, 3.142ms. The operator class makes the index sortable byte by byte, so the prefix becomes a range.
• like '%[email protected]' with a leading wildcard: 57.466ms, again reading the whole index. No B-tree can do this. A trigram index (the pg_trgm extension with a GIN index) can, and for word search see full text search.

The other nodes you will meet

Real queries join, sort and aggregate, and each adds nodes to the plan. A Nested Loop takes each row from the outer input and looks it up in the inner one; with an index on the inner side it is the fast shape for a few outer rows, and with a sequential scan inside it is the disaster behind many slow queries, because the inner scan runs once per outer row. A Hash Join builds an in-memory table from one input and probes it with the other, good for joining many rows. A Merge Join joins two inputs already sorted on the join key. A Sort that reports Sort Method: external merge Disk spilled to disk because it did not fit in work_mem, which is a cue to add an index that returns rows in order or raise the setting for that query. Rows Removed by Filter tells you how many rows a node read and then threw away: a large number means an index could have found only the rows you wanted.

Two habits keep plans readable. Compare actual time and loops: a node that takes 0.05ms and runs 50,000 times costs 2.5 seconds, and the per loop figure hides it. And always ask which node holds most of the time and the buffers; the others are noise. If the slow node is not what you expected, the fix is rarely where you started looking.

What the lab cost in disk

Seven indexes together are bigger than the table's 85 MB. In a real service, drop the ones nobody uses: pg_stat_user_indexes.idx_scan counts how often each index was scanned since statistics were reset, and an index with zero scans over a few weeks is a candidate.

Statistics, and why the planner guesses

The planner does not run the query to choose a plan. It estimates how many rows each step will produce from statistics that analyze collects: how many rows the table has, how many distinct values a column holds, which values are most common. Autovacuum refreshes them in the background, but after a bulk load or a big delete they can be badly out of date, and the planner then picks a nested loop for a million rows or a sequential scan for ten. When explain analyze shows estimated rows far from actual rows, run analyze table_name first and look again before touching any index. For columns whose values are skewed, such as one status that holds 99 percent of the rows, raising the column's statistics target gives the planner a finer picture.

A working method

1. Find the slow query first. Use a slow query log or tracing; see slow query logging.
2. Run explain (analyze, buffers) on the real query with real parameters, against data of production size. A plan on 1,000 rows tells you nothing about a million.
3. Look for the big numbers: a sequential scan on a large table where few rows come back, a node with huge actual rows times loops, a sort that spills to disk, a large gap between estimated and actual rows.
4. Change one thing, create the index, run analyze, explain again and compare buffers.
5. Check the writes. Measure the insert and update cost with the index in place. An index that doubles write time for a query that runs once an hour is a bad trade.
6. Do it safely in production with create index concurrently, as covered in zero downtime migrations.

Do not run explain analyze on a data modifying statement in production without wrapping it in a transaction you roll back, because it executes the statement.

When an index will not help

Small tables are scanned faster than they are indexed. Queries that return a large share of the table need a scan. Wide select * over many rows is bound by the data, not by finding it. Stale statistics make the planner choose badly, and a quick analyze is the fix. And some slowness is in the application: 200 fast queries in a loop, as in the repository pattern article, are slow however good the indexes are. The behaviour of each plan node and the EXPLAIN options are documented in the PostgreSQL manual's chapter on using EXPLAIN, and the index types and operator classes in the indexes chapter, including the skip scan added in version 18.