Experiments and implementations · Intermediate

Exploring partial-index predicates and use conditions

Inspect whether a query implies a partial-index predicate, distinguishing fixed constants from a generic prepared plan.

Ask Logeia about this articleTry the three questionsReview your answers

Updated

On this page
  1. Index only a defined subset
  2. Distinguish eligibility from estimated advantage
  3. Compare three plans on one table
  4. A supplied open value does not define every plan
  5. Do not assume unrestricted theorem proving
  6. Include maintenance and changing distribution
  7. Ask Logeia about this articleCheck your understanding

Index only a defined subset

A partial index contains only rows satisfying its predicate. This experiment indexes open rows while leaving most closed rows outside the index. A smaller structure can be useful, but it is not an index that serves every query. The planner must establish that the query’s possible answers are covered without omission. The following is a PostgreSQL 18 investigation procedure rather than a measured plan transcript. Run it in an isolated disposable local database and preserve the actual plans with their conditions. Do not apply a specialized index to production merely because its selected subset looks small in this example.

Distinguish eligibility from estimated advantage

A query explicitly requiring status open can establish the index predicate. A query requesting closed rows cannot use that index as if the missing rows were present. Even an eligible open query can receive another plan when many rows match, the table is small or another path is cheaper. Predicate implication is a logical eligibility question; planner cost comparison is a selection question. One observation that an index was not selected does not by itself identify a predicate error. Keep both stages of the decision visible when interpreting EXPLAIN instead of treating every missed index as the same kind of failure.

Compare three plans on one table

Insert one hundred thousand rows with one open row per hundred, collect statistics and compare open constants, closed constants and a parameterized statement. The parameterized case uses force_generic_plan only in this session to make the comparison explicit. This is an experimental setting, not a production recommendation. A generic plan must remain valid for all parameter values, which creates a different reasoning problem from a plan built for a known constant. Deallocate the prepared statement and reset the setting afterward. Plain EXPLAIN displays a plan without running the query, a distinction worth preserving when comparing this procedure with EXPLAIN ANALYZE.

PostgreSQL 18 SQL — procedure
-- PostgreSQL 18, disposable local database, one session.
CREATE TEMP TABLE lab_partial(id integer, status text, amount integer);
INSERT INTO lab_partial
SELECT i, CASE WHEN i%100=0 THEN 'open' ELSE 'closed' END, i%50
FROM generate_series(1, 100000) AS i;
CREATE INDEX lab_partial_open ON lab_partial(id) WHERE status='open';
ANALYZE lab_partial;
EXPLAIN SELECT id FROM lab_partial WHERE status='open' AND id=50000;
EXPLAIN SELECT id FROM lab_partial WHERE status='closed' AND id=50001;
SET plan_cache_mode = force_generic_plan;
PREPARE lab_partial_query(text, integer) AS
SELECT id FROM lab_partial WHERE status=$1 AND id=$2;
EXPLAIN EXECUTE lab_partial_query('open', 50000);
DEALLOCATE lab_partial_query;
RESET plan_cache_mode;

A supplied open value does not define every plan

The caller may know that this execution supplies open, but a generic plan cannot assume that parameter one always has that value. A predicate that fails for other allowed values cannot serve as its universal premise. A custom plan made with particular parameter values has a different situation. Do not respond by abandoning safe parameter binding for string concatenation. Instead inspect whether a fixed business condition can be expressed explicitly, whether another index suits the workload and which plan type is actually selected. Safe value binding and eligibility for a specialized partial index are separate design concerns.

Do not assume unrestricted theorem proving

Expressions that look equivalent to a human are not necessarily recognized as equivalent by the planner. Support for simple implications does not imply general reasoning about functions, casts or complex disjunctions. Compare the index definition with the query’s actual predicate, types and parameterization. Then inspect the plan and verify that any rewritten condition returns the intended row set. A rewrite that obtains an attractive plan while changing NULL behavior is not a correct optimization. Logical equivalence, recognized implication and estimated execution cost each require their own evidence rather than being collapsed into the phrase “the same condition.”

Include maintenance and changing distribution

As open rows become more common or frequently switch between states, both index size and update cost change. A single initial plan is insufficient for a permanent design decision. Revisit the distribution and representative query set. The one-percent proportion here is a controlled workload choice, not a universal threshold that predicts the same plan for every table. Close the temporary-table session after comparing and record why the specialized index is useful, which queries it cannot cover and which settings were changed. This makes its intended role reviewable without mistaking a narrowly eligible access path for a general replacement for indexing.

Reading and practice are recorded separately in this browser, for each language.

Learn this in order

Understand the systems behind the web · STEP 12

Choose a learning path