PARTIAL / FILTERED INDEXIndexes & Search
Buy Me A Coffee

An index that covers only a subset of rows, defined by a WHERE condition. Produces smaller, faster indexes by excluding rows that are never queried via that index path.

An index built on a subset of rows defined by a WHERE clause, reducing size and maintenance overhead. Excellent for tables with frequently queried rare values (e.g., active users only, undeleted records). Only supports queries matching the filter predicate.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✗ Not Supported — MySQL does not support partial or filtered indexes with a WHERE clause. The workaround is to use a generated (computed) column that returns NULL for excluded rows, then add an index — NULL values are not stored in MySQL's B-tree indexes, achieving a similar effect. Functional indexes (MySQL 8.0+) are also an option for expression-based selectivity.
PostgreSQL ✓ Supported 7.2 WHERE clause on CREATE INDEX supported since PostgreSQL 7.2. The partial index is only used by queries whose WHERE clause is compatible with (implies) the index predicate. Works with B-tree, GiST, GIN, and BRIN index types. Extremely useful for sparse conditions (e.g., only ~5% of rows are active).
SQL Server ✓ Supported 2008 Called filtered indexes. WHERE clause on CREATE INDEX supported since SQL Server 2008. The filtered index is considered by the optimizer when the query's WHERE clause is compatible with the filter predicate. Statistics are also maintained only for the filtered rows, improving cardinality estimation. Cannot use non-deterministic functions or reference computed columns in the filter.
Oracle ✗ Not Supported — Oracle does not support a WHERE clause on CREATE INDEX directly. However, Oracle does not index NULL values in a standard B-tree index — a common workaround is to create a function-based index that returns NULL for rows to exclude. Bitmap indexes can also achieve selective indexing. Oracle 21c introduced the concept of invisible indexes and more advanced filtering via expression indexes.
SQLite ✓ Supported 3.8.9 Partial indexes with a WHERE clause supported since SQLite 3.8.9 (2015-03-09). The query's WHERE clause must imply the index predicate for the optimizer to use it. Works with all SQLite column types.

Details

Partial indexes are one of the highest-impact indexing tools available: if 5% of rows match the filter condition, the index is 95% smaller, fits in cache, and updates are faster. PostgreSQL (7.2+), SQL Server (2008+), and SQLite (3.8.9+) have direct WHERE clause support on CREATE INDEX. MySQL and Oracle do not — MySQL's workaround uses generated columns (since NULL values are not indexed); Oracle uses function-based indexes returning NULL for excluded rows. A unique partial index is a particularly powerful pattern: it enforces uniqueness only within the matching subset (e.g., unique email among active users only), which is impossible to express with a regular unique constraint.

Standard Syntax

-- PostgreSQL: CREATE INDEX idx_active_users ON users (email) WHERE active = true; -- SQL Server: CREATE INDEX idx_pending_orders ON orders (created_at) WHERE status = 'pending';

Version Support

MySQL: Not supported PostgreSQL: Since 7.2 SQL Server: Since 2008 Oracle: Not supported SQLite: Since 3.8.9

Per-Database Syntax & Notes

MySQL

MySQL does not support partial or filtered indexes with a WHERE clause. The workaround is to use a generated (computed) column that returns NULL for excluded rows, then add an index — NULL values are not stored in MySQL's B-tree indexes, achieving a similar effect. Functional indexes (MySQL 8.0+) are also an option for expression-based selectivity.

-- Workaround: generated column + index (NULLs not indexed): ALTER TABLE users ADD COLUMN active_email VARCHAR(255) GENERATED ALWAYS AS (IF(active = 1, email, NULL)) VIRTUAL; CREATE INDEX idx_active_email ON users (active_email); -- Now a query for active users can use the index: SELECT * FROM users WHERE active_email = 'alice@example.com'; -- This is equivalent to: WHERE active = 1 AND email = 'alice@example.com'

PostgreSQL

WHERE clause on CREATE INDEX supported since PostgreSQL 7.2. The partial index is only used by queries whose WHERE clause is compatible with (implies) the index predicate. Works with B-tree, GiST, GIN, and BRIN index types. Extremely useful for sparse conditions (e.g., only ~5% of rows are active).

-- Index only on active users: CREATE INDEX idx_active_users_email ON users (email) WHERE active = true; -- Index only on unprocessed rows: CREATE INDEX idx_pending_orders ON orders (created_at) WHERE status = 'pending'; -- Unique partial index (common pattern: allow multiple NULLs but enforce -- uniqueness among non-NULL values): CREATE UNIQUE INDEX idx_unique_active_username ON users (username) WHERE active = true; -- Compound column + partial: CREATE INDEX idx_recent_errors ON events (user_id, occurred_at) WHERE event_type = 'error' AND occurred_at > NOW() - INTERVAL '90 days';

SQL Server

Called filtered indexes. WHERE clause on CREATE INDEX supported since SQL Server 2008. The filtered index is considered by the optimizer when the query's WHERE clause is compatible with the filter predicate. Statistics are also maintained only for the filtered rows, improving cardinality estimation. Cannot use non-deterministic functions or reference computed columns in the filter.

-- Filtered index on pending orders only: CREATE INDEX idx_pending_orders ON orders (created_at) WHERE status = 'pending'; -- Filtered unique index: CREATE UNIQUE INDEX idx_unique_active_email ON users (email) WHERE is_deleted = 0; -- Filtered index on non-NULL column (reduce index size): CREATE INDEX idx_orders_promo ON orders (promo_code) WHERE promo_code IS NOT NULL;

Oracle

Oracle does not support a WHERE clause on CREATE INDEX directly. However, Oracle does not index NULL values in a standard B-tree index — a common workaround is to create a function-based index that returns NULL for rows to exclude. Bitmap indexes can also achieve selective indexing. Oracle 21c introduced the concept of invisible indexes and more advanced filtering via expression indexes.

-- Workaround: function-based index returning NULL for excluded rows: -- Index only active users (active=1): CREATE INDEX idx_active_users ON users (CASE WHEN active = 1 THEN email END); -- Query must match the expression to use the index: SELECT * FROM users WHERE CASE WHEN active = 1 THEN email END = 'alice@example.com'; -- More practical: filter via NOT NULL expression: CREATE INDEX idx_pending ON orders (CASE WHEN status = 'PENDING' THEN order_id END);

SQLite

Partial indexes with a WHERE clause supported since SQLite 3.8.9 (2015-03-09). The query's WHERE clause must imply the index predicate for the optimizer to use it. Works with all SQLite column types.

-- Partial index on active users: CREATE INDEX idx_active_users ON users (email) WHERE active = 1; -- Unique partial index: CREATE UNIQUE INDEX idx_unique_active_username ON users (username) WHERE active = 1; -- Index on non-NULL values only: CREATE INDEX idx_tagged_posts ON posts (tag) WHERE tag IS NOT NULL;