Can my DB?

-- Index only active users (omits the much larger set of inactive users)
CREATE INDEX idx_active_users ON users(email)
  WHERE is_active = true;

-- Unique partial index: enforce uniqueness only among active records
CREATE UNIQUE INDEX idx_unique_active_email ON users(email)
  WHERE is_active = true;

-- Queue processing: index only unprocessed rows
CREATE INDEX idx_pending_jobs ON jobs(created_at)
  WHERE status = 'pending';

-- Query uses the partial index automatically
SELECT * FROM users WHERE email = 'alice@example.com' AND is_active = true;

-- View partial indexes
SELECT indexname, indexdef
FROM   pg_indexes
WHERE  tablename = 'users'
  AND  indexdef LIKE '%WHERE%';

• WHERE clause must be IMMUTABLE.
• Queries must include a WHERE clause that implies the index predicate for the planner to pick it.

-- MySQL does not support partial or filtered indexes.
-- Workaround 1: index a generated column with conditional logic
ALTER TABLE users
  ADD COLUMN active_email VARCHAR(255)
    AS (IF(is_active = 1, email, NULL)) VIRTUAL,
  ADD INDEX idx_active_email (active_email);

-- Queries can then use:
SELECT * FROM users WHERE active_email = 'alice@example.com';

-- Workaround 2: use a view (read-only)
CREATE VIEW active_users AS
  SELECT * FROM users WHERE is_active = 1;

-- Workaround 3: partition the table by status and use partition pruning

• No WHERE on CREATE INDEX. The usual substitute is an expression index or virtual generated column that returns NULL for excluded rows — NULLs aren't indexed.

-- MariaDB does not support partial indexes.
-- Same workaround as MySQL: index a virtual generated column
ALTER TABLE users
  ADD COLUMN active_email VARCHAR(255)
    AS (IF(is_active = 1, email, NULL)) VIRTUAL,
  ADD INDEX idx_active_email (active_email);

SELECT * FROM users WHERE active_email = 'alice@example.com';

• No WHERE on CREATE INDEX. Index a VIRTUAL generated column that returns NULL for excluded rows.

-- Oracle does not support partial indexes with a WHERE predicate.
-- Workaround: function-based index using a CASE expression
-- Rows that should be excluded return NULL (not indexed)
CREATE INDEX idx_active_email
  ON users (CASE WHEN is_active = 1 THEN email ELSE NULL END);

-- Query must match the expression exactly
SELECT * FROM users
WHERE  CASE WHEN is_active = 1 THEN email ELSE NULL END = 'alice@example.com';

-- More natural query rewrite (optimizer may still use the index)
SELECT * FROM users
WHERE  email = 'alice@example.com'
  AND  is_active = 1;

• No WHERE on CREATE INDEX. Function-based index on CASE ... THEN col ELSE NULL END is the usual trick, since NULL values aren't indexed.

-- Filtered index on active users only
CREATE INDEX idx_active_users
  ON users (email)
  WHERE is_active = 1;

-- Unique filtered index: one active record per email
CREATE UNIQUE INDEX idx_unique_active_email
  ON users (email)
  WHERE is_active = 1;

-- Filtered index on a non-nullable sparse column
CREATE INDEX idx_pending_jobs
  ON jobs (created_at)
  WHERE status = 'pending';

-- Query automatically uses the filtered index
SELECT * FROM users WHERE email = 'alice@example.com' AND is_active = 1;

-- View filtered indexes
SELECT name, filter_definition
FROM   sys.indexes
WHERE  object_id = OBJECT_ID('users')
  AND  filter_definition IS NOT NULL;

• Added in 2008. Filter predicate accepts =, <>, >, <, >=, <=, IS NULL, IS NOT NULL, IN, NOT IN, and AND — but not OR.
• The filter also produces filtered statistics, which are often more selective than full-table stats.
• Predicate is in sys.indexes.filter_definition.

-- Partial index on active users only
CREATE INDEX idx_active_users
  ON users (email)
  WHERE is_active = 1;

-- Unique partial index
CREATE UNIQUE INDEX idx_unique_active_email
  ON users (email)
  WHERE is_active = 1;

-- Queue: index only pending jobs
CREATE INDEX idx_pending_jobs
  ON jobs (created_at)
  WHERE status = 'pending';

-- Query uses partial index automatically
SELECT * FROM users WHERE email = 'alice@example.com' AND is_active = 1;

-- Check index usage with EXPLAIN QUERY PLAN
EXPLAIN QUERY PLAN
  SELECT * FROM users WHERE email = 'alice@example.com' AND is_active = 1;

• Added in 3.8.9.

-- Redshift has no user-defined indexes.
-- Use sort keys for range-based access patterns:
CREATE TABLE orders (
  id         BIGINT,
  status     VARCHAR(20),
  created_at TIMESTAMP
)
SORTKEY (status, created_at);

-- Zone maps (automatic min/max metadata per block) provide
-- some of the same benefits as partial indexes for range scans.
-- Use WHERE clauses that align with the sort key order.

• No indexes. Columnar storage, zone maps, and SORTKEY handle most of the same query patterns.

-- DB2 does not have a direct WHERE clause on CREATE INDEX.
-- Workaround: create an index on a generated column
ALTER TABLE users ADD COLUMN active_email
  VARCHAR(255) GENERATED ALWAYS AS
  (CASE WHEN is_active = 1 THEN email ELSE NULL END);

CREATE INDEX idx_active_email ON users(active_email);

-- Query using the generated column
SELECT * FROM users WHERE active_email = 'alice@example.com';

-- DB2 also supports indexes on expression values in some versions:
CREATE INDEX idx_expr ON users
  (CASE WHEN is_active = 1 THEN email ELSE NULL END);

• No WHERE on CREATE INDEX. Closest substitutes: an expression/generated-column index that excludes rows via NULL, or local indexes on a partitioned table.

-- Snowflake has no user-defined indexes.
-- Cluster keys physically co-locate similar rows for micro-partition pruning:
ALTER TABLE orders CLUSTER BY (status, order_date);

-- Search Optimization Service can accelerate selective point lookups:
ALTER TABLE orders ADD SEARCH OPTIMIZATION;

-- Pruning happens automatically based on micro-partition metadata.
-- A query like this benefits from clustering on status:
SELECT * FROM orders WHERE status = 'pending' AND order_date > '2024-01-01';

• No indexes. Pruning comes from automatic micro-partition min/max metadata; CLUSTER BY and Search Optimization cover tuned lookups.

-- BigQuery has no user-defined B-Tree or partial indexes.
-- Partitioned tables provide time-based pruning:
CREATE TABLE orders
PARTITION BY DATE(order_date)
AS SELECT * FROM staging.orders;

-- Clustered tables co-locate rows for pruning:
CREATE TABLE orders
PARTITION BY DATE(order_date)
CLUSTER BY status, customer_id
AS SELECT * FROM staging.orders;

-- Search Index for SEARCH() function (managed, not user-defined):
CREATE SEARCH INDEX idx ON orders (ALL COLUMNS);

-- Selective queries benefit from partition + cluster pruning:
SELECT * FROM orders
WHERE order_date = '2024-01-15'
  AND status = 'pending';

• No B-tree indexes. PARTITION BY and CLUSTER BY drive pruning; CREATE SEARCH INDEX feeds the SEARCH() function for substring lookups.