Sargability, Statistics, and Cardinality Estimation

Learning outcomes

Make predicates searchable and estimates credible

Sargability connects SQL to index order

A predicate is commonly called sargable when it exposes a search argument that an index can navigate. The database needs a relationship such as indexed_key = constant, a bounded range, or a compatible prefix—not an opaque transformation that must be computed for every row.

Date filtering: half-open ranges beat wrapped columns

CREATE INDEX idx_order_dateON sales_order (ordered_at);

SELECT COUNT(*)FROM sales_orderWHERE strftime('%Y-%m', ordered_at) = '2025-06';

SELECT COUNT(*)FROM sales_orderWHERE ordered_at >= '2025-06-01'  AND ordered_at <  '2025-07-01';

The half-open range preserves the raw indexed value and handles timestamps throughout the final day without fragile “23:59:59” boundaries.

Use an expression index when the expression is the contract

CREATE INDEX idx_customer_email_lowerON customer (lower(email));SELECT customer_id, emailFROM customerWHERE lower(email) = lower('Customer0417@Example.com');

Expression indexes are appropriate when the transformation is deterministic, frequent, and standardized. They are not a reason to index every ad hoc function users might write.

Pattern matching and prefixes

CREATE INDEX idx_customer_emailON customer (email);SELECT customer_id, emailFROM customerWHERE email >= 'customer04'  AND email <  'customer05'ORDER BY email;

Implicit conversions and type alignment

Column type: INTEGERParameter type: integer, not textColumn expression: unchanged when possibleCollation: matches indexed collationTimezone boundary: converted once before queryPattern: anchored unless substring search is intentional

Database products differ in coercion rules, but the safe principle is stable: compare compatible types and collations. Convert the parameter once rather than wrapping the indexed column for every row.

Cardinality is estimated row count

If a relation has N rows and the optimizer estimates predicate selectivity s, the estimated output cardinality is:

Plan decisions depend on this estimate: nested loop versus hash join, index lookup versus scan, sort memory, parallelism, and join order.

Refresh SQLite statistics

CREATE INDEX IF NOT EXISTS idx_order_customer_dateON sales_order (customer_id, ordered_at);CREATE INDEX idx_order_status_dateON sales_order (status, ordered_at);ANALYZE;SELECT tbl, idx, statFROM sqlite_stat1WHERE tbl IN ('customer', 'sales_order')ORDER BY tbl, idx;

ANALYZE records statistics that help SQLite compare candidate plans. Statistics should represent production-like data; a tiny empty test database teaches the optimizer very little about the real workload.

Correlation breaks naive independence

Suppose region and channel are correlated. A simple estimator may multiply selectivities:

If mobile orders are concentrated in one region, the true joint selectivity can differ substantially. PostgreSQL extended statistics can model dependencies and multivariate distributions; SQLite’s statistics model is simpler, so schema, indexes, and query shape deserve careful testing.

Prove the rewrite with plans

EXPLAIN QUERY PLANSELECT order_idFROM sales_orderWHERE strftime('%Y-%m', ordered_at) = '2025-06';EXPLAIN QUERY PLANSELECT order_idFROM sales_orderWHERE ordered_at >= '2025-06-01'  AND ordered_at <  '2025-07-01';

The second form should expose a bounded range on idx_order_date. The first can use an expression index only if one was explicitly created for the same expression.

Checkpoint

Summary and references

• Sargable predicates expose ordered keys and compatible operators.
• Half-open ranges are robust for timestamp intervals.
• Expression indexes support standardized deterministic transformations.
• Statistics drive selectivity and cardinality estimates.
• Validate rewrites with plans and representative data.

References

• SQLite: Query Optimizer Overview
• SQLite: ANALYZE
• SQLite: Indexes on Expressions
• PostgreSQL: Statistics Used by the Planner
• PostgreSQL: How the Planner Uses Statistics