We replaced some database views with direct joins. Cleaner code, same results, no schema changes. Catalogue search got 54% slower at p90.

Nothing about the indexes had changed. That's what made it interesting.

Partial indexes come with a condition attached

A partial index only covers some rows: 

CREATE INDEX idx_store_company_brand
    ON brand_store_area_relationship (store_id, company_id, brand_id)
 WHERE is_active = true AND is_deleted = false;

This is usually a good trade. The index is smaller, cheaper to maintain, and most queries only want live rows anyway.

The catch is that Postgres can only use it when it can prove your query is restricted to the same rows. That proof comes from your WHERE clause matching the index predicate. Drop those conditions and the index becomes invisible — not slower, ineligible.

The old view had is_active and is_deleted baked into its definition. The hand-written join filtered differently and didn't carry them in the same place. Same rows out. Different proof available to the planner.

This is why the failure is so quiet. Nobody dropped an index. Nobody wrote a slow query. A condition moved, and an optimisation silently switched itself off. There is no warning for this — a plan that no longer uses an index looks exactly like a plan for a table that never had one.

Finding it

EXPLAIN (ANALYZE, BUFFERS) on both versions, side by side. The fast plan had an index scan where the slow one had a sequential scan over the same table. Once you see that, the question stops being "why is this slow" and becomes "why is this index not eligible", which has a much shorter list of answers.

Two fixes exist. Put the predicates back in the query, or make the index non-partial. I took the second: the predicates had moved for good reasons, and an index that stops working when someone rewrites a join is a trap waiting for the next person. Trading some index size for not having to remember a condition was worth it here.

281ms to 29ms at p90.

Measure it properly, or you are guessing

One timing before and after tells you almost nothing. Caches warm, load varies, and the number you quote is whichever run you happened to keep.

I ran both versions A/B across the same request set and compared p90 and p95, not averages — an average hides exactly the tail you are trying to fix. I also checked response bodies matched between versions, because a "faster" query that returns different rows is not a faster query.

That harness paid for itself twice. Beyond confirming the fix, it surfaced 12 pre-existing P0 route failures that had nothing to do with the refactor and that nobody had noticed. Running every route through a comparison is a cheap audit you get for free once the harness exists.

What I would check first next time

When a refactor that "changed nothing" gets slower, diff the plans before reading the code. The planner will tell you what it stopped doing far faster than you will spot the moved condition by eye.

And if you use partial indexes, treat the predicate as part of the contract. Write it in a comment above the query if you have to. It's a dependency that no type system, linter or test will enforce for you.