# Improving Query Performance with Indexes using Prisma: Introduction

URL: https://www.prisma.io/blog/improving-query-performance-using-indexes-1-zuLNZwBkuL
Published: 2022-09-06
Updated: 2026-07-08
Authors: Alex Ruheni
Tags: education
Series: Improving query performance with indexes using Prisma
Description: A database index is a secondary data structure that speeds up reads by reducing search scope. Learn the main index types, what they cost, and when to add one.

A database index is a smaller, secondary data structure the database uses to find matching rows without scanning the whole table. Indexes are the most common way to speed up slow read queries, and they come with a cost on writes and storage. This article covers the fundamentals: what indexes are, how the database decides to use them, the main index types, and when adding one is worth it. Parts [two](https://www.prisma.io/blog/improving-query-performance-using-indexes-2-MyoiJNMFTsfq) and [three](https://www.prisma.io/blog/improving-query-performance-using-indexes-3-kduk351qv1) of this series put the theory into practice with B-tree and hash indexes in a [Prisma ORM](https://www.prisma.io/orm) schema.

> **Updated (July 2026):** Revised as part of a refresh of this series. The fundamentals in this introduction are version independent; the hands-on measurements in [part two](https://www.prisma.io/blog/improving-query-performance-using-indexes-2-MyoiJNMFTsfq) were verified end-to-end on Prisma ORM 7.8 and PostgreSQL 17.

## What we can learn about databases from a book library [#what-we-can-learn-about-databases-from-a-book-library]

A database is like a library. Books in a library are usually well organized, much like data in a database. Both provide great structures for storing vast amounts of information for later retrieval.

Continuing with the library analogy, libraries store large amounts of data. The more books you have, the longer the time it takes to find a book. The time taken to retrieve a book can be influenced by the strategy for **storing** and **searching** books in the library.

### Storing books in smaller shelves (partitioning) [#storing-books-in-smaller-shelves-partitioning]

You may naively organize the books into one big shelf alphabetically according to the title, for example. One strategy for retrieving a book faster would be storing them in smaller bookshelves in alphabetical ranges.

This is similar to [*partitioning*](https://www.postgresql.org/docs/current/ddl-partitioning.html) a table in a database. A table is split into smaller chunks by a *partition key* which is included in a query. A partition key is the column used to split the table. The database narrows down the data to be queried using the partition key.

![Table Partitioning](https://www.prisma.io/blog/improving-query-performance-using-indexes-1-zuLNZwBkuL/imgs/table-partitioning.png)

### More people searching leads to faster searches (query parallelization) [#more-people-searching-leads-to-faster-searches-query-parallelization]

Another strategy for faster book retrieval would be to ask more people to join your search and distribute their work in a structured way. Some databases support a similar pattern where the database server can assign more compute or CPU cores to execute a query. This is known as [*parallel query execution*](https://www.postgresql.org/docs/current/parallel-query.html).

![Parallelized database query](https://www.prisma.io/blog/improving-query-performance-using-indexes-1-zuLNZwBkuL/imgs/query-parallelization.png)

### Using a catalog to store book metadata for faster retrieval (indexes) [#using-a-catalog-to-store-book-metadata-for-faster-retrieval-indexes]

Another pattern you could use to retrieve a book is using a catalog. The catalog is an external, organized structure separate from the existing shelves containing the *metadata* of the books in the library. The metadata contains information such as titles, authors, subjects, publication dates, and the book's location. Databases provide similar structures called indexes.

![Database index](https://www.prisma.io/blog/improving-query-performance-using-indexes-1-zuLNZwBkuL/imgs/index.png)

### Conclusion: You can speed up search time by reducing search scope [#conclusion-you-can-speed-up-search-time-by-reducing-search-scope]

The common denominator for all three strategies is retrieving data faster by reducing the scope of the search.

The rest of the article will explore database indexes: what they are and how they work, benefits, cost, the anatomy of a query, and how to identify slow database queries.

## What are database indexes? [#what-are-database-indexes]

A database index is a smaller, secondary data structure used by the database server to store a subset of a table's data. Indexes are commonly used to improve the read performance of a given table.

Indexes contain key-value pairs:

* **key**: the column(s) that will be used to create an index
* **value**: and a pointer to the record in the specific table

You can index more than one column in a table. For example, if you have a table called `User` with 3 columns:`id`, `firstName`, and `lastName`, you can create one index on the `firstName` and `lastName` columns.

The length of an index may be identical to the original column in the table but indexes have a smaller width.

### Types of database indexes [#types-of-database-indexes]

There are different types of indexes, each one suitable for a different use case. Some of the general categories of indexes include but are not limited to:

* **Index (default)**: a normal, non-unique index that does not enforce any constraints on your data
* **Primary keys**: used to uniquely identify a row in a table
* **Unique indexes**: used to enforce uniqueness of a value in a column, preventing duplicate values
* **Full-text indexes**: used on text columns and enables [full-text search](https://www.postgresql.org/docs/current/textsearch-intro.html)

There are more specialized types of indexes different databases support. Some of the other types include hash indexes, GIN indexes for PostgreSQL, and clustered indexes for SQL Server.

> This series covers the two most common index types in practice: [B-tree indexes in part two](https://www.prisma.io/blog/improving-query-performance-using-indexes-2-MyoiJNMFTsfq) and [hash indexes in part three](https://www.prisma.io/blog/improving-query-performance-using-indexes-3-kduk351qv1), both defined directly in a Prisma schema.

### Anatomy of a database query [#anatomy-of-a-database-query]

When the database receives a query, it first creates a *query plan*. A query plan is a list of instructions the database needs to follow to execute a given query.

The query plan specifies how the database intends to retrieve records. The database chooses the most optimal query strategy.

You can prefix your SQL query with `EXPLAIN` to see information about your database's query plan. Josh Berkus, a former PostgreSQL core team member, explains the query planner in "Explaining EXPLAIN", a talk from a Prisma online meetup:

[Watch the video on YouTube](https://www.youtube.com/watch?v=ZOZglRUjfFI)

### Sequential scans go through the entire table [#sequential-scans-go-through-the-entire-table]

One path the database might choose is look through each row in a table to find the records matching the filters. This is known as a *sequential scan* or *full table scan*.

If you're working with a large dataset, sequential scans will slow down your queries. The time taken to find a record is influenced by the size of your dataset. The bigger the dataset, the longer it takes to retrieve a record.

### Index scans first look up metadata to find the record faster [#index-scans-first-look-up-metadata-to-find-the-record-faster]

Another path the database might choose is query the index, matching the filter with the values available in the index. The database then "consults" the original table for more data such as more columns. This is known as an *index scan*.

### Index-only scans look up the record in the index directly [#index-only-scans-look-up-the-record-in-the-index-directly]

The database might also choose to return the matching records from the index without even "consulting" the original table. This is known as an *index-only scan*. This can happen when the columns being returned and being filtered on already exist in the index. Here's an example that could make use of an index-only scan.

```sql
SELECT "firstName" FROM "User" WHERE "firstName" = 'Jimmy';
```

Other database providers such as PostgreSQL support other types of query plans such as [bitmap heap scan](https://pganalyze.com/docs/explain/scan-nodes/bitmap-heap-scan) and [bitmap index scans](https://pganalyze.com/docs/explain/scan-nodes/bitmap-index-scan). You will see a bitmap index scan in action in [part two](https://www.prisma.io/blog/improving-query-performance-using-indexes-2-MyoiJNMFTsfq) of this series.

### The price you pay for faster reads [#the-price-you-pay-for-faster-reads]

The universal consensus is that indexes are great for speeding up read performance. However, indexes come at a cost.

You will incur additional overhead for your write operations. This is because every write requires the index to be updated as well.

The other cost of indexes is that they require additional resources from the database server for maintenance. Indexes require additional storage, memory, and IO from the database server.

**A general rule of thumb is to use indexes sparingly and on columns that are queried frequently.** You should also select the appropriate index type based on your requirements.

## Frequently asked questions [#frequently-asked-questions]

### What is a database index?

A database index is a smaller, secondary data structure that stores a subset of a table's data as key-value pairs: the indexed column values as keys, and pointers to the full rows as values. The database uses it to find matching rows without scanning the entire table, which turns slow full-table reads into fast lookups.

### What is the difference between a sequential scan and an index scan?

In a sequential scan the database reads every row in the table and filters out the ones that match, so the cost grows linearly with table size. In an index scan the database first looks up matching entries in the index and then fetches only those rows from the table. Run `EXPLAIN` on a query to see which plan the database chose.

### Why not index every column?

Every index must be updated on every insert, update, or delete that touches its column, so each one adds write overhead, storage, and maintenance cost. Index the columns your queries filter and sort on frequently, and skip the rest.

### What types of database indexes are there?

The common categories are the default non-unique index, primary keys, unique indexes, and full-text indexes. Databases also offer specialized types such as B-tree indexes (the default in PostgreSQL, good for equality and range queries), hash indexes (equality only), and GIN indexes for values like JSON and arrays.

## Summary and next steps [#summary-and-next-steps]

In this article, you learned what a database index is, the different types of database indexes, the anatomy of a database query, and the cost of using database indexes to optimize your queries.

In [part two](https://www.prisma.io/blog/improving-query-performance-using-indexes-2-MyoiJNMFTsfq), you will put this into practice: declare a B-tree index with `@@index` in a Prisma schema, measure a query over 500,000 rows before and after with `EXPLAIN ANALYZE`, and watch it get about 80x faster. Because the index lives in your Prisma schema, it is declared in code, reviewed with the rest of your changes, and applied the same way in every environment, from a local [Prisma Postgres](https://www.prisma.io/docs/postgres) database to production. [Part three](https://www.prisma.io/blog/improving-query-performance-using-indexes-3-kduk351qv1) does the same for hash indexes.