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 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.”