Expression indexes and a GIN/JSONB preview

Capsule description

You have this query, ubiquitous in any system with case-insensitive login:

SELECT id FROM users WHERE lower(email) = lower($1);

You create a traditional index on email:

CREATE INDEX idx_users_email ON users(email);

You capture the plan. Seq Scan on users. Why? Your index is on the raw email. The query filters by lower(email). The lower() function wraps the column and breaks the match with the index.

The solution is an expression index: instead of indexing the raw column, you index the result of applying the function.

CREATE INDEX idx_users_email_lower ON users(lower(email));

Now WHERE lower(email) = ... uses the index. Plan: Index Scan.

This capsule teaches you to design expression indexes for queries with functions, casts, and expressions; the critical IMMUTABLE requirement (the rule that confuses people most); and a preview of when you need GIN for JSONB and arrays — without going deep, because that's guide #14.

Concrete objective: you'll be able to identify queries that break traditional indexes by applying functions, and create the right expression index the planner actually uses.

Mental model: index what the query actually searches for

A B-tree index searches by equality or the order of the indexed key. If your index is on email and the query searches by lower(email), those are two different things for the planner — even though conceptually we want the same thing.

Index on email:               Query: WHERE lower(email) = 'foo@bar.com'
  → keys: [Foo@Bar.com, FOO@bar.com, foo@bar.com, ...]
  → planner: "I'm ordered by email, but the query doesn't search by email,
              it searches by lower(email). I can't use the index."
              → Seq Scan + filter

Expression index on lower(email):   Query: WHERE lower(email) = 'foo@bar.com'
  → keys: [foo@bar.com, foo@bar.com, foo@bar.com, ...]   (all lowercase)
  → planner: "the index is ordered by lower(email). The query searches by
              lower(email). Perfect match."
              → Index Scan

Basic syntax

CREATE INDEX idx_name ON table(expression);

Examples:

-- Case-insensitive search
CREATE INDEX idx_users_email_lower ON users(lower(email));

-- Cast to date for queries that truncate time
CREATE INDEX idx_orders_date ON orders((created_at::date));

-- Computed expression
CREATE INDEX idx_invoices_total ON invoices((quantity * unit_price));

-- Concat
CREATE INDEX idx_users_full_name ON users((first_name || ' ' || last_name));

-- Built-in function
CREATE INDEX idx_logs_month ON logs(date_trunc('month', created_at));

Important: the query must use exactly the same expression (textually, after the planner's normalization) as the index. If the index is lower(email), the query must be WHERE lower(email) = .... If the query is WHERE upper(email) = ..., it isn't used.

The IMMUTABLE requirement: the rule that confuses people most

PostgreSQL classifies functions by their volatility:

Only IMMUTABLE functions can be indexed. Reason: the index stores the pre-computed results. If the function could return different values for the same input, the index would become inconsistent.

Common IMMUTABLE cases

CREATE INDEX ON users(lower(email));           -- ✅ lower is IMMUTABLE
CREATE INDEX ON books((pages * 2));            -- ✅ simple arithmetic
CREATE INDEX ON logs(length(message));         -- ✅ length is IMMUTABLE
CREATE INDEX ON addrs(upper(country_code));    -- ✅ upper is IMMUTABLE

Common cases that FAIL

-- ❌ now() is STABLE
CREATE INDEX ON sessions((now() - created_at));
-- ERROR: functions in index expression must be marked IMMUTABLE

-- ❌ random() is VOLATILE
CREATE INDEX ON tickets((random() * total));
-- ERROR

-- ❌ to_char with a timestamp and implicit timezone can be STABLE
CREATE INDEX ON logs(to_char(created_at, 'YYYY-MM'));
-- ⚠️ FAILS if created_at is TIMESTAMPTZ and you don't specify an explicit timezone

Subtle case: to_char with timestamps

-- created_at is TIMESTAMPTZ
CREATE INDEX ON logs(to_char(created_at, 'YYYY-MM'));
-- ERROR: functions in index expression must be marked IMMUTABLE

Why? to_char with TIMESTAMPTZ depends on TimeZone (a session setting). If the session changes timezone, the result changes. That's why it's STABLE, not IMMUTABLE.

Solution: specify an explicit timezone or use TIMESTAMP (without TZ).

-- Option 1: cast to a fixed timezone
CREATE INDEX ON logs(to_char(created_at AT TIME ZONE 'UTC', 'YYYY-MM'));

-- Option 2: use TIMESTAMP (without TZ) if your app always works in one zone
CREATE INDEX ON logs(to_char(created_at::timestamp, 'YYYY-MM'));

-- Option 3: use date_trunc (it's IMMUTABLE for a timestamp + text input)
CREATE INDEX ON logs(date_trunc('month', created_at));

date_trunc('month', timestamp) is IMMUTABLE and is usually the best alternative when you group by month/day.

How to verify a function's volatility

SELECT
  proname,
  CASE provolatile
    WHEN 'i' THEN 'IMMUTABLE'
    WHEN 's' THEN 'STABLE'
    WHEN 'v' THEN 'VOLATILE'
  END AS volatility
FROM pg_proc
WHERE proname IN ('lower', 'upper', 'now', 'random', 'date_trunc', 'to_char');

Output:

proname     | volatility
------------+-----------
lower       | IMMUTABLE
upper       | IMMUTABLE
now         | STABLE
random      | VOLATILE
date_trunc  | IMMUTABLE   (depends on the signature)
to_char     | STABLE       (with TIMESTAMPTZ)

(Some functions have multiple signatures with different volatilities. date_trunc(text, timestamp) is IMMUTABLE; with timestamp with time zone it may not be.)

User functions: marking as IMMUTABLE

If you have your own function that's mathematically deterministic, you can mark it:

CREATE OR REPLACE FUNCTION normalize_phone(phone TEXT)
RETURNS TEXT
LANGUAGE SQL
IMMUTABLE                    -- ← key
AS $$
    SELECT regexp_replace(phone, '[^0-9]', '', 'g');
$$;

-- Now you can index it
CREATE INDEX idx_users_phone_normalized ON users(normalize_phone(phone));

-- And queries that use the function leverage the index
SELECT * FROM users WHERE normalize_phone(phone) = '5491145678901';

Careful: marking a function IMMUTABLE that ISN'T (depends on an external table, calls now(), etc.) is dangerous: PostgreSQL accepts the index but the results can be inconsistent. Only mark IMMUTABLE if you're sure the function is mathematically deterministic for the same input.

Classic use cases

Case 1: case-insensitive search

CREATE INDEX idx_users_email_lower ON users(lower(email));

-- Queries
SELECT * FROM users WHERE lower(email) = lower($1);
SELECT * FROM users WHERE lower(email) LIKE 'foo%';   -- prefix still works

Why it matters: emails are typically stored in mixed upper and lower case. The user types in any capitalization. If you don't normalize with lower(), logins fail inconsistently. And if you normalize but don't index the expression, all logins are Seq Scan — a typical symptom of "slow login in production".

Alternative: the citext (case-insensitive text) extension stores the text and compares case-insensitively automatically. It's cleaner but requires the extension installed and a schema migration. lower() with an expression index is a lightweight and portable solution.

Case 2: group by month/day

CREATE INDEX idx_orders_month ON orders(date_trunc('month', created_at));

SELECT date_trunc('month', created_at) AS month, SUM(total)
FROM orders
WHERE date_trunc('month', created_at) = '2026-05-01'
GROUP BY month;

The index covers the WHERE date_trunc('month', ...) filter. Without it, it would be Seq Scan + filter over the whole table.

Alternative: filter by range without a function:

SELECT date_trunc('month', created_at) AS month, SUM(total)
FROM orders
WHERE created_at >= '2026-05-01' AND created_at < '2026-06-01'
GROUP BY month;

This does use a traditional index on created_at. It's generally better because the index on created_at also works for other queries (arbitrary ranges, ordering). The expression index on date_trunc only works for filtering by an exact month.

Rule: prefer rewriting the query to use ranges over creating an expression index on date_trunc. Only create the expression index if rewriting isn't viable.

Case 3: computed field

An invoices(quantity, unit_price) table. The queries filter by total:

SELECT * FROM invoices WHERE (quantity * unit_price) > 10000;

CREATE INDEX idx_invoices_total ON invoices((quantity * unit_price));

Now the filter uses the index. The result of the multiplication stays automatically up to date when quantity or unit_price change.

Alternative: a generated column (PostgreSQL 12+):

ALTER TABLE invoices ADD COLUMN total NUMERIC GENERATED ALWAYS AS (quantity * unit_price) STORED;
CREATE INDEX idx_invoices_total ON invoices(total);

More explicit in the schema. Works the same in queries. Trade-off: it takes up space in the heap (the generated column is stored), but there's no hidden magic in the index.

Case 4: text prefix

CREATE INDEX idx_books_title_prefix ON books(substr(title, 1, 50));

SELECT * FROM books WHERE substr(title, 1, 50) = 'The Lord of the';

Useful for long texts where only the prefix matters. The index stores only the first 50 characters, saving space.

Preview: when you need GIN (we don't go deep)

Up to here, everything is B-tree. PostgreSQL has other index types for cases where B-tree doesn't fit. The most relevant in modern backend is GIN (Generalized Inverted Index).

When you need GIN

GIN is for multi-value containment: rows where a column contains multiple elements and you want to search by "contains this element".

Basic syntax (preview)

-- Array of tags
CREATE TABLE posts (
    id SERIAL,
    tags TEXT[]
);
CREATE INDEX idx_posts_tags ON posts USING GIN(tags);

SELECT * FROM posts WHERE tags @> ARRAY['fastapi'];

-- JSONB
CREATE TABLE events (
    id SERIAL,
    metadata JSONB
);
CREATE INDEX idx_events_metadata ON events USING GIN(metadata);

SELECT * FROM events WHERE metadata @> '{"action": "login"}';

-- Full-text search
CREATE TABLE articles (
    id SERIAL,
    body TEXT
);
CREATE INDEX idx_articles_body_fts
ON articles USING GIN(to_tsvector('english', body));

SELECT * FROM articles
WHERE to_tsvector('english', body) @@ to_tsquery('english', 'database & performance');

Why we don't go deep here

GIN is excellent for its use cases, but it has serious trade-offs that require a whole module:

• Much slower on writes than B-tree (5-20x).
• Much larger on disk (sometimes 5-10x the size of the data).
• Fine configuration (fastupdate, gin_pending_list_limit) affects performance.
• JSONB has specific operators (@>, ?, ?|, ?&, jsonb_path_ops) with trade-offs.
• Full-text search requires understanding tsvector, tsquery, language configuration, ranking.

All of that is content of guide #14: Advanced PostgreSQL Features. Here you only care about recognizing when the case goes beyond B-tree and needs GIN. If your query uses @> over a JSONB or array, you know B-tree isn't the answer — but you'll see the full solution in #14.

Pattern to memorize for recognition

If you see unusual operators in the WHERE and B-tree doesn't work, the problem is almost always "you need GIN/GiST". Identify it and consult guide #14 to go deeper.

Worked example: case-insensitive login

You'll set up the table, see the problem, create the expression index, validate.

Setup

DROP TABLE IF EXISTS demo_users;
CREATE TABLE demo_users (
    id          BIGSERIAL PRIMARY KEY,
    email       TEXT NOT NULL,
    password    TEXT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

INSERT INTO demo_users (email, password)
SELECT
    -- Mix of capitalizations
    CASE (random() * 3)::INTEGER
        WHEN 0 THEN 'user' || g || '@example.com'
        WHEN 1 THEN 'User' || g || '@Example.com'
        ELSE 'USER' || g || '@EXAMPLE.COM'
    END,
    md5(random()::text)
FROM generate_series(1, 500000) g;

ANALYZE demo_users;

Initial plan: traditional index, the function breaks the matching

CREATE INDEX idx_users_email ON demo_users(email);

EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM demo_users WHERE lower(email) = 'user12345@example.com';

Expected output:

Seq Scan on demo_users
  Filter: (lower(email) = 'user12345@example.com'::text)
  Rows Removed by Filter: 499999
  Buffers: shared hit=...
Execution Time: 95ms

Seq Scan. The index on email isn't used because the query filters by lower(email).

Improved plan: expression index

DROP INDEX idx_users_email;
CREATE INDEX idx_users_email_lower ON demo_users(lower(email));

ANALYZE demo_users;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM demo_users WHERE lower(email) = 'user12345@example.com';

Expected output:

Index Scan using idx_users_email_lower on demo_users
  Index Cond: (lower(email) = 'user12345@example.com'::text)
  Buffers: shared hit=4
Execution Time: 0.234 ms

Comparison:

Key point: the query didn't change. The change is only the index.

Test with a partial expression UNIQUE

If you want to guarantee case-insensitive unique emails (regardless of capitalization):

DROP INDEX idx_users_email_lower;
CREATE UNIQUE INDEX idx_users_email_lower_unique
ON demo_users(lower(email));

-- This WORKS
INSERT INTO demo_users (email, password) VALUES ('test@new.com', 'pass');

-- This FAILS (lower(email) = 'test@new.com' already exists)
INSERT INTO demo_users (email, password) VALUES ('TEST@NEW.COM', 'pass');
-- ERROR: duplicate key value violates unique constraint "idx_users_email_lower_unique"

A very useful pattern to avoid email duplicates "but with different capitalization", which otherwise slip through.

Why does this matter in real work?

1. Slow login in production is this problem 80% of the time.

An app with WHERE lower(email) = ... without an expression index = Seq Scan on every login. On a 1M-user table, each login is 800ms+. The user waits, abandons, support gets complaints. The fix is one line of SQL and the improvement is 400x. It's one of the fastest wins in the whole guide.

2. Reports with date_trunc become viable.

Dashboards that group by month (SUM(total) GROUP BY date_trunc('month', created_at)) without an index take minutes. With an expression index on date_trunc, seconds.

3. The STABLE vs IMMUTABLE difference avoids frustrating errors.

Creating CREATE INDEX ON logs(to_char(created_at, 'YYYY-MM')) and getting ERROR: functions in index expression must be marked IMMUTABLE stops you cold if you don't understand why. Knowing the reason and having the three solutions (timezone cast, TIMESTAMP, date_trunc) saves you hours of Google.

4. Recognizing "this is GIN, not B-tree" saves you useless optimizations.

If a query uses @> over JSONB and you put B-tree on it, you'll fail three times before accepting that you need another index type. Recognizing the pattern takes you straight to guide #14.

Traps and common mistakes

Mistake 1 (conceptual): the query doesn't use the index's exact expression

Symptom: you create CREATE INDEX ON users(lower(email)). The query is WHERE LOWER(email) = ... (uppercase). Does it work?

Analysis: SQL is case-insensitive for keywords (LOWER and lower are equivalent for the parser). But the planner normalizes, so it should use it. More subtle cases:

-- Index
CREATE INDEX ON users(lower(email));

-- ✅ Does use it
WHERE lower(email) = 'foo'

-- ❌ Does NOT use it (the function wraps something different)
WHERE lower(trim(email)) = 'foo'

-- ❌ Does NOT use it (a variation of the expression)
WHERE substring(lower(email) from 1 for 5) = 'foo@b'

How to fix it: make sure the query uses literally the same expression as the index. Or create another index for the variation.

Mistake 2 (practical): trying to index a non-IMMUTABLE function

Symptom:

CREATE INDEX ON sessions(now() - last_active);
-- ERROR: functions in index expression must be marked IMMUTABLE

Why it happens: now() is STABLE, not IMMUTABLE. The index can't be created.

How to fix it: reformulate the query to index something IMMUTABLE. Instead of "time since the last activity", index last_active and filter:

CREATE INDEX ON sessions(last_active);
-- Query
SELECT * FROM sessions WHERE last_active < NOW() - INTERVAL '1 hour';

The index covers the range and the query is functionally equivalent.

Mistake 3 (conceptual): marking a function IMMUTABLE that isn't

Symptom: you mark IMMUTABLE a function that internally reads a table or calls now(). PostgreSQL accepts the index. Later "lost" or "duplicate" rows appear.

Why it's dangerous: the index stores pre-computed results assuming the function is deterministic. If the function can return something different for the same input, the index falls out of sync with reality.

How to fix it: don't mark IMMUTABLE what isn't. If you need to index something dependent on an external table, rethink the design (denormalize? a generated column with a static value?). Don't fool the planner.

Mistake 4 (conceptual): an expression index where an index + rewriting the query works better

Symptom: you create CREATE INDEX ON orders(date_trunc('day', created_at)) for queries that filter by day.

Why it's sometimes sub-optimal: an index on created_at (without a function) works for WHERE created_at >= X AND created_at < Y (a range), which is equivalent to "of day Y" and also works for many other ranges. More versatile.

How to fix it: first evaluate rewriting the query with ranges. Only create an expression index if the query can't be rewritten or a range doesn't apply.

Mistake 5 (conceptual): an expression index over a broken cast

Symptom:

CREATE INDEX ON orders((created_at::date));

-- Query
WHERE created_at::date = '2026-05-01'

It works, but it loses range. If the query is WHERE created_at::date BETWEEN '2026-05-01' AND '2026-05-31', the expression index works. But if it's WHERE created_at >= '2026-05-01 14:00' AND created_at < '2026-05-01 15:00' (an hourly filter), the expression index does NOT work because created_at::date loses the time.

How to fix it: index created_at directly for queries with an hourly range. The expression index on created_at::date is for queries that ONLY filter by a full day. Know the queries before choosing.

Mistake 6 (conceptual): JSONB with an expression index where GIN is needed

Symptom:

CREATE INDEX ON events((metadata->>'user_id'));

-- Query
WHERE metadata @> '{"user_id": "42"}';

The index isn't used. Reason: metadata @> '...' is a GIN operator, it doesn't use the expression index on metadata->>'user_id'.

How to fix it: if you filter by ->> or -> with a specific field, an expression index works. If you filter with @>, ?, etc. (containment), you need GIN. Recognize the operator and choose the right index type.

-- For WHERE metadata->>'user_id' = '42'
CREATE INDEX ON events((metadata->>'user_id'));

-- For WHERE metadata @> '{"user_id": "42"}'
CREATE INDEX ON events USING GIN(metadata);
-- Or for more specific queries:
CREATE INDEX ON events USING GIN((metadata->'user_id'));

(Deeper dive: guide #14.)

Exercises

Exercise 1: identify queries that need an expression index

For each query, decide whether a traditional index works or it needs an expression index. Justify.

1. WHERE email = 'foo@bar.com'
2. WHERE lower(email) = 'foo@bar.com'
3. WHERE name LIKE 'Juan%'
4. WHERE name ILIKE 'juan%' (insensitive)
5. WHERE created_at::date = '2026-05-01'
6. WHERE created_at >= '2026-05-01' AND created_at < '2026-05-02'
7. WHERE quantity * price > 1000
8. WHERE jsonb_data->>'role' = 'admin'

Exercise 2: diagnose the IMMUTABLE error

You try to create this index:

CREATE INDEX idx_logs_month
ON logs(to_char(created_at, 'YYYY-MM'));
-- ERROR: functions in index expression must be marked IMMUTABLE

Diagnose it and propose three functional alternatives.

CREATE INDEX idx_logs_month
ON logs(to_char(created_at AT TIME ZONE 'UTC', 'YYYY-MM'));

CREATE INDEX idx_logs_month
ON logs(date_trunc('month', created_at));

-- Queries
SELECT * FROM logs
WHERE date_trunc('month', created_at) = '2026-05-01';

CREATE INDEX idx_logs_created ON logs(created_at);

-- Queries
SELECT * FROM logs
WHERE created_at >= '2026-05-01' AND created_at < '2026-06-01';

Exercise 3: validate the IMMUTABLE of built-in functions

Query pg_proc to verify the volatility of these functions. For each one, say whether it can be indexed.

1. lower(text)
2. now()
3. current_timestamp
4. random()
5. md5(text)
6. length(text)
7. nextval(regclass)
8. extract(month from timestamp)

SELECT proname, provolatile
FROM pg_proc
WHERE proname IN ('lower', 'now', 'random', 'md5', 'length', 'nextval', 'extract');
-- (extract doesn't appear directly; it's an internal operator)

Exercise 4: rewrite a query to avoid an expression index

You have:

CREATE INDEX idx_orders_created ON orders(created_at);

-- Query
SELECT * FROM orders WHERE created_at::date = '2026-05-01';

Current plan: Seq Scan (because the cast breaks the use of the traditional index).

Rewrite the query so it uses the traditional index without needing an expression index.

SELECT * FROM orders
WHERE created_at >= '2026-05-01'::timestamp
  AND created_at < '2026-05-02'::timestamp;

Index Scan using idx_orders_created on orders
  Index Cond: ((created_at >= '2026-05-01 00:00:00'::timestamp without time zone)
               AND (created_at < '2026-05-02 00:00:00'::timestamp without time zone))

Exercise 5: identify the GIN case

For each query, say whether it needs B-tree (with or without an expression) or whether it needs GIN.

1. WHERE tags @> ARRAY['fastapi']
2. WHERE tags = ARRAY['fastapi', 'python']
3. WHERE jsonb_data->>'role' = 'admin'
4. WHERE jsonb_data @> '{"role": "admin"}'
5. WHERE to_tsvector('english', body) @@ to_tsquery('english', 'database & performance')
6. WHERE name ILIKE '%foo%'
7. WHERE name ILIKE 'foo%'

Exercise 6: apply it to your own case

Find in your app a query that uses a function or cast in the WHERE over an indexed column, and verify whether the traditional index is being used or not. If it's not used, create the right expression index and measure the difference.

## Case: login with case-insensitive email

**Query:**
```sql
SELECT id, password FROM users WHERE lower(email) = lower($1);

Seq Scan on users
  Filter: (lower(email) = lower($1))
  Buffers: shared hit=...
Execution Time: 250ms

CREATE INDEX idx_users_email_lower ON users(lower(email));
ANALYZE users;

Index Scan using idx_users_email_lower on users
  Index Cond: (lower(email) = lower($1))
  Buffers: shared hit=4
Execution Time: 0.5ms

CREATE UNIQUE INDEX idx_users_email_lower_unique
ON users(lower(email))
WHERE deleted_at IS NULL;  -- combining expression + partial

If your app doesn't have this pattern, look for one of these common ones:

- Case-insensitive search (`lower(name) LIKE ...`).
- Filtering by day/month (`date_trunc(...)`).
- Filtering by a jsonb field (`jsonb_data->>'key'`).
- A derived calculation in the WHERE (`a + b > X`).

</details>

---

## Summary and next step

In this capsule you learned:

- An **expression index** indexes the result of an expression, not the raw column.
- Necessary when the query applies a function or cast: `WHERE lower(email) = ...`, `WHERE created_at::date = ...`, `WHERE qty * price > ...`.
- Only **IMMUTABLE** functions can be indexed. `lower`, `length`, `md5`, `date_trunc(text, timestamp)` are. `now()`, `random()`, `to_char(timestamptz, ...)` aren't.
- For `to_char` with `TIMESTAMPTZ`: use a cast to UTC, or `TIMESTAMP` (without TZ), or `date_trunc`.
- User functions marked `IMMUTABLE` are also indexable — but only mark it if the function really is deterministic.
- **Before creating an expression index, evaluate rewriting the query with ranges** over a traditional index. More versatile.
- **GIN preview**: if the query uses `@>`, `?`, `@@` (over arrays, JSONB, FTS), B-tree doesn't work. You need GIN. A deeper dive in guide #14.

Before moving on, you should be able to:

- Identify queries that break traditional indexes by applying functions.
- Create an expression index with the right syntax.
- Diagnose the `must be marked IMMUTABLE` error and propose alternatives.
- Recognize when the WHERE operator indicates GIN instead of B-tree.

**Next capsule — Index maintenance: bloat, REINDEX, and detecting unused ones.** Up to here you've created indexes. Now you'll learn to **maintain** them: detect unused indexes with `pg_stat_user_indexes` (the recurring discipline few people do), understand what index bloat is and when it matters, run `REINDEX CONCURRENTLY` without blocking writes, and quantify the cost of each index added on INSERT/UPDATE/DELETE. It's the capsule that closes the conversation: adding an index is a decision, maintaining an index is a decision.

---

## Resources

1. [Markus Winand — Use The Index, Luke! — "Functions"](https://use-the-index-luke.com/sql/where-clause/functions) — the canonical explanation of why functions break indexes and how expression indexes solve it.
2. [PostgreSQL Documentation — Indexes on Expressions](https://www.postgresql.org/docs/16/indexes-expressional.html) — the official chapter on expression indexes in PostgreSQL 16.
3. [PostgreSQL Documentation — Function Volatility Categories](https://www.postgresql.org/docs/16/xfunc-volatility.html) — the precise definition of IMMUTABLE, STABLE, VOLATILE.
4. [Hubert "depesz" Lubaczewski — "Functional indexes and queries"](https://www.depesz.com/2007/05/13/functional-indexes-and-queries/) — real cases with before/after plans.
5. [PostgreSQL Documentation — GIN Indexes](https://www.postgresql.org/docs/16/gin.html) — a GIN preview for when you need it in guide #14.
6. [PostgreSQL Documentation — JSONB Indexing](https://www.postgresql.org/docs/16/datatype-json.html#JSON-INDEXING) — the official reference for indexing JSONB. A deeper dive in guide #14.
7. [Tom Lane on IMMUTABLE function marking](https://www.postgresql.org/message-id/9192.1320599580%40sss.pgh.pa.us) — a technical discussion of why marking IMMUTABLE incorrectly is dangerous.

---

*Module 3 — Database Performance & Query Tuning Guide*