When a table has a partial index (for example, on (tenant_id, id) where status = 'open'), queries that satisfy that predicate can use the index. However, a prepared query might not use it if the planner cannot prove the predicate at planning time. We explore this with a controlled experiment on PostgreSQL 18, comparing four cases: a literal query, a generic prepared query with a parameter, a custom prepared query, and a generic prepared query with the predicate fixed in SQL text.
We also independently check that all query results match expectations, so that a seemingly faster plan isn’t allowed to return the wrong rows. This is a PostgreSQL 18 planning experiment, not an upgrade or major migration scenario. The complete SQL fixture is provided for readers to run. The result examples below are expected outcomes; no execution or measured plans are claimed. This article addresses only this one index and one query shape, building on the documented partial-index and prepared-query planning rules.
It does not revisit broad upgrade processes or generic vs. custom tuning; those are covered elsewhere (e.g. our advanced SQL techniques guide for general index concepts).
1. Define the partial-index question before tuning
The practical symptom is this: there is a valid partial index on tickets(tenant_id, id) with the predicate status='open', and a prepared query SELECT id FROM tickets WHERE tenant_id=$1 AND status=$2 ORDER BY id is used by an application. In a session where we execute it with $2 = 'open', it is tempting to expect the query to use the index if cost-effective, because the supplied status matches the predicate. Yet the chosen plan does not use the index. We must then ask three things:
• Predicate validity: For this query and index, can the planner prove status='open' from the query conditions at planning time? (PostgreSQL only uses a partial index if it can recognize that the query’s WHERE implies the index’s predicate.)
• Cost choice: If the index is eligible, does the planner’s cost model actually select an index scan, or does it pick a sequential scan or other path?
• Result integrity: Does our code change or query rewrite return exactly the intended ticket IDs (no more, no less)? Faster is pointless if the result set is wrong.
Answering these requires detailed inspection. We’ll build a minimal reproducible scenario and capture plans and query outputs. Readers should be familiar with standard SQL and EXPLAIN output. (See also our overview of SQL procedures, views and index foundations for general principles of indexes and query tuning.) We focus here on the partial-index nuance: just because a parameter value happens to be 'open' at execution time does not mean the generic plan assumed it. We keep the table, index, data distribution, and query shape otherwise fixed throughout.
2. Freeze the query contract and the index predicate
In our experiment, we define a non-unique B-tree partial index on tickets(tenant_id,id) with the predicate status='open'. We have two query forms:
• Qparam: a parameterized prepared statement with SELECT id FROM tickets WHERE tenant_id=$1 AND status=$2 ORDER BY id;. Here both tenant_id and status come from user-supplied parameters.
• Qopen: a variant where status='open' is literally in the SQL text (still with tenant_id=$1 as a parameter): SELECT id FROM tickets WHERE tenant_id=$1 AND status='open' ORDER BY id;.
Both queries serve the “open tickets for a tenant” use case, but they are not interchangeable. Qopen only applies to open-ticket operations. It is not a valid replacement for a query when $2='closed'. We keep the table, column types, row counts, and index definition identical in every test. The only differences are how the SQL is written and how the planner sees the status predicate.
Separate a fixed predicate from an interpolated value
The literal 'open' in Qopen is a fixed part of the SQL text (like a version-controlled constant for an “open tickets” operation), not something injected or concatenated at runtime. We do not rewrite queries by stringing together user values into SQL, nor do we implicitly change what “closed” means in code. User-provided tenant values remain bound to parameters in both forms. Qparam also binds the supplied status; Qopen has no status parameter. Any fix that silently hard-codes 'open' would change the semantics: e.g. using Qopen when a user wanted closed tickets would return the wrong IDs. We will demonstrate that mismatch and insist that such a rewrite is not a valid patch for the original operation.
3. Create the disposable fixture and capture its baseline
We perform all tests in one fresh psql session against a PostgreSQL 18 database. The clean test uses a standalone session without application frameworks, poolers, replicas, or concurrent writes. Save the entire script below as partial_index_lab.sql and run it via psql -X --set=ON_ERROR_STOP=1 --file=partial_index_lab.sql. This session creates only temporary objects and named prepared statements.
We capture all stdout, stderr, and the process exit code. Do not overwrite previous runs. The script itself includes checks (it fails if the server major version is not 18) and prints environment and diagnostic info. We also preset some variables: SET plan_cache_mode = auto; SET enable_seqscan = on;.
Before any query, we define the fixtures: a temp table tickets(id, tenant_id, status, padding), and insert our sentinel rows plus 30,000 ‘closed’ ballast rows for a dummy tenant 99. This yields exactly 30,006 distinct IDs. In particular, the sentinels are:
• Tenant 7: open tickets 101, 102, and a closed ticket 103.
• Tenant 8: open ticket 201 and a closed ticket 202.
• Tenant 9: open ticket 301 (an extra).
• Tenant 99: tickets 100001..130000, all with status ‘closed’ (ballast).
We then create the partial index:
CREATE INDEX tickets_open_idx ON tickets(tenant_id,id) WHERE status='open';
ANALYZE tickets;We run queries to prove our setup. For example, the script prints:SELECT count(*) AS total, count(DISTINCT id) AS distinct_ids FROM tickets;We expect total = 30006 and distinct_ids = 30006. It also groups by tenant/status to show each count per (tenant, status). Crucially, it prints our expected outputs explicitly:SELECT ARRAY[101,102] AS expected_open_tenant7,
ARRAY[201] AS expected_open_tenant8,
ARRAY[103] AS expected_closed_tenant7;These arrays (for open t7, open t8, closed t7) form our independent oracle for correct IDs, before running any query.
We also query pg_index to ensure the index is valid and ready:
SELECT indexrelid::regclass AS index_name,
indisvalid, indisready, indislive,
pg_get_expr(indpred,indrelid) AS predicate
FROM pg_index
WHERE indexrelid = 'pg_temp.tickets_open_idx'::regclass;This should show indisvalid=true, indisready=true, indislive=true (meaning the index is fully built and usable for queries), and predicate = status = 'open'. We also retrieve pg_get_indexdef(indexrelid) to see the full index definition. These serve as a baseline proof that the partial index exists and is the expected object.
Next, the script runs our four main experiment cases, each labeled by \echo for clarity. It does:
• LITERAL_BASELINE_OPEN_TENANT7: A fully literal query SELECT id FROM tickets WHERE tenant_id=7 AND status='open'. We run EXPLAIN (FORMAT JSON, VERBOSE, SETTINGS) on it, then run the corresponding SELECT query. This tests the trivial case where the predicate is explicit in the SQL.
• GENERIC_PARAMETERIZED_STATUS: We set plan_cache_mode = force_generic_plan and PREPARE qp_generic(integer,text). The query is the same as above but with $1,$2. We EXPLAIN EXECUTE qp_generic(7,'open'), then execute it with (7,'open'), (8,'open'), (7,'closed'). We then check pg_prepared_statements for the row named qp_generic, selecting its generic_plans, custom_plans counters and verifying parameter types and from_sql.
• CUSTOM_PARAMETERIZED_STATUS: Similarly, with plan_cache_mode = force_custom_plan, we prepare qp_custom(int,text) and do the same EXPLAIN/EXECUTE sequence. This enforces custom planning each time.
• GENERIC_FIXED_OPEN_PREDICATE_BOUND_TENANT: Now with plan_cache_mode = force_generic_plan but using a literal predicate. We PREPARE qo_generic(int) AS SELECT id FROM tickets WHERE tenant_id=$1 AND status='open'. We run EXPLAIN EXECUTE qo_generic(7), then EXECUTE qo_generic(7) and EXECUTE qo_generic(8). For demonstration, the script even tries EXECUTE qo_generic(7) again as a “wrong-case” (closed-ticket request) and prints the expected [103] for closed. Then we check pg_prepared_statements for qo_generic.
After these, the script runs a quick AUTO_OBSERVATION test (the default plan_cache_mode = auto scenario) by preparing qa(int,text) and executing it 5 times, checking counters, then 2 more times, checking again, and finally doing an EXPLAIN EXECUTE and checking counters once more. This illustrates the “first five custom” rule.
Finally, an optional diagnostic block runs in a transaction with enable_seqscan = off (local). It prepares two statements: one parameterized on $1,$2 and one with status fixed to 'open', and uses EXPLAIN on both to see how disabling seqscan changes nothing for the predicate problem. Then we roll back, restoring the settings.
Here is the full fixture script (to be saved as partial_index_lab.sql):
-- Proposed PostgreSQL 18 lab. No execution or measured plan is claimed.
\set ON_ERROR_STOP on
\set AUTOCOMMIT on
\pset pager off
\echo ENVIRONMENT
SELECT version(), current_database(), pg_backend_pid();
DO $$ BEGIN
IF current_setting('server_version_num')::int / 10000 <> 18 THEN
RAISE EXCEPTION 'Reference fixture requires PostgreSQL 18';
END IF;
END $$;
SET search_path = pg_temp, pg_catalog;
SET statement_timeout = '30s';
SET plan_cache_mode = auto;
SET enable_seqscan = on;
CREATE TEMP TABLE tickets (
id integer NOT NULL, tenant_id integer NOT NULL,
status text NOT NULL, padding text NOT NULL
);
INSERT INTO tickets VALUES
(101,7,'open','sentinel-a'), (102,7,'open','sentinel-b'),
(103,7,'closed','sentinel-c'), (201,8,'open','sentinel-d'),
(202,8,'closed','sentinel-e'), (301,9,'open','sentinel-f');
INSERT INTO tickets
SELECT 100000+n,99,'closed',repeat('x',64)
FROM generate_series(1,30000) AS g(n);
CREATE INDEX tickets_open_idx ON tickets(tenant_id,id) WHERE status='open';
ANALYZE tickets;
\echo FIXTURE_AND_INDEPENDENT_ORACLE
SELECT count(*) AS total, count(DISTINCT id) AS distinct_ids FROM tickets;
SELECT tenant_id,status,count(*) FROM tickets GROUP BY tenant_id,status ORDER BY 1,2;
SELECT ARRAY[101,102] AS expected_open_tenant7,
ARRAY[201] AS expected_open_tenant8,
ARRAY[103] AS expected_closed_tenant7;
SELECT indexrelid::regclass AS index_name, indrelid::regclass AS table_name,
indisvalid, indisready, indislive,
pg_get_expr(indpred,indrelid) AS predicate,
pg_get_indexdef(indexrelid) AS index_definition
FROM pg_index WHERE indexrelid='pg_temp.tickets_open_idx'::regclass;
\echo LITERAL_BASELINE_OPEN_TENANT7
EXPLAIN (FORMAT JSON, VERBOSE, SETTINGS)
SELECT id FROM tickets WHERE tenant_id=7 AND status='open' ORDER BY id;
SELECT id FROM tickets WHERE tenant_id=7 AND status='open' ORDER BY id;
\echo GENERIC_PARAMETERIZED_STATUS
SET plan_cache_mode = force_generic_plan;
PREPARE qp_generic(integer,text) AS
SELECT id FROM tickets WHERE tenant_id=$1 AND status=$2 ORDER BY id;
EXPLAIN (FORMAT JSON, VERBOSE, SETTINGS) EXECUTE qp_generic(7,'open');
EXECUTE qp_generic(7,'open');
EXECUTE qp_generic(8,'open');
EXECUTE qp_generic(7,'closed');
SELECT name,parameter_types,from_sql,generic_plans,custom_plans
FROM pg_prepared_statements WHERE name='qp_generic';
DEALLOCATE qp_generic;
\echo CUSTOM_PARAMETERIZED_STATUS
SET plan_cache_mode = force_custom_plan;
PREPARE qp_custom(integer,text) AS
SELECT id FROM tickets WHERE tenant_id=$1 AND status=$2 ORDER BY id;
EXPLAIN (FORMAT JSON, VERBOSE, SETTINGS) EXECUTE qp_custom(7,'open');
EXECUTE qp_custom(7,'open');
EXECUTE qp_custom(8,'open');
EXECUTE qp_custom(7,'closed');
SELECT name,parameter_types,from_sql,generic_plans,custom_plans
FROM pg_prepared_statements WHERE name='qp_custom';
DEALLOCATE qp_custom;
\echo GENERIC_FIXED_OPEN_PREDICATE_BOUND_TENANT
SET plan_cache_mode = force_generic_plan;
PREPARE qo_generic(integer) AS
SELECT id FROM tickets WHERE tenant_id=$1 AND status='open' ORDER BY id;
EXPLAIN (FORMAT JSON, VERBOSE, SETTINGS) EXECUTE qo_generic(7);
EXECUTE qo_generic(7);
EXECUTE qo_generic(8);
\echo NOT_A_VALID_REPLACEMENT_FOR_A_CLOSED_TICKET_REQUEST
EXECUTE qo_generic(7);
SELECT ARRAY[103] AS expected_for_closed_request;
SELECT name,parameter_types,from_sql,generic_plans,custom_plans
FROM pg_prepared_statements WHERE name='qo_generic';
DEALLOCATE qo_generic;
\echo AUTO_OBSERVATION_NO_ASSUMED_SIXTH_EXECUTION_SWITCH
SET plan_cache_mode = auto;
PREPARE qa(integer,text) AS
SELECT id FROM tickets WHERE tenant_id=$1 AND status=$2 ORDER BY id;
EXECUTE qa(7,'open');
EXECUTE qa(7,'open');
EXECUTE qa(7,'open');
EXECUTE qa(7,'open');
EXECUTE qa(7,'open');
SELECT name,generic_plans,custom_plans FROM pg_prepared_statements WHERE name='qa';
EXECUTE qa(7,'open');
EXECUTE qa(7,'open');
SELECT name,generic_plans,custom_plans FROM pg_prepared_statements WHERE name='qa';
\echo EXPLAIN_IS_A_SEPARATE_PLANNING_OBSERVATION
EXPLAIN (FORMAT JSON, VERBOSE, SETTINGS) EXECUTE qa(7,'open');
SELECT name,generic_plans,custom_plans FROM pg_prepared_statements WHERE name='qa';
DEALLOCATE qa;
\echo OPTIONAL_COST_DISCOURAGEMENT_DIAGNOSTIC_NOT_A_DEPLOYMENT_SETTING
BEGIN;
SET LOCAL plan_cache_mode = force_generic_plan;
SET LOCAL enable_seqscan = off;
PREPARE qd_parameter(integer,text) AS
SELECT id FROM tickets WHERE tenant_id=$1 AND status=$2 ORDER BY id;
PREPARE qd_literal(integer) AS
SELECT id FROM tickets WHERE tenant_id=$1 AND status='open' ORDER BY id;
EXPLAIN (FORMAT JSON, VERBOSE, SETTINGS) EXECUTE qd_parameter(7,'open');
EXPLAIN (FORMAT JSON, VERBOSE, SETTINGS) EXECUTE qd_literal(7);
DEALLOCATE qd_parameter;
DEALLOCATE qd_literal;
ROLLBACK;
SHOW enable_seqscan;
SHOW plan_cache_mode;
SELECT count(*) AS final_population FROM tickets;
-- Session end removes temporary objects. Preserve client output first.No actual results or plans are shown here. In practice, the above will emit the JSON plans and query outputs for each step. We keep the expected results arrays separate from the actual query outputs. The script does not automatically compare them or assert equality. It is up to the reviewer or testing harness to verify that, for each request, the returned IDs match the expected array printed.
4. Inspect the literal-query baseline
First we consider the baseline: tenant_id=7 AND status='open' with literal constants. In this query, the condition status='open' exactly matches the index’s predicate. Thus at planning time, PostgreSQL can see that all rows will satisfy the predicate. We record the plan in JSON (with settings) via EXPLAIN, and then execute the query to retrieve the IDs. The expected IDs are [101,102]. In a small fixture, cost factors could still favor a seq scan, so we do not assume the index is used without checking.
We store the plan output along with the schema: record the table and index names shown in the JSON, together with the baseline catalog definition. Numeric table and index OIDs must be obtained separately from pg_index; they are not supplied by EXPLAIN JSON. This is important because an index name alone could refer to a different object in another run. We log the exact index definition and plan tree here. The returned rows (printed by the final SELECT) should be two rows (101, 102) in order.
If the plan JSON shows an Index Scan using tickets_open_idx, we know the index path was chosen; otherwise the plan may show Seq Scan. We do not use elapsed-time or benchmark numbers here, only the plan shape and result correctness.
5. Expose the unknown status in a generic prepared plan
Next we force a generic plan. With plan_cache_mode = force_generic_plan, prepare qp_generic(int,text). The query is SELECT id FROM tickets WHERE tenant_id=$1 AND status=$2. We run EXPLAIN EXECUTE qp_generic(7,'open'). In that JSON plan, you should see parameter symbols $1 and $2 (because it’s a generic plan). Crucially, the planner at plan time does not know that $2 will be 'open'; it must assume $2 could be any status. Since our table has some closed rows, it cannot infer that status='open' holds for all cases. In other words, the partial index predicate is not proven by the WHERE clause, so the index isn’t recognized (it’s “not available” to that plan).
We then execute qp_generic three times: with (7,'open'), (8,'open'), and (7,'closed'). They must return [101,102], [201], and [103] respectively, matching the independent expected arrays. These are acceptance expectations, not recorded results. We query pg_prepared_statements for qp_generic: the prepared-statement counters should show generic_plans = 4 and custom_plans = 0 for this sequence, counting one EXPLAIN EXECUTE and three EXECUTE calls. These counters record plan selections, not the number of distinct cached plans.
According to the documentation, generic plans must work for all parameter values, so the planner will never “cheat” by assuming $2='open' just because the current call used 'open'. The partial-index documentation explains that an unknown parameter cannot establish the predicate for all possible values. In this generic plan, the status predicate remains unknown; a separate bound tenant parameter does not prevent use when the open-status predicate is fixed in SQL. We highlight that inference: combining the partial-index rules with the generic-plan rules shows that a generic plan cannot rely on $2 being open. We must rely on the recorded EXPLAIN output to see which nodes appear; we don’t just assume the index “might have been used.” For qp_generic, the partial-index predicate is ineligible at planning, and thus the JSON plan should omit any index scan on tickets_open_idx (it will likely show a Seq Scan).
6. Use a custom plan as the comparison case
Now we force a custom plan for the same parameterized query. With plan_cache_mode = force_custom_plan, we PREPARE qp_custom(int,text) using the same SQL. Each EXECUTE generates a fresh plan using that execution’s parameter values. We first run EXPLAIN EXECUTE qp_custom(7,'open'). Because this is custom, PostgreSQL substitutes $2 = 'open' at plan time, so the WHERE clause effectively includes status='open'. The planner can now see that predicate and may consider the partial index. The JSON plan might show an Index Scan on tickets_open_idx if cost prefers it, or it might still choose a Seq Scan depending on the estimated costs. We then run (7,'open'), (8,'open'), (7,'closed'). The results must again match [101,102], [201], and [103] respectively.
We check pg_prepared_statements for qp_custom: now generic_plans=0 and custom_plans should be 4 for this sequence, including EXPLAIN EXECUTE as well as the three EXECUTE calls. Verify these plan-selection counters in the saved output. In this comparison, the only difference from the previous arm is that now the planner could use the index because the constant was known. We still verify outcomes before declaring anything “fixed.” If, say, the custom plans also chose a sequential scan (due to very small row counts), we report that as a cost/choice result.
We are not globally suggesting that everyone should always force custom plans; that decision depends on workload and overhead. Our sole purpose here is to see that the parameter-value knowledge enables the index predicate, not that it automatically speeds up the query. The returned IDs must be correct in all cases before accepting that the query semantics are preserved.
7. Keep the partial predicate literal and the tenant bound
Next, we prepare qo_generic(int) with a literal predicate: WHERE tenant_id=$1 AND status='open'. With plan_cache_mode = force_generic_plan, this execution is forced to use a generic plan. Having only one parameter does not, by itself, eliminate custom planning in other modes. We run EXPLAIN EXECUTE qo_generic(7) and then EXECUTE qo_generic(7) and EXECUTE qo_generic(8). Here, $1 is the only parameter; 'open' is fixed in the SQL. This query always requests open tickets. Now the partial index predicate is clearly implied (the condition literally contains status='open'), so an index scan is eligible this time. Again, whether it’s chosen depends on cost, but at least the eligibility issue is resolved. The executions should return [101,102] for tenant 7 and [201] for tenant 8, as expected.
Reject a fast rewrite that answers the wrong request
This final test also includes a caution: We run EXECUTE qo_generic(7) again under the label "NOT_A_VALID_REPLACEMENT_FOR_A_CLOSED_TICKET_REQUEST" and then show SELECT ARRAY[103] as the expected closed-ticket result. The point is that qo_generic was defined for open tickets only. If someone tried to use it to satisfy a “closed tickets for tenant 7” request, it would still return [101,102] (open ticket IDs), which mismatches [103]. That mismatch is unacceptable. In other words, the status = 'open' rewrite is only correct for the open-ticket use case.
We must treat this as a semantic stop: just because the plan might be efficient does not mean we can break the query contract. If an operation sometimes needs closed tickets, it needs its own query or index, or it must accept a generic plan. Fixes for correctness (not performance) must come from reviewing the operation’s requirements. (This is similar to the advice on reviewing rewrites from our guide to query optimization with AI-powered assistants; always confirm a proposed rewrite returns the right data.)
8. Observe auto planning without inventing a switch point
After covering the force-generic/custom cases, we reset plan_cache_mode = auto to see PostgreSQL’s built-in behavior. By default PostgreSQL uses the first five executions of a prepared statement to generate custom plans, computes their average cost, then compares with a one-time generic plan. Our script prepares qa(int,text) and calls it five times (all with (7,'open')). We then query pg_prepared_statements for qa: we should see custom_plans=5 (since each call was custom up to 5) and generic_plans=0. Next we execute two more calls (6th and 7th); PostgreSQL considers a generic candidate after the first five custom executions and compares estimated costs, including the benefit of avoiding repeated planning.
It may select that generic plan or continue with custom plans. We capture the counts again. We then run EXPLAIN EXECUTE qa(7,'open') a final time and check the counters one more time. The important lesson is that there is no fixed rule that the 6th call automatically switches to generic; it depends on the cost estimates. A lower average custom-plan cost does not by itself rule out a generic plan; the choice also weighs repeated planning overhead. In our sample, either way is informative. This micro-test illustrates the auto logic.
This step is solely diagnostic. It shows that we cannot assume, for example, “the 6th call will always go generic.” Any development team reproducing this should remember that our fixture is static: if qa remains using custom plans, that’s fine; it just means the planner chose not to switch (which can happen). This is not the place to dive into overall performance curves or hot-tenant scenarios. (For broader context on testing prepared queries during upgrades, see our PostgreSQL 19 workload-replay readiness guide.) EXPLAIN EXECUTE is a separate planning observation. It can affect plan-selection counters and the plan chosen in auto mode, even though this EXPLAIN does not execute the underlying SELECT.
9. Distinguish an ineligible path from an expensive one
Finally, we run the optional diagnostic with enable_seqscan = off (wrapped in a transaction). Disabling sequential scans discourages them but does not forbid them. The point is to see whether discouraging sequential scans would make the generic parameterized query pick some index path. We prepare two statements within the transaction: one with parameters (qd_parameter) and one with the literal open (qd_literal). We then EXPLAIN each. The expectation is: the literal-predicate query may use tickets_open_idx because the predicate is provable and sequential scans are discouraged.
In contrast, the parameterized one still cannot logically use that index (the predicate is unknown), so even with enable_seqscan=off it must use another valid path. In this fixture, there is no other index, so a sequential scan remains possible despite being discouraged. In any case, the key is that we cannot “manufacture” a valid partial-index path by tweaked settings when the predicate implication is absent.
After these EXPLAINs, we ROLLBACK, restoring enable_seqscan to on and plan_cache_mode to auto. We check with SHOW to confirm they are back to defaults. We also count final_population of the table to verify nothing changed. Again, we do not treat the index use in this diagnostic as a guaranteed new plan; it’s just a cost hint. We explicitly avoid calling this a benchmark: the eligible literal-predicate plan may or may not select the index; the generic parameterized-status plan cannot use this partial index.
The lesson is to not confuse “we turned off seqscan and saw an index” with “the parameter query is actually eligible.” If the generic parameterized plan omits the index, that is consistent with the documented eligibility diagnosis. Absence of an index node alone does not distinguish ineligibility from a cost decision. We also note: any chosen plan is just the result of the current snapshot. Different data, stats, or PostgreSQL version or settings could pick a different node.
We do not rewrite our story if, say, both literal and custom still chose seqscan for some reason. We stick with the evidence as recorded.
Avoid treating a chosen plan as a permanent guarantee
Every EXPLAIN above shows "the chosen plan under these settings and this data." If in any case the partial index is used by, say, the custom or literal query, it does not magically mean it will always be used. Changes to table size, clustering, statistics, or a new version could change that decision. Conversely, if we never see the index used here, that doesn't prove it can never be. In other words, treat these plans as answers for this snapshot only. The important outcome is our logical determination of eligibility and correctness, not a hard claim like “INDEX SCAN is guaranteed”.
10. Reconcile all result sets before accepting a change
After running the fixture, compare the results for each query variant with the expected IDs. For each case, list: (Tenant, Requested status, Query form, Expected IDs, Actual IDs). For example:
• Literal baseline (t7, 'open') must return [101,102] (expected [101,102]).
• Generic-prep (t7, 'open') must return [101,102] (expected [101,102]).
• Generic-prep (t8, 'open') must return [201] (expected [201]).
• Generic-prep (t7, 'closed') must return [103] (expected [103]).
• Custom-prep results (same cases) must each match the expected list.
• Fixed-open (t7, 'open') must return [101,102] (expected [101,102]).
• Fixed-open (t8, 'open') must return [201] (expected [201]).
• Fixed-open used as closed (t7, closed) would return [101,102] (which does not match the expected [103]).
For acceptance, every valid scenario must have actual IDs equal to the expected IDs. The deliberately expected mismatch occurs when trying to use the open-only query for a closed request, which is a semantic error. We do not automatically assert or stop in the script if something is wrong; the entire point of the separate expected arrays is to let us manually or programmatically check. This mirrors good SQL data-granularity validation practice. The script makes no judgment; it simply provides both sides. Any mismatch (like the one above) tells us to HOLD that proposed change. We must explicitly compare identity and cardinality of IDs, not just counts, to guard against subtle duplicates or extra rows.
11. Capture session and plan identity as evidence
Throughout the test, label outputs and record context. The session’s version(), current_database(), and pg_backend_pid() are printed first to tie everything to a single server process. Each prepared statement is queried in pg_prepared_statements for its name, parameter_types, and from_sql. In SQL mode, from_sql = true indicates these came from the PREPARE commands in the fixture. We capture generic_plans and custom_plans for each named statement, which are counters for this session only. For qp_generic, the proposed sequence should produce generic_plans = 4 and custom_plans = 0: one selection for EXPLAIN EXECUTE and three for EXECUTE. Reconcile the observed counters with the commands actually issued.
For the plans themselves, we save the full JSON output of EXPLAIN (FORMAT JSON, SETTINGS, VERBOSE). If it’s a generic plan, the JSON will show $1/$2 placeholders; if custom, it will show actual values (as noted in docs). EXPLAIN SETTINGS includes nondefault options affecting query planning. Retain the explicit SET commands and relevant SHOW output as well, including plan_cache_mode and enable_seqscan, rather than treating SETTINGS as a complete configuration inventory. By bundling each plan with its environment and with the index definition text (from the baseline), we have complete evidence. We do not rely on any statistics collector or extension; everything is visible through normal SQL. These counters belong to the prepared statements in the current session; a fresh session or deallocation and re-preparation starts new statement evidence. These details are proof of what exactly was tested.
12. Repair and reprepare the reviewed query path
Based on the evidence, we have a small query fix in mind. If the “list open tickets” operation is strictly defined to only ever need open tickets, we can safely use the fixed-predicate form (WHERE status='open') in the SQL. The proposed change is thus: leave the index as is, but in the application or report code, use Qopen instead of Qparam. That means the prepared statement (or ORM query, or API call) should be updated so that the SQL text includes status='open' and only the tenant ID is a parameter. In practice, this would involve deallocating or dropping the old prepared statement and preparing the new one (as shown in the script), or deploying new client-side code with the revised query.
We do not suggest changing any global planner settings (like permanently disabling seq scans); those are beyond scope. We do not call this a data “recovery” or suggest dropping/rebuilding the index. It is purely a query rewrite. As with any rewrite, it must be reviewed carefully: double-check that Qopen is indeed never used when a user requests closed tickets. If so, after deployment one should rerun tests (e.g. using the same fixture or a similar harness) to ensure the new query works as expected under real conditions. This kind of correctness review is exactly what we advocate (see our discussion on reviewing automated query rewrites); the rewrite here must be validated with actual data.
Restore the previous reviewed query without changing data
Once a decision is made, undoing this experiment is simple. In the disposable session, use DEALLOCATE for the named statements and end the session. In a real system, rolling back to the old code would mean removing or disabling the new prepared query and reintroducing the original one. The proposed query rewrite and the comparison SELECT statements do not modify application data. Fixture setup does create and populate temporary objects. We are not dropping the tickets_open_idx or touching the tickets table. We are not invoking any external tools or poolers. A rollback of this change is conceptually just “use the original prepared statement again and run the tests again,” preserving the same result-contract. There is no need for data recovery in a transactions sense, because the query rewrite itself changes no application data.
13. Set the decision matrix and operational owners
Use the following outcomes to assign the next action:
• Fixture invalid / setup error: If the row counts or distinct ID check failed (e.g. totals mismatched), do not proceed. Have the DBA re-examine the data loading. Action: HOLD further steps until fixture is corrected.
• Generic plan predicate unprovable, but results correct: This indicates the partial index could not be used in the generic plan, yet the query returned the right IDs anyway. We note this as a diagnosed limitation (generic plans can’t use that index) but no data is wrong. Action: Consider it a known constraint; no immediate rollback needed, but document it.
• Fixed-predicate query eligible with correct results: The open-only query works and now allows the index predicate. This is a candidate fix for that operation. Action: Developer or application owner should code-review this change (they own the query logic). Then re-run tests on this path.
• Index-eligible but cost too high: If our JSON shows the index scan was not chosen even when eligible, and performance is a concern, escalate to the DBA/performance team. They might examine statistics, adjust costs, or consider physical clustering. Action: Open a performance analysis ticket (DBA).
• Semantic mismatch (e.g. open-only query vs closed request): This is an outright rejection. Do not use the rewritten query in that context. Action: Maintain separate queries or indexes for open vs closed paths; developers must keep query logic correct.
• Incomplete or missing plan data: If some EXPLAIN didn’t run or counters are weird, treat that as an error and re-run the test. Action: Repeat the fixture execution (ops team / DBA should verify environment).
In terms of ownership: the application/operations owner of the “ticket listing” feature understands whether a request is for open-only or not (business logic). They should decide if a version-specific fix (the open-only query) is acceptable. The DBA or performance engineer provides the index and plan data evidence (using EXPLAIN and pg_index). The platform or developer team that manages query deployment must handle the prepared-statement lifecycle (apply or roll back the change in code or migrations). In other words, developers handle query rewriting and testing, DBAs handle index health and stats, and product owners ensure semantic correctness.
For a broader view of roles, see our discussion of database performance and operational ownership. Re-validation triggers include: data growth (more tickets), index changes, or query-time error reports (if someone requested closed tickets and got wrong results). Note that a version upgrade itself might warrant rechecking (though our scope here is PostgreSQL 18 specifically).
14. Build the SQL foundations needed to review this evidence
Diagnosing these issues requires a combination of skills: understanding how WHERE predicates relate to index definitions, interpreting EXPLAIN plan trees, verifying query result sets, and controlling session settings (like plan_cache_mode and enable_seqscan). It also helps to be comfortable writing SQL to summarize and compare data at the row level. The database administrator or engineer needs to spot whether an index scan could occur and whether the returned data actually makes sense.
For a structured learning path covering these skills, consider the Database Administrator Essentials program from Refonte Learning. This is a three-month training program (about 12–14 hours per week) for aspiring DBAs. It covers database design, SQL query optimization, performance tuning, backup/recovery, security, and more. The curriculum explicitly includes SQL Query Optimization and Performance Tuning topics, exactly the background needed for exercises like this. Students who complete the program earn a Training Certificate and a Certificate of Internship.
Admission requires at least a Bachelor’s degree in progress, and some basic programming familiarity is recommended. The program does not claim to teach PostgreSQL 18 specifically or this exact lab; it builds the general foundation that enables tasks like reviewing query plans and indexes. If you found this exercise helpful, those courses may be a useful next step in solidifying the SQL and database knowledge required to apply these techniques safely.