Why a Partial Index Disappears Under a Generic Prepared Plan in YSQL

A partial index can work perfectly when you test a query with literal values, yet disappear when the same query is executed as a prepared statement.

The table, data, index, and logical query have not changed. The important difference is the type of execution plan:

●A custom plan is created using the parameter values supplied for that execution.
●A generic plan must work for every possible value that could be supplied during a future execution.

The planner can use a partial index only when it can prove, during planning, that the query satisfies the index’s WHERE predicate. When the required value is represented by an unknown parameter, that proof may not be possible. PostgreSQL’s partial-index documentation specifically notes that parameterized query conditions cannot use a partial index when the required implication cannot be established at planning time.

This tip reproduces the problem in YugabyteDB, explains why an index hint does not solve it, and shows three practical ways to address it.

💡 YugabyteDB Tip: When a partial index works with literal SQL but disappears from a prepared statement, compare the custom and generic plans before changing planner costs, adding hints, or rebuilding the index.

Version Note

The custom-versus-generic prepared-plan behavior applies to both PostgreSQL 11–based and PostgreSQL 15–based YugabyteDB releases.

The plan_cache_mode setting used to force the behavior in this demonstration is available with PostgreSQL 15–based YugabyteDB releases, starting with YugabyteDB v2025.1. YugabyteDB v2024.2 and earlier are based on PostgreSQL 11.

On a PostgreSQL 11–based YugabyteDB release, you can still encounter the same problem after a prepared statement has been executed repeatedly. However, you cannot use plan_cache_mode to force the generic or custom plan for testing.

The Setup

Create a simple multi-tenant catalog table:

				
					DROP TABLE IF EXISTS catalog_items;
DROP TYPE IF EXISTS item_status;

CREATE TYPE item_status AS ENUM (
  'active',
  'inactive',
  'draft'
);

CREATE TABLE catalog_items (
  tenant_id   uuid        NOT NULL,
  catalog_id  uuid        NOT NULL,
  sku         text        NOT NULL,
  label       text,
  status      item_status NOT NULL
);

Create a partial covering index containing only active catalog items:

				
					CREATE INDEX idx_catalog_items_active
  ON catalog_items USING lsm (
    (tenant_id, catalog_id) HASH,
    sku ASC
  )
  INCLUDE (label)
  WHERE status = 'active';

The index contains the three lookup columns and includes label, allowing the query to potentially use an Index Only Scan without retrieving the row from the base table. YugabyteDB covering indexes use INCLUDE columns to store additional projected columns in the index.

Insert the row used by the demonstration:

				
					INSERT INTO catalog_items (
  tenant_id,
  catalog_id,
  sku,
  label,
  status
)
VALUES (
  '11111111-1111-1111-1111-111111111111',
  '22222222-2222-2222-2222-222222222222',
  'sku-1234',
  'Prepared Plan Test Item',
  'active'
);

Add some additional data so that a sequential scan has meaningful work to perform:

				
					INSERT INTO catalog_items (
  tenant_id,
  catalog_id,
  sku,
  label,
  status
)
SELECT
  md5('tenant-' || (g % 100)::text)::uuid,
  md5('catalog-' || (g % 1000)::text)::uuid,
  'sku-' || (g % 5000),
  'Catalog Item ' || g,
  CASE
    WHEN g % 10 = 0 THEN 'inactive'
    WHEN g % 15 = 0 THEN 'draft'
    ELSE 'active'
  END::item_status
FROM generate_series(1, 50000) AS g;

ANALYZE catalog_items;

Verify the Partial Index with Literal SQL

First, run the query using literal values:

				
					EXPLAIN (ANALYZE, DIST, COSTS OFF)
SELECT
  tenant_id,
  catalog_id,
  sku,
  label
FROM catalog_items
WHERE tenant_id =
        '11111111-1111-1111-1111-111111111111'
  AND catalog_id =
        '22222222-2222-2222-2222-222222222222'
  AND sku = 'sku-1234'
  AND status = 'active';

The important portion of the plan should resemble:

				
					Index Only Scan using idx_catalog_items_active
  on catalog_items
  Index Cond: (...)
  Storage Index Read Requests: 1

The planner can see the literal condition:

				
					status = 'active'

It can therefore prove that the query satisfies the partial index predicate:

				
					WHERE status = 'active'

Prepared Statements Can Use Two Types of Plans

With the default auto behavior, PostgreSQL initially creates custom plans for a parameterized prepared statement. After the first five executions, it compares the average estimated cost of those custom plans with the cost of a reusable generic plan. It may then select the generic plan when the planning savings appear worthwhile. The switch is cost-based and is not guaranteed to occur for every statement.

Mode	Planner Behavior	Partial-Index Impact
`auto`	Starts with custom plans and may later select a reusable generic plan.	The partial index may work during the initial executions and disappear if the statement switches to a generic plan.
`force_custom_plan`	Creates a new plan using the parameter values supplied for each execution.	The planner can use the partial index when the supplied value satisfies its predicate.
`force_generic_plan`	Creates one reusable plan without considering the parameter values supplied for an individual execution.	A parameterized partial-index predicate usually cannot be proven during planning.

Reproduce the Generic-Plan Problem

Force YSQL to use a generic plan:

				
					SET plan_cache_mode = force_generic_plan;

Prepare the query with status represented by a parameter:

				
					PREPARE catalog_lookup (
  uuid,
  uuid,
  text,
  item_status
) AS
SELECT
  tenant_id,
  catalog_id,
  sku,
  label
FROM catalog_items
WHERE tenant_id = $1
  AND catalog_id = $2
  AND sku = $3
  AND status = $4;

Execute the prepared statement:

				
					EXPLAIN (ANALYZE, DIST, COSTS OFF)
EXECUTE catalog_lookup(
  '11111111-1111-1111-1111-111111111111',
  '22222222-2222-2222-2222-222222222222',
  'sku-1234',
  'active'
);

The resulting plan will typically resemble:

				
					 Seq Scan on catalog_items (actual time=63.824..63.830 rows=1 loops=1)
   Storage Filter: ((tenant_id = $1) AND (catalog_id = $2) AND (sku = $3))
   Filter: (status = $4)
   Storage Table Read Requests: 1
   Storage Table Read Execution Time: 63.641 ms
   Storage Table Read Ops: 1
   Storage Table Rows Scanned: 50001

The exact timing, row count, and distributed-storage counters will vary. The important detail is the Seq Scan.

The generic plan contains parameter placeholders such as $1 and $4 because it was not created for the values supplied to this specific execution.

💡 YugabyteDB Tip: Parameter placeholders such as $1 and $4 in an EXPLAIN EXECUTE plan are a strong indication that you are viewing a generic plan. A custom plan normally displays the supplied values.

Why the Partial Index Becomes Unusable

The partial index contains only rows for which:

				
					status = 'active'

The generic plan sees:

				
					status = $4

At execution time, $4 may indeed be assigned the value 'active'.

At planning time, however, $4 could represent any value of item_status:

				
					active
inactive
draft

A generic plan must remain correct for all three values. The planner therefore cannot assume that:

				
					status = $4

always implies:

				
					status = 'active'

The partial index does not contain inactive or draft rows. Using it in a plan that must also support those values could return incomplete results.

Because the implication cannot be proven when the generic plan is created, the partial index is not a legal access path for that plan. This is a correctness restriction… not simply a planner cost estimate.

Why an Index Hint Does Not Fix It

It is natural to try forcing the index with pg_hint_plan:

				
					DEALLOCATE catalog_lookup;

PREPARE catalog_lookup (
  uuid,
  uuid,
  text,
  item_status
) AS
/*+ IndexScan(catalog_items idx_catalog_items_active) */
SELECT
  tenant_id,
  catalog_id,
  sku,
  label
FROM catalog_items
WHERE tenant_id = $1
  AND catalog_id = $2
  AND sku = $3
  AND status = $4;

Execute it again:

				
					EXPLAIN (ANALYZE, DIST, COSTS OFF)
EXECUTE catalog_lookup(
  '11111111-1111-1111-1111-111111111111',
  '22222222-2222-2222-2222-222222222222',
  'sku-1234',
  'active'
);

The hint still cannot make the partial index usable.

An index hint can influence the planner’s choice among executable plans. It cannot make an index valid when that index may be missing rows required by the generic plan. The pg_hint_plan documentation notes that the planner selects an executable alternative when a requested plan cannot be executed.

💡 YugabyteDB Tip: An index hint can influence cost-based plan selection, but it cannot override the correctness requirements for a partial index. If the planner cannot prove the index predicate, the index is not a valid access path.

Fix 1: Keep the Partial-Index Predicate as a Literal

When a particular application path always retrieves active items, avoid parameterizing that one value.

The tenant, catalog, and SKU values can remain parameters. Keep the value required by the partial-index predicate visible in the SQL text:

				
					DEALLOCATE catalog_lookup;

SET plan_cache_mode = force_generic_plan;

PREPARE active_catalog_lookup (
  uuid,
  uuid,
  text
) AS
SELECT
  tenant_id,
  catalog_id,
  sku,
  label
FROM catalog_items
WHERE tenant_id = $1
  AND catalog_id = $2
  AND sku = $3
  AND status = 'active';

Test the new prepared statement:

				
					EXPLAIN (ANALYZE, DIST, COSTS OFF)
EXECUTE active_catalog_lookup(
  '11111111-1111-1111-1111-111111111111',
  '22222222-2222-2222-2222-222222222222',
  'sku-1234'
);

The generic plan can now use the partial index:

				
					 Index Only Scan using idx_catalog_items_active on catalog_items (actual time=0.558..0.566 rows=1 loops=1)
   Index Cond: ((tenant_id = $1) AND (catalog_id = $2) AND (sku = $3))
   Heap Fetches: 0
   Storage Index Read Requests: 1

The values for the indexed lookup columns remain unknown, but that does not matter. The planner can see the literal condition:

				
					status = 'active'

It can therefore prove that the query satisfies the partial-index predicate.

This is usually the most efficient solution when the query represents a dedicated “active items” application path.

💡 YugabyteDB Tip: Keeping a known application constant such as status = ‘active’ in the SQL text is different from concatenating user input into a query. Continue using bind parameters for values supplied by users or external systems.

Fix 2: Force a Custom Plan

When the application must keep status parameterized, you can force YSQL to create a custom plan for each execution.

Re-create the original prepared statement:

				
					DEALLOCATE active_catalog_lookup;

PREPARE catalog_lookup (
  uuid,
  uuid,
  text,
  item_status
) AS
SELECT
  tenant_id,
  catalog_id,
  sku,
  label
FROM catalog_items
WHERE tenant_id = $1
  AND catalog_id = $2
  AND sku = $3
  AND status = $4;

Force custom plans:

				
					SET plan_cache_mode = force_custom_plan;

Execute the query:

				
					EXPLAIN (ANALYZE, DIST, COSTS OFF)
EXECUTE catalog_lookup(
  '11111111-1111-1111-1111-111111111111',
  '22222222-2222-2222-2222-222222222222',
  'sku-1234',
  'active'
);

Because the custom plan is created using the supplied value 'active', the planner can prove the partial-index predicate and use idx_catalog_items_active.

The trade-off is that the statement must be planned for every execution. Generic plans avoid repeated planning work, while custom plans may provide better execution plans when the ideal plan depends heavily on parameter values.

For that reason, force_custom_plan is most useful as:

● A diagnostic test.
● A targeted session-level workaround.
● A targeted setting for a workload whose execution cost is much greater than its planning cost.

Avoid enabling it globally without measuring the effect on other queries.

Fix 3: Replace the Partial Index with a General Lookup Index

When the same prepared statement must efficiently retrieve active, inactive, and draft items, a partial index may not be the best design.

Replace it with a non-partial index that includes status as an index key:

				
					DROP INDEX idx_catalog_items_active;

CREATE INDEX idx_catalog_items_lookup
  ON catalog_items USING lsm (
    (tenant_id, catalog_id) HASH,
    sku ASC,
    status ASC
  )
  INCLUDE (label);

Return to a generic plan:

				
					SET plan_cache_mode = force_generic_plan;

Execute the original prepared statement:

				
					EXPLAIN (ANALYZE, DIST, COSTS OFF)
EXECUTE catalog_lookup(
  '11111111-1111-1111-1111-111111111111',
  '22222222-2222-2222-2222-222222222222',
  'sku-1234',
  'active'
);

The plan can now resemble:

				
					Index Only Scan using idx_catalog_items_lookup
  on catalog_items
  Index Cond: (
    (tenant_id = $1)
    AND (catalog_id = $2)
    AND (sku = $3)
    AND (status = $4)
  )
  Storage Index Read Requests: 1

There is no partial-index predicate left to prove. The status parameter is now an ordinary index search condition.

The trade-off is index size and write maintenance. The original partial index contained only active rows. The replacement index contains rows for every status.

In exchange, it provides predictable index access for both custom and generic plans.

Why `INCLUDE (status)` Alone Is Not Always the Best Replacement

You could instead create:

				
					CREATE INDEX idx_catalog_items_lookup
  ON catalog_items USING lsm (
    (tenant_id, catalog_id) HASH,
    sku ASC
  )
  INCLUDE (status, label);

That may produce an Index Only Scan resembling:

				
					Index Only Scan using idx_catalog_items_lookup
  Index Cond: (
    (tenant_id = $1)
    AND (catalog_id = $2)
    AND (sku = $3)
  )
  Storage Filter: (status = $4)

This avoids a table lookup because status and label are stored in the index. However, status is not part of the index search key. YSQL may still retrieve all index entries matching the tenant, catalog, and SKU before filtering by status.

When multiple status rows can exist for the same (tenant_id, catalog_id, sku) combination, placing status in the index key is generally the stronger design:

				
					sku ASC,
status ASC

Use INCLUDE (status) only when the preceding key columns already narrow the lookup sufficiently and you primarily want to avoid the table read.

Choosing the Best Fix

Solution	Best Use Case	Advantage	Trade-Off
Keep the partial predicate as a literal	The query path always uses one known status.	Preserves the smaller partial index and works with generic plans.	Requires control over the generated SQL.
Force a custom plan	The SQL must remain parameterized and the setting can be narrowly scoped.	Preserves the existing query and partial index.	Adds planning work to each execution.
Use a non-partial lookup index	One prepared statement must efficiently support several status values.	Provides predictable index usage with custom and generic plans.	The index contains more rows and increases write maintenance.

How to Detect This Problem

1. Check the Current Plan-Cache Mode

On a PostgreSQL 15–based YugabyteDB release:

				
					SHOW plan_cache_mode;

The default value is normally:

				
					auto

The supported values are:

				
					auto
force_custom_plan
force_generic_plan

2. Compare the Literal and Prepared Plans

Run the literal query:

				
					EXPLAIN (ANALYZE, DIST)
SELECT
  tenant_id,
  catalog_id,
  sku,
  label
FROM catalog_items
WHERE tenant_id =
        '11111111-1111-1111-1111-111111111111'
  AND catalog_id =
        '22222222-2222-2222-2222-222222222222'
  AND sku = 'sku-1234'
  AND status = 'active';

Then compare it with:

				
					EXPLAIN (ANALYZE, DIST)
EXECUTE catalog_lookup(
  '11111111-1111-1111-1111-111111111111',
  '22222222-2222-2222-2222-222222222222',
  'sku-1234',
  'active'
);

When the literal query uses the partial index but the prepared statement performs a sequential scan, inspect how the status condition appears in each plan.

3. Look for Parameter Placeholders

A generic plan normally shows placeholders:

				
					Filter: (status = $4)

A custom plan normally shows the supplied value:

				
					Filter: (status = 'active'::item_status)

4. Inspect Prepared-Statement Plan Counters

On PostgreSQL 15–based YugabyteDB releases, inspect the prepared statements in the current session:

				
					SELECT
  name,
  generic_plans,
  custom_plans,
  statement
FROM pg_prepared_statements;

Example output:

				
					.         name          | generic_plans | custom_plans
------------------------+---------------+--------------
 catalog_lookup         |             3 |            5

The generic_plans and custom_plans columns show how many times each type of plan was selected.

Remember that prepared statements are session-specific. Run this query through the same database connection that owns the prepared statement.

5. Review the YugabyteDB Distributed Counters

Use:

				
					EXPLAIN (ANALYZE, DIST)

The DIST option adds YugabyteDB-specific distributed execution information, including storage read requests, rows scanned, and storage execution timing. These counters make the effect of losing the partial index much easier to identify.

💡 YugabyteDB Tip: Do not compare only the total execution time. Also compare Storage Table Rows Scanned, Storage Table Read Requests, and Storage Index Read Requests. A small test table can hide the impact of an inefficient generic plan.

Reset the Session

After completing the demonstration, return the plan-cache setting to its default behavior:

				
					RESET plan_cache_mode;

Remove the prepared statement:

				
					DEALLOCATE ALL;

Final Takeaway

A partial index can be used only when the planner can prove that the query’s conditions imply the index predicate.

With a custom plan, the planner sees the parameter value supplied for the current execution. When $4 is 'active', it can prove that the query satisfies:

				
					WHERE status = 'active'

With a generic plan, $4 is unknown. Because the reusable plan must also work when $4 is 'inactive' or 'draft', the planner cannot safely use an index containing only active rows.

An index hint cannot override that correctness requirement.

The most practical solutions are:

● Keep the partial-predicate value as a literal when it is a known application constant.
● Force a custom plan for the affected session or workload.
● Replace the partial index with a general lookup index when the query must efficiently support multiple predicate values.

When a partial index works during manual testing but disappears after an application has executed the query several times, compare the custom and generic prepared plans before changing planner costs or adding hints.

Resources

Resource	Description
PostgreSQL Documentation – PREPARE	Explains custom plans, generic plans, and automatic plan selection.
PostgreSQL Documentation – plan_cache_mode	Documents the auto, force_custom_plan, and force_generic_plan settings.
PostgreSQL Documentation – Partial Indexes	Explains predicate implication and the limitation involving parameterized conditions.
PostgreSQL Documentation – pg_prepared_statements	Documents the generic_plans and custom_plans counters.
YugabyteDB Documentation – PostgreSQL 15 Features	Explains PostgreSQL 15 support beginning with YugabyteDB v2025.1.
YugabyteDB Documentation – Analyze Queries with EXPLAIN	Describes EXPLAIN ANALYZE and YugabyteDB distributed execution counters.
YugabyteDB Documentation – Covering Indexes	Explains Index Only Scans and additional columns stored with INCLUDE.
pg_hint_plan Documentation – Functional Limitations	Explains how the planner handles hints that request a plan that cannot be executed.

Have Fun!

Every Wednesday for the past four and a half years, I’ve enjoyed taking advantage of YugabyteDB’s DoorDash meal benefit.

One of my favorite regular spots has been Mandy’s Pizza. 🍕

Yesterday was probably our last order before we move to Dallas. Goodbye, Mandy’s… you’ll be missed!

Now it’s time to discover what great pizza places Dallas has to offer! 🤠🍕