Can my DB?

-- Case-insensitive email lookup
CREATE INDEX idx_email_lower ON users (lower(email));

-- Query must use the same expression to hit the index
SELECT * FROM users WHERE lower(email) = 'alice@example.com';

-- JSONB field extraction
CREATE INDEX idx_payload_type ON events ((payload->>'event_type'));
SELECT * FROM events WHERE payload->>'event_type' = 'click';

-- Date truncation for month-level grouping
CREATE INDEX idx_month ON orders (date_trunc('month', created_at));

-- Compound expression index
CREATE INDEX idx_full_name ON users (lower(first_name || ' ' || last_name));

-- List indexes with their definitions
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'users';

• Expression must use only IMMUTABLE functions (output determined solely by input).
• Every INSERT/UPDATE re-evaluates the expression, so writes pay for it even if reads never benefit.

-- Expression index syntax: expression wrapped in double parentheses
CREATE INDEX idx_email_lower ON users ((lower(email)));

-- Query uses the expression index
SELECT * FROM users WHERE lower(email) = 'alice@example.com';

-- Functional index on a JSON field
CREATE INDEX idx_payload_type ON events ((payload->>'$.event_type'));

-- Multi-column expression index
CREATE INDEX idx_year_month ON orders ((YEAR(created_at)), (MONTH(created_at)));

-- Show index details
SHOW INDEX FROM users;

• Added in 8.0. Double-parenthesize the expression in the DDL — (( ... )) — or MySQL reads it as a column reference.
• Implemented behind the scenes as a hidden generated column. Expression must be deterministic.

-- MariaDB does not support direct expression indexes.
-- Workaround: create a VIRTUAL generated column and index it
ALTER TABLE users
  ADD COLUMN email_lower VARCHAR(255)
    AS (LOWER(email)) VIRTUAL,
  ADD INDEX idx_email_lower (email_lower);

-- The query must reference the generated column explicitly
SELECT * FROM users WHERE email_lower = 'alice@example.com';

-- Alternatively: store the column with PERSISTENT (materialize it)
ALTER TABLE users
  ADD COLUMN email_lower VARCHAR(255)
    AS (LOWER(email)) PERSISTENT,
  ADD INDEX idx_email_lower (email_lower);

• No direct expression syntax (unlike MySQL 8.0+). Index a VIRTUAL or PERSISTENT generated column and reference the column name — not the expression — in queries.

-- Case-insensitive email lookup
CREATE INDEX idx_email_upper ON users (UPPER(email));

-- Query must use the same expression
SELECT * FROM users WHERE UPPER(email) = 'ALICE@EXAMPLE.COM';

-- Arithmetic expression index
CREATE INDEX idx_total_tax ON orders (amount * 1.1);
SELECT * FROM orders WHERE amount * 1.1 > 100;

-- Date extraction
CREATE INDEX idx_order_year ON orders (EXTRACT(YEAR FROM order_date));
SELECT * FROM orders WHERE EXTRACT(YEAR FROM order_date) = 2024;

-- Verify function-based indexes are used
EXPLAIN PLAN FOR
  SELECT * FROM users WHERE UPPER(email) = 'ALICE@EXAMPLE.COM';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

• Shipped in 8i. Needs QUERY_REWRITE_ENABLED = TRUE (the default) and the relevant QUERY REWRITE privilege.
• Virtual columns (11g+) are an alternative: declare col AS (expr) VIRTUAL and build a normal index on it.

-- SQL Server does not support expressions directly in CREATE INDEX.
-- Step 1: add a persisted computed column
ALTER TABLE users
  ADD email_lower AS LOWER(email) PERSISTED;

-- Step 2: create a regular index on the computed column
CREATE INDEX idx_email_lower ON users (email_lower);

-- Query the computed column directly
SELECT * FROM users WHERE email_lower = 'alice@example.com';

-- Non-persisted computed column can also be indexed (with restrictions)
ALTER TABLE users ADD email_lower_virt AS LOWER(email);
CREATE INDEX idx_email_lower_virt ON users (email_lower_virt);

-- View computed column definitions
SELECT name, definition, is_persisted
FROM   sys.computed_columns
WHERE  object_id = OBJECT_ID('users');

• No expression syntax on CREATE INDEX directly. Add a computed column (PERSISTED, or non-PERSISTED if deterministic and precise) and index it.
• The optimizer can match a WHERE-clause expression to the computed column automatically — queries don't always have to reference the column by name.

-- Case-insensitive email lookup
CREATE INDEX idx_email_lower ON users (lower(email));

-- Query uses the expression index
SELECT * FROM users WHERE lower(email) = 'alice@example.com';

-- Arithmetic expression index
CREATE INDEX idx_discounted ON products (price * 0.9);
SELECT * FROM products WHERE price * 0.9 < 50;

-- Verify with EXPLAIN QUERY PLAN
EXPLAIN QUERY PLAN
  SELECT * FROM users WHERE lower(email) = 'alice@example.com';

• Added in 3.9.0. Expression must be deterministic.

-- Redshift has no user-defined indexes.
-- For expression-based filtering, use computed columns or sort keys:
CREATE TABLE users (
  id         BIGINT,
  email      VARCHAR(255),
  email_lower VARCHAR(255) DEFAULT NULL
)
SORTKEY (email_lower);

-- Or use a materialized view to pre-compute the expression:
CREATE MATERIALIZED VIEW users_lower AS
  SELECT id, LOWER(email) AS email_lower FROM users;

SELECT * FROM users_lower WHERE email_lower = 'alice@example.com';

• No indexes. Store the expression in a column and SORTKEY it, or expose it through a materialized view.

-- Case-insensitive lookup
CREATE INDEX idx_email_lower ON users (LOWER(email));

-- Query using the expression
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';

-- Expression on multiple columns
CREATE INDEX idx_full_name ON employees (UPPER(last_name || ',' || first_name));

-- View index details
SELECT indname, colnames
FROM   syscat.indexes
WHERE  tabname = 'USERS';

• Expression must be deterministic. Generated columns (GENERATED ALWAYS AS (expr)) can also be indexed as an alternative.

-- Snowflake has no user-defined expression indexes.
-- Use a materialized view or a precomputed column instead:
CREATE MATERIALIZED VIEW users_lower AS
  SELECT id, LOWER(email) AS email_lower
  FROM users;

SELECT * FROM users_lower WHERE email_lower = 'alice@example.com';

-- Search Optimization accelerates direct-column lookups, not arbitrary expressions:
ALTER TABLE users ADD SEARCH OPTIMIZATION ON EQUALITY(email);

• No indexes and no generated columns on standard tables. Use a precomputed column plus CLUSTER BY or Search Optimization, or expose the expression via a materialized view.
• Search Optimization targets direct columns, not arbitrary expressions.

-- BigQuery has no user-defined indexes.
-- Use a clustered table on a pre-computed column:
CREATE TABLE users
CLUSTER BY email_lower AS
  SELECT *, LOWER(email) AS email_lower FROM staging.users;

-- Or use a materialized view to pre-compute expressions:
CREATE MATERIALIZED VIEW users_mv AS
  SELECT id, LOWER(email) AS email_lower FROM mydataset.users;

SELECT * FROM users_mv WHERE email_lower = 'alice@example.com';

• No indexes. Materialize the expression as a column and CLUSTER BY it, or expose it through a materialized view.