Adding an index made my query slower. Here's why.

Repo: github.com/bitorsic/export-service

I built a Go service that exports large datasets to CSV in the
background. Along the way, adding an index to a slow query made it
slower, not faster. Here's what happened and what actually fixed it.

The query

The service exports a seller's order history, joined across four tables:

SELECT o.order_id, o.order_status, o.order_purchase_timestamp,
       c.customer_id, c.customer_city, c.customer_state,
       p.product_category,
       oi.price, oi.freight_value
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
JOIN customers c ON c.customer_id = o.customer_id
JOIN products p ON p.product_id = oi.product_id
WHERE oi.seller_id = $1
  AND o.order_purchase_timestamp BETWEEN $2 AND $3

Seeded with a few million rows, skewed so a small number of sellers
account for most of the order volume. That skew is what makes this a real
problem rather than a query that's fast regardless of what you do to it.

Baseline: no index

EXPLAIN ANALYZE against a high-volume seller showed a full sequential
scan on order_items: 1.5 million rows scanned to find the ~36,000
belonging to this seller. Same story on orders, 141,000 rows thrown out
by the date filter.

Execution time: ~160ms.

First attempt

CREATE INDEX idx_order_items_seller_id ON order_items(seller_id);

Execution time: ~228ms. Worse.

Why

The index found the matching rows fast, about 9ms to locate ~72,000 row
pointers. But an index only stores the indexed column and a pointer to
the row's location on disk. For every match, Postgres still had to jump
to that location to fetch the other columns the query needed.

Those rows were inserted in random order, so they were scattered across
the table rather than sitting near each other. Scattered random reads
cost more than the original sequential scan, which reads disk in one
pass. An index only pays off when the query can be answered from the
index alone, or the matching rows are physically clustered. Neither was
true here.

The fix

A covering index. Normally an index only stores the column you filtered
on plus a pointer to the rest of the row, so a match still means a trip
to the table to fetch everything else. A covering index also stores the
extra columns the query needs, directly in the index itself. If every
column the query asks for is in the index, Postgres never has to touch
the table at all.

CREATE INDEX idx_order_items_seller_covering
  ON order_items(seller_id)
  INCLUDE (order_id, product_id, price, freight_value);

Now Postgres answers the query from the index alone. Plan changed from a
bitmap heap scan (look up the row, then go fetch it) to an index only
scan (everything needed is already in the index).

Execution time: ~108ms.

Version	Execution time
No index	~160ms
Plain index	~228ms
Covering index	~108ms

The tradeoff

The covering index isn't free. Every insert into order_items now
updates a wider index, so writes get more expensive, not just reads
faster.

Fine here since the data is bulk loaded once and only read after. In a
system with continuous writes, that cost is ongoing. Worth measuring
insert throughput with and without the index, and weighing it against how
often the table is actually read. A table written to constantly but
rarely queried probably isn't worth indexing this heavily. A table read
constantly and written to occasionally almost certainly is.

One more thing, from load testing

Load tested the service afterward with hey, pushing well past what it
could realistically process. Two requests failed with:

could not resize shared memory segment: No space left on device

Not an application bug. A hash join is how Postgres combines two tables
when there's no useful index to walk, it builds a lookup table (a hash
table) out of one side of the join in memory, then scans the other side
checking each row against it. A parallel hash join splits that work
across multiple processes, each building and probing its own chunk, which
is why several of them can be running at once under load. That work needs
shared memory to coordinate across processes, and running enough of these
concurrently, under the load I was throwing at it, exhausted Docker's
default shared memory allocation for the container. Real constraint,
infrastructure not code. Fix in production is tuning shm_size for
expected concurrent query load.

Takeaway

"Add an index" isn't a complete fix on its own. What matters is whether
the index lets the database skip the table entirely, or just makes
finding the right rows faster while leaving the expensive part, fetching
them, untouched. Check the query plan before assuming an index will help.
Sometimes it won't. Occasionally it makes things worse.

Comments

[Mads Hansen]

One important nuance: a covering index makes an index-only scan possible, not guaranteed. PostgreSQL still visits the heap for pages that are not marked all-visible in the visibility map, so EXPLAIN (ANALYZE, BUFFERS) and the Heap Fetches counter are the evidence. On a write-heavy table, VACUUM cadence can make the same plan behave very differently over time. The seller skew also deserves separate plans for high- and low-volume sellers; with prepared statements from Go, a generic cached plan can be fine for one and terrible for the other. I’d benchmark representative parameter buckets, compare estimated versus actual rows at each node, and watch whether the driver/server switches from custom to generic plans. Finally, the date predicate is on orders, so the end-to-end improvement may also need an index aligned with that filter/join path rather than widening only order_items. The best takeaway is plan + buffers + workload distribution—not merely “covering beats plain.”