PostgreSQL Query Performance Lab: indexes and vector search

About the PostgreSQL Query Performance Lab

This lab runs a PostgreSQL build inside your browser tab and fills it with sample orders and customers. The tables start with no indexes and no primary keys, so the first run of most queries has to read every row.

Each time you press Run, the query is executed with EXPLAIN ANALYZE and the plan is drawn step by step: which table was read, how many rows were read and thrown away, how long each step took and whether an index was used.

Compare index choices builds several candidate indexes for the query, one at a time, and measures each: B-tree or hash, BRIN, GIN, different columns and column orders, partial indexes and a primary key. You see which ones PostgreSQL used, the query time, rows read, pages touched, index size and build time in one table.

You can also build your own index from the Tables panel, choosing the type, the columns and their order, uniqueness, extra stored columns and a condition, then add it to the comparison or create it for real.

With pgvector, the lab also has a table of 384-number vectors, grouped by topic the way real text embeddings are. The vector experiments compare an exact scan with HNSW and IVFFlat indexes and measure recall: how many of the true nearest rows each index finds, next to how fast it is. The vectors are synthetic, so they show how the indexes behave rather than meaningful search results; for real documents and questions use the pgvector Playground.

Timings come from a database running inside a browser tab, so absolute numbers are slower than a real server. The shape of the plan, the rows read and the difference an index makes are the same.

PostgreSQL index types you can compare

B-tree index

The default index. Use it for =, <, >, BETWEEN, IN, ORDER BY and LIKE with a fixed start. It keeps values sorted, so it can also return rows in order without a sort step.

Hash index

Supports only = on a single column. It cannot sort or answer range conditions, and is rarely smaller or faster than a B-tree.

BRIN index

Stores only the lowest and highest value for each block of table pages, so it is tiny. It works when rows sit on disk in the same order as the column, such as timestamps in an append-only table.

GIN index

Indexes every element inside a value. Use it for arrays, JSONB and full-text search with operators such as @> and &&.

Multi-column index

One index on several columns. Put columns compared with = first and the range or sort column last. The order of columns decides which queries can use it.

Partial index

Indexes only the rows that match a WHERE condition, for example only pending orders. Smaller and faster to maintain when queries always ask for that slice.

Covering index (INCLUDE)

Stores extra columns in the index so a query can be answered from the index alone, as an index-only scan, without visiting the table.

Expression index

Indexes the result of an expression such as lower(email). Needed when the query applies a function to the column.

HNSW index (pgvector)

A graph that links each vector to its near neighbours, for fast approximate nearest-neighbour search. Settings: m and ef_construction when built, hnsw.ef_search when queried. The operator class (vector_cosine_ops, vector_l2_ops, vector_ip_ops) must match the distance operator in ORDER BY.

IVFFlat index (pgvector)

Splits the vectors into lists around centres found from the data; a search checks only the ivfflat.probes nearest lists. Quick to build and small, but built from existing data, so create it after loading.

Unique index and primary key

Guarantee that no two rows share the value. A primary key is a unique, not-null index that identifies each row, and is what joins and lookups by id use.

How to read an EXPLAIN ANALYZE plan

Seq Scan

A full table scan. Every row is read and checked against the filter.

Index Scan

The index finds the matching rows, then each row is fetched from the table.

Index Only Scan

Everything the query needs is in the index, so the table is not read.

Bitmap Index Scan and Bitmap Heap Scan

The index lists the pages that hold matches, then those pages are read once each. Chosen when an index matches many rows.

Nested Loop

For each row on one side of a join, matching rows are looked up on the other side. Fast when the lookup uses an index.

Hash Join

One side of the join is loaded into an in-memory hash table and the other side is matched against it.

Sort

Rows are sorted for ORDER BY, GROUP BY or a merge join. An index that already holds the order removes this step.

Index Scan using an HNSW or IVFFlat index

An approximate nearest-neighbour search: the index returns rows in order of distance and the LIMIT stops it early. Seen only with ORDER BY a distance to a constant vector and a LIMIT.

Rows Removed by Filter

Rows that were read and then thrown away. A large number here usually means an index is missing or is on the wrong column.

Index experiments in the lab

1. Find rows by a column. A filter on a column with no index makes PostgreSQL read the whole table.
2. Primary key lookup. A primary key gives every row a unique, indexed address.
3. B-tree or BRIN for a date range. Orders are stored in date order, so a tiny BRIN index can do the job of a much larger B-tree.
4. Join two tables. Joins need an index on the column they match on, as well as on the filter.
5. Newest rows first. ORDER BY with LIMIT sorts everything unless an index already keeps the order.
6. Two conditions: which columns, which order? One index on both columns beats two separate ones, and column order matters.
7. When an index is ignored. If most rows match, reading the whole table is faster than using an index.
8. Function on a column. Wrapping a column in a function hides it from a normal index.
9. LIKE and wildcards. A pattern that starts with % cannot use a B-tree index. A fixed start can.
10. Array column: GIN. Looking inside an array needs a GIN index. A B-tree on the same column is no use.
11. Covering index. If the index holds every column the query needs, PostgreSQL never visits the table.
12. What indexes cost. Every index must be updated on each insert, so writes get slower as you add indexes.
13. Your own query. Write any query against orders and customers and see how PostgreSQL runs it.

Vector search experiments (pgvector)

Choose Vector search above the editor. For RAG queries on real documents and embeddings, use the pgvector Playground.

1. Vector search: exact scan or index? Find the 10 nearest of thousands of 384-number vectors (pgvector). Without an index every vector is compared; HNSW and IVFFlat are faster but approximate.
2. Speed against recall: ef_search and probes. An approximate index looks at a limited number of candidates. More candidates: closer to the exact answer, but slower.
3. Why the vector index is ignored. A vector index needs ORDER BY distance LIMIT k, an operator that matches its operator class, and the question vector as a single value.
4. Filtered vector search: fewer rows than LIMIT. With an approximate index, WHERE is checked after the index hands over its candidates, so a rare category can leave fewer than 10 rows.
5. Half the size: halfvec. halfvec stores each number in 2 bytes instead of 4. An HNSW index on embedding::halfvec(384) is about half the size, usually with the same results.
6. Hybrid search: GIN for the text part. Hybrid search mixes full-text and vector search. The full-text filter needs its own GIN index on to_tsvector.
7. What a vector index costs on insert. Every insert must also be added to the HNSW graph, which is much slower than adding to a B-tree.
8. Your own vector query. Write any query against items (id, category, title, created_at, embedding vector(384)) and queries (id, embedding) and see how PostgreSQL runs it.

Questions

More playgrounds

PostgreSQL Playground · SQLite Playground · DuckDB Playground · Mongo Query Playground · All playgrounds