NoIndexScan in PostgreSQL: How It Works and When to Use It

PostgreSQL is smart about choosing how to fetch data. Most of the time, it picks the right method on its own. But sometimes it doesn’t — and when it gets it wrong, your queries slow down in ways that are hard to pin down without digging into the planner.

NoIndexScan is one of the tools you reach for when that happens. It’s a query hint that tells PostgreSQL to stop using index scans for a specific table in a specific query. Simple idea — but genuinely useful when the planner makes a bad call.

So, this guide covers exactly how it works and when it actually helps.

How PostgreSQL Decides to Fetch Your Data

Before NoIndexScan makes sense, you need to understand what it’s overriding. Every time you run a query, PostgreSQL’s query planner looks at the available options and picks the one it thinks will cost the least.

The main options are sequential scans, index scans, index-only scans, and bitmap index scans. A sequential scan reads every row in the table from top to bottom — simple, predictable, and fast when you need most of the rows. An index scan jumps directly to matching rows using an index — much faster when only a small share of rows match.

The planner calculates a cost estimate for each method based on table statistics collected by the ANALYZE command. Then it picks the lowest-cost option. The problem: if those statistics are stale or off, the planner can pick the wrong method — and your query pays for it.

As the official PostgreSQL documentation on index-only scans explains, these decisions rely heavily on visibility map accuracy — something that can’t always be guaranteed after recent data changes.

Also read: Fix Archivebate Download Errors: yt-dlp and Script Users

What NoIndexScan Actually Does

NoIndexScan is a query hint that works through the pg_hint_plan extension. When you add it to a query, it tells the planner to skip index scans for the table you name. The planner then picks from what’s left — usually a sequential scan or a bitmap scan.

One thing worth knowing upfront: NoIndexScan also disables index-only scans automatically. You don’t need to say that separately. Apply NoIndexScan to a table, and both regular index scans and index-only scans are off for that query.

That’s different from session-level parameters like SET enable_indexscan = off, which hits every table across your entire session. NoIndexScan is more precise — it targets one table in one query and leaves everything else alone.

Here’s what the syntax looks like:

sql

/*+ NoIndexScan(table_alias) */
SELECT column1, column2
FROM my_table table_alias
WHERE condition;

The hint sits inside a comment right before the SELECT. pg_hint_plan reads it before the planner runs and applies it from there.

The Extension Behind the Hint — pg_hint_plan

NoIndexScan needs the pg_hint_plan extension to work. PostgreSQL doesn’t support query hints natively — that’s a deliberate design call. pg_hint_plan fills that gap.

It was built and maintained by the NTT OSS Center in Japan and lives on pgxn.org. It supports PostgreSQL versions 9.1 through 18. The current release is 1.6.0, covering PostgreSQL 16.

As the pg_hint_plan official documentation explains, the extension reads hint comments placed before SELECT statements and applies them before the planner runs — giving you precise, per-query control over how data gets fetched.

Beyond NoIndexScan, pg_hint_plan gives you a full set of scan hints. Here’s the full list:

Hint	What It Does
SeqScan(table)	Forces sequential scan
NoSeqScan(table)	Disables sequential scan
IndexScan(table)	Forces index scan
NoIndexScan(table)	Disables index scan AND index-only scan
IndexOnlyScan(table)	Forces index-only scan
NoIndexOnlyScan(table)	Disables index-only scan only
BitmapScan(table)	Forces bitmap index scan
NoBitmapScan(table)	Disables bitmap index scan

Each hint is per-query and per-table. You can stack multiple hints in one comment block, targeting different tables in the same query.

Also read: MonStream Explained: Set Up and Optimize Live Video Grids with Ease

When NoIndexScan Is Actually Useful

Knowing what it does is one thing. Knowing when to use it is another. Here are the real situations where it earns its place.

Stale Planner Statistics

These are the most common trigger. If ANALYZE hasn’t run recently after big data changes, the planner might think an index scan is faster when it isn’t.

One developer documented a case where a query on a 1,000-row table took several hundred milliseconds — because the planner was working off outdated stats.

Disabling index scans and letting the planner reconsider dropped execution time to 2.1 milliseconds. The real fix was running ANALYZE — but NoIndexScan closed the gap right away.

Testing and benchmarking

It is another solid use. Want to measure what a query costs without index access — so you can see what the index is actually doing for you?

NoIndexScan gives you a clean baseline. Run the query with and without it, then compare the execution plans.

Index maintenance windows

This is a third case. When you’re rebuilding an index or think one might be corrupt, you can use NoIndexScan to keep the planner away from it while you sort things out. Clean and immediate.

Query plan stability

We know that it matters very much in production. When a plan shifts after a stats update, it can cause sudden regressions. Pinning behaviour with NoIndexScan gives you a stable, predictable path that won’t change when stats refresh.

Also read: Fix Windows Modules Installer Worker High CPU Usage

NoIndexScan vs Session-Level Parameters

PostgreSQL has built-in ways to influence scan behaviour — session-level parameters that don’t need any extension. Here’s how they stack up against NoIndexScan:

Method	Scope	Precision	Best Use
NoIndexScan (pg_hint_plan)	Per-query, per-table	High — targets one table	Production tuning
SET enable_indexscan = off	Entire session	Low — affects all tables	Testing only
SET enable_indexonlyscan = off	Entire session	Low — affects all tables	Testing only
SET enable_seqscan = off	Entire session	Low — forces index globally	Debugging

The official PostgreSQL documentation calls these session-level parameters “crude” tools — good for diagnosis, not production. NoIndexScan gives you the precision they don’t.

You can turn off index access on one table in one query while everything else in that same query runs exactly as the planner planned.

The Index-Only Scan Detail Worth Knowing

Index-only scans sound like a clear win. The planner pulls all the data it needs straight from the index — no heap access, less I/O, faster results. In theory, always better than a regular index scan when conditions line up.

But in practice, it’s messier. Index-only scans are only truly “index only” when the table’s visibility map confirms rows are visible without touching the heap. If recent inserts, updates, or deletes have happened and the visibility map bits aren’t set yet, PostgreSQL falls back to checking heap pages anyway — which kills much of the benefit.

That’s why NoIndexScan disables index-only scans alongside regular ones. A plan that looks like it’s taking the fast path may secretly be doing heap access all along. Disabling both gives you a clean, predictable alternative without the hidden cost.

A Real-World Code Example

Here’s how NoIndexScan looks inside a PL/pgSQL function — straight from the official pg_hint_plan documentation:

sql

CREATE FUNCTION hints_func(integer) RETURNS integer AS $$
DECLARE
  id integer;
  cnt integer;
BEGIN
  SELECT /*+ NoIndexScan(a) */ aid
  INTO id
  FROM pgbench_accounts a
  WHERE aid = $1;

  SELECT /*+ SeqScan(a) */ count(*)
  INTO cnt
  FROM pgbench_accounts a;

  RETURN id + cnt;
END;
$$ LANGUAGE plpgsql;

The first SELECT applies NoIndexScan to pgbench_accounts — aliased as a — blocking any index or index-only scan for that lookup. The second SELECT forces a sequential scan on the same table for the count.

Both hints work independently inside the same function, hitting the same table in different ways based on what each query actually needs.

Things to Watch Out For

NoIndexScan is genuinely useful — but a few details can trip you up if you’re not ready for them.

• Case sensitivity matters. pg_hint_plan matches table names in hints against internal database object names in a case-sensitive way — unlike how PostgreSQL normally handles unquoted names. A hint with My_Table won’t match my_table. Use the exact name as stored internally, or rely on aliases and use those consistently.
• Use the alias, not the original name. If your query uses a table alias, the hint must reference that alias. The original table name won’t work once you’ve defined one.
• It’s a short-term fix, not a long-term answer. The PostgreSQL community is clear on this: query hints are workarounds, not solutions. The right fix for most planner issues is running ANALYZE with a higher default_statistics_target, building better indexes, or rewriting the query. NoIndexScan buys you time while you handle the root cause.
• Watch parallel query behaviour. Adding scan hints to subqueries can affect whether PostgreSQL runs them in parallel. If parallelism matters for your performance, test carefully after adding hints to make sure you haven’t quietly turned it off.

Also read: Where to Find System Preferences on Mac? It’s here

Wrapping Up

NoIndexScan is a precise, useful tool. It’s not something you reach for every day — and you shouldn’t need to. But when the planner gets it wrong and your query is paying for it, having a per-query, per-table override is genuinely valuable.

Use it when stats are stale and you need an immediate fix. Use it during index maintenance to keep queries away from a problem index. Use it in testing to see what your indexes are really doing for performance.

Just don’t lean on it as a substitute for the real work: keeping statistics fresh, building the right indexes, and writing queries the planner can reason about cleanly. NoIndexScan works best as a bridge — not a destination.