PostgreSQL Ignored My Index — Until I Fought Back (A Planner Battle Story)

Max Shafiei — Mon, 12 May 2025 09:42:18 +0000

I noticed that a seemingly harmless query on one of our lookup tables was responsible for ~13% of total CPU usage in production. This was surprising, especially since the query targeted a text field backed by a trigram GIN index — which should’ve made it lightning fast.

But PostgreSQL had other ideas. It kept choosing a sequential scan, leading to unnecessary CPU load.

This post walks through how I investigated the problem, why I rejected some common solutions, and the subtle one-line trick that ended up working — without extensions, transactions, or rewriting the query.

🔥 The Problem

Here's the query:

SELECT *
FROM company_entities
WHERE category_id = 123
  AND (name ILIKE '%alpha%' OR name ILIKE '%group%' OR name ILIKE '%inc%')
LIMIT 10;

Even though a trigram GIN index existed on name, PostgreSQL consistently chose a sequential scan:

Seq Scan on company_entities
  Filter: name ILIKE ...
  Rows Removed by Filter: 9996
  Execution Time: ~46 ms

This would be understandable for a massive table — but this one had only ~17,000 rows.

Turns out, Postgres’s planner estimated that scanning the whole table would be cheaper than using the index — which simply wasn't true under real production load.

🧪 Can We Force the Index?

Option 1: `SET LOCAL enable_seqscan = off`

Yes, this works. Wrap the query in a transaction and disable sequential scans for that scope:

ActiveRecord::Base.transaction do
  ActiveRecord::Base.connection.execute("SET LOCAL enable_seqscan = off")

  CompanyEntity
    .where(category_id: 123)
    .where("name ILIKE ANY (ARRAY[?, ?, ?])", "%alpha%", "%group%", "%inc%")
    .limit(10)
    .load
end

✅ It forces the planner to use the GIN index

✅ Execution time drops by ~10×

But here’s the catch.

🚫 Why we didn’t go with it:

My colleagues raised valid concerns:

It requires wrapping read-only queries in transactions, which can surprise developers or break expectations.
It adds noise and complexity to otherwise simple scopes or service calls.
It doesn't compose well — if the calling code already opens a transaction, behavior can become unpredictable.
It smells like a workaround, not a long-term fix.

So we looked for something safer and less intrusive.

Option 2: A Harmless Dummy Condition

After testing a few ideas, one simple trick worked:

AND char_length(name) >= 0

This does nothing logically — unless name is null (which it isn't in our dataset). But Postgres treats it as a filtering condition on the name column, and that’s enough to make the planner reevaluate its assumptions.

The query plan switched to:

Bitmap Index Scan on index_company_entities_on_name
Execution Time: ~3.8 ms

That’s over 10× faster, without changing query logic or semantics.

We also verified the results were identical:

a = query_without_dummy.pluck(:id)
b = query_with_dummy.pluck(:id)
raise "Mismatch!" unless a == b

✅ Same data

✅ Less CPU

✅ No weird transactional behavior

I tried several other logically harmless dummy conditions to influence the planner. The idea was always the same: nudge PostgreSQL into using the GIN index without changing results. But most of these failed, for example:

This seemed like an obvious candidate. All name values are non-null in our dataset, so the condition is always true.

AND name IS NOT NULL

Result: Still triggered a sequential scan.
Likely because Postgres sees this as a no-op for planning purposes — not selective, not expressive enough.

Option 3: Query Hints or Extensions?

We also considered using the pg_hint_plan extension to force index usage:

/*+ BitmapScan(company_entities index_company_entities_on_name) */

But installing a C extension in a large organization (especially one on managed Postgres) is... ambitious.

It requires infra buy-in
It's harder to test and maintain
It adds a new surface area for query tuning most devs won’t understand

So we skipped it.

🧩 Summary of Options

Technique	Index Used?	Practical in Prod?	Notes
`SET LOCAL enable_seqscan = off`	✅	⚠️ Not ideal	Works, but adds transactional complexity
Query Hints or Extensions	✅	✅	Seems hacky and adds dependency on an extension
`char_length(name) >= 0`	✅	✅	Subtle, safe, and effective

✅ Final Takeaway

PostgreSQL’s query planner is smart — but not perfect. Especially when:

The table is small
You’re using %...% patterns
The index exists but looks “too expensive” at first glance

In our case, the best fix was not to rewrite the query, install an extension, or wrap everything in transactions.

Instead, it was a single, harmless condition:

AND char_length(name) >= 0

🧠 Low effort

🔒 Safe for production

⚡ Fast and effective

Have you ever fought the Postgres planner? I’d love to hear how you nudged it in the right direction.

Forem: Max Shafiei