Skip to main content
Databases

PostgreSQL indexes without tears.

An index is the table of contents for your data. Without one, PostgreSQL reads the whole book to find one sentence. Here's how to write a good table of contents, and how to tell when you've written too many.

By Anvexis Stack Engineering8 min read

Index types you'll actually use

TypeUse forExample
B-treeEquality and rangesWHERE created_at > now() - interval '7 days'
CompositeFilters on several columns(partner_id, state, date_order DESC)
PartialA hot subset of rowsWHERE state = 'draft'
ExpressionSearching on a computed valuelower(email)
GINJSONB, arrays, full-text searchpayload @> '{"type": "refund"}'

How PostgreSQL finds rows

Without an index, PostgreSQL answers a query by reading every row of the table and checking each one, a sequential scan. That's perfectly fine for small tables and for queries that need most of the rows anyway. It becomes a problem on large tables when you only need a handful of rows.

An index is a separate, sorted structure that points to rows. The PostgreSQL documentation describes the trade-off in two sentences: an index lets the server find and retrieve specific rows much faster, but indexes add overhead to the database system as a whole, so they should be used sensiblySource [1]. The planner decides whether to use an index from statistics about your data, which is why the same query can use an index on one database and ignore it on another.

Find the queries worth fixing first

Don't guess. The pg_stat_statements extension tracks planning and execution statistics for every SQL statement the server runsSource [5]. Sort by total execution time and you'll usually find that a small number of queries dominate. Those are where an index pays off; a query that runs once a day in 200 milliseconds is not worth an index that slows every write.

The extension has to be added to shared_preload_libraries, which requires a restartSource [5], so turn it on before you're in the middle of a performance problem. It's the single most useful line in a production postgresql.conf.

Read the plan before adding the index

explain.sqlsql
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM sale_order
WHERE partner_id = 42 AND state = 'sale';

-- Look for "Seq Scan" on big tables and "Rows Removed by Filter".
CREATE INDEX CONCURRENTLY sale_order_partner_state_idx
  ON sale_order (partner_id, state);

Reading EXPLAIN without panic

EXPLAIN ANALYZE runs the query and shows the plan PostgreSQL chose and how long each step actually took. Look for sequential scans on large tables, estimated row counts that are wildly different from actual counts, and steps that dominate the total time. Each points to a missing index, stale statistics, or a query that needs rewriting.

Remember that ANALYZE really executes the statement, so wrap data-changing queries in a transaction you roll back. Change one thing at a time and run the plan again. Indexes that look helpful in theory sometimes go unused, and the plan is the only reliable judge.

Composite indexes and column order

A composite index covers several columns in a specific order. It works like a phone book sorted by last name, then first name: great for finding everyone named Smith, or Smith, John, but useless for finding everyone named John.

The documentation gives the exact rule for B-tree indexes: they're most efficient when there are constraints on the leading columns, and equality constraints on leading columns plus an inequality on the first column without an equality are what limit the portion of the index that's scannedSource [2]. In practice, put columns used with equality first, then the column used for ranges or sorting. An index on partner, state, then order date serves “this customer's confirmed orders, newest first” very well. Reverse the order and it may barely help.

Don't overdo it. The same page advises using multicolumn indexes sparingly, notes that a single-column index is sufficient in most situations, and says indexes with more than three columns are unlikely to help unless usage of the table is extremely stylizedSource [2].

Partial and expression indexes

A partial index covers only the rows matching a condition, called its predicateSource [3]. The main reason to use one is to avoid indexing common values: a query for a value that accounts for more than a few percent of the table won't use the index anyway, so leaving those rows out makes the index smaller, speeds up the queries that do use it, and makes many updates cheaperSource [3]. If most queries look at draft or open orders and most rows are closed, a partial index on the open ones is a great fit.

Expression indexes cover the result of a function, such as the lowercase version of an email address. They let queries that search on that expression use an index, which a plain index on the column wouldn't allow. Match the expression in the query exactly, or the planner can't use it.

B-tree indexes sort values, which is perfect for equality and ranges but useless for questions like “which documents contain this key?” or “which rows mention this word?”. GIN indexes are built for those: they index the elements inside a value, such as the keys of a JSONB document, the items of an array, or the words of a full-text search vector.

They're larger and slower to update than B-tree indexes, so add them for queries you actually run, such as containment searches on a JSONB payload column used by an integration log. For simple lookups on one field inside a JSON document, an expression index on that field is often smaller and faster than a GIN index over the whole document.

Covering indexes and index-only scans

When an index contains every column a query needs, PostgreSQL can sometimes answer from the index alone without visiting the table, an index-only scan. You can add extra, non-searched columns to a B-tree index with INCLUDE to make that possible for a hot query, such as listing order numbers and totals for one customer.

Index-only scans depend on the visibility map that vacuum maintains, so they work best on tables where autovacuum keeps up. Use INCLUDE sparingly: every extra column makes the index bigger and every write a little slower.

Index myths

Myth: More indexes are always faster.

Reality: Every index slows writes and uses disk. Indexes add overhead to the whole system, so remove the unused onesSource [1].

Myth: Column order doesn't matter.

Reality: In composite indexes it matters a lot: equality columns first, then the first range columnSource [2].

Myth: An index on every column covers every query.

Reality: Single-column indexes can't always be combined efficiently. One well-ordered composite index often beats several separate ones.

Creating indexes on a busy database

A standard CREATE INDEX blocks writes to the table until it finishes, which on a large, busy table means orders stop saving. With the CONCURRENTLY option, PostgreSQL builds the index without taking locks that prevent concurrent inserts, updates, or deletesSource [4].

It has caveats worth knowing before you run it in production. A concurrent build does more total work and takes significantly longer; it can't run inside a transaction block; and if it fails part-way, for example on a deadlock or a uniqueness violation, it leaves behind an invalid index that queries ignore but writes still maintainSource [4]. The documented fix is to drop the invalid index and run the concurrent build againSource [4]. Check for invalid indexes after any failed build.

When indexes hurt

Every index has to be updated on every insert, update, and delete that touches its columns. Tables with many indexes write more slowly, take more disk space, and need more vacuuming. Unused indexes are pure cost.

PostgreSQL tracks how often each index is scanned in its statistics views. Review those numbers periodically and drop indexes that are never used, after checking they aren't backing a unique or primary-key constraint, and remembering that statistics on a replica are separate from the primary. Drop them concurrently too, so you don't block normal work.

Statistics keep the planner honest

PostgreSQL chooses plans using statistics about how values are distributed in each table. When a table changes a lot, such as after a big import, those statistics can go stale and the planner may pick a slow plan even though a good index exists. Running ANALYZE on the table refreshes them.

Autovacuum normally handles this, but very large or very busy tables sometimes need tuned settings so statistics and cleanup keep pace with the workload. If a query suddenly gets slow after a data load, refresh the statistics before you add anything.

Indexes in Odoo specifically

Odoo creates indexes for many fields automatically, but custom fields used in searches, filters, and domains often lack them. Large models such as stock moves, journal items, and messages are where missing indexes hurt most, and a single unindexed field used in a record rule can slow every query on the model.

In custom modules, declare indexes on the fields you search by rather than adding them by hand in the database, so they survive upgrades and are documented in code. For the wider picture of Odoo performance, including workers and database settings, see the architecture behind high-performance Odoo, or let our Odoo performance tuning team do the measuring.

A safe indexing routine

  • Find the expensive queries with pg_stat_statements
  • Read the plan with EXPLAIN (ANALYZE, BUFFERS)
  • Add one targeted index, created concurrently
  • Check for invalid indexes if the build fails
  • Re-check the plan and the timing
  • Review index usage and drop unused indexes every few months

Frequently asked questions

How many indexes should a table have?
As few as serve your important queries. Every index costs write time, disk, and vacuum workSource [1]. Start from the slowest frequent queries, add targeted indexes, and remove ones that statistics show are never used.
Why isn't PostgreSQL using my index?
Common reasons: the query returns a large share of the table so a sequential scan is cheaper, the index columns are in the wrong order for the filterSource [2], the query wraps the column in a function the index doesn't match, or statistics are stale. EXPLAIN ANALYZE shows which.
Is CREATE INDEX CONCURRENTLY always safe?
It avoids blocking writes, but it takes longer, can't run in a transaction block, and can leave an invalid index if it fails, which you must drop and rebuildSource [4]. Plan it for a quieter period on very large tables.
Should we add indexes directly in the Odoo database?
Prefer declaring them on fields in your custom modules, so they're versioned with the code and survive upgrades. Ad-hoc database indexes are easy to forget and can be dropped during migrations.

Sources

  1. [1]Chapter 11. Indexes, PostgreSQL 18 documentation (opens in a new tab)The PostgreSQL Global Development Group
  2. [2]Multicolumn Indexes, PostgreSQL 18 documentation (opens in a new tab)The PostgreSQL Global Development Group
  3. [3]Partial Indexes, PostgreSQL 18 documentation (opens in a new tab)The PostgreSQL Global Development Group
  4. [4]CREATE INDEX, PostgreSQL 18 documentation (opens in a new tab)The PostgreSQL Global Development Group
  5. [5]pg_stat_statements, PostgreSQL 18 documentation (opens in a new tab)The PostgreSQL Global Development Group

Written by Anvexis Stack

We build Odoo, AI, web, and cloud systems for growing companies, and we write about the patterns we use. This is engineering guidance, not a product guarantee: check vendor details for your version before you decide. Last reviewed .