Verify partition pruning

Oracle

EXPLAIN PLAN FOR
SELECT * FROM sale WHERE sale_date = DATE '2026-01-15';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- Look for 'PARTITION RANGE ITERATOR/SINGLE' and the Pstart/Pstop columns in the plan.

Pstart/Pstop show that only the relevant partitions are read (a number rather than KEY is ideal). Applying a function/implicit conversion to the partition key prevents pruning.

PostgreSQL

SET enable_partition_pruning = on;
EXPLAIN (ANALYZE)
SELECT * FROM sale WHERE sale_date = DATE '2026-01-15';
-- Verify in the plan that only the relevant partition (child table) is scanned.

With enable_partition_pruning on, only matching partitions appear in the plan; the others show as 'never executed'. Constant values are pruned at plan time, parameters at execution time.

MySQL

-- Show which partitions are scanned:
EXPLAIN SELECT * FROM sale WHERE sale_date = '2026-01-15';
-- 5.7 and earlier: EXPLAIN PARTITIONS SELECT ... (removed in 8.0)

In MySQL 5.7+ the 'partitions' column of plain EXPLAIN output lists the partitions to be scanned (the EXPLAIN PARTITIONS syntax was removed in 8.0); only the relevant partition(s) should appear. If all partitions are listed, pruning is not happening.

SQL Server

-- Find which partition a value falls into:
SELECT $PARTITION.pf_sale_date('2026-01-15') AS partition_no;
-- Examine 'Actual Partition Count' and 'Actual Partitions Accessed' in the actual plan.

The $PARTITION function returns a value's partition number. If the number of partitions accessed in the actual execution plan is close to 1, pruning is in effect; if all partitions are scanned, the predicate does not match.

MongoDB

// Targeted query using the shard key in a sharded cluster:
db.sale.find({ shop_id: 42 }).explain("executionStats")
// In explain, only the relevant shard should appear under 'shards'.

If the query contains the shard key, mongos routes it only to the relevant shard (targeted); otherwise it fans out to all shards (scatter-gather). Verify the shards accessed in the explain output.

Redis

# Not applicable: there is no table/partition concept.

Data is distributed via Cluster hash slots; instead of query pruning, key access is direct.

Elasticsearch

# Route to the relevant shard with routing:
GET <index>/_search?routing=customer42
{ "query": { "term": { "customer_id": 42 } } }
# Query only the relevant indices with a time-based index pattern:
GET sale-2026-01/_search

Custom routing narrows the query to a single shard (similar to pruning). For time-series data, a date-patterned index name (sale-2026-01) ensures only the relevant indices are searched; use a narrow pattern instead of a wildcard.