Indexing

e08ef650-b436-4c0e-949c-6a4c7ad3daea

Indexing

Node Details

Public graph metadata

Updated5/1/2026

PageRank0.0001

Effectiveness0.000

Connections

21 linked nodes

PERTAIN_TORootCause

The query needs to satisfy OR predicates across different JSONB keys and an ORDER BY created_at, so separate partial expression indexes duplicate the timestamp column

PERTAIN_TOProblem

Selecting a more optimal index for a PostgreSQL query that filters on multiple fields inside a JSONB column

PERTAIN_TOProblem

as long as the `realm_id` has a name which status is neither `closed` nor `denied`, it'll be marked as `active`, otherwise, it's `inactive` — aggregate based on `realm_id` first;. Tension: I've tried to use aggregate and left outer join, but no luck thus far. Outcome: You can use `bool_or()` aggregation function.

PERTAIN_TOSolution

on 2M rows it takes `575ms` on average, having to run a sequential scan. Tension: Unfortunately, it can't use an index which is why on 2M rows it takes `575ms` on average. Outcome: The skip scan runs 56x faster, under `11ms` using index-onlies.

PERTAIN_TOProblem

none of the three queries you showed ended up using it — Select * from users where Type = 'SomeType' and First = 'SomeName1' Order by CreatedAt DESC;. Tension: The fact that you're selecting and ordering by `CreatedAt` doesn't mean it's needed as an index key. Outcome: If you simply remove `createdat` from the index, they're happy to use it for a Bitmap Index Scan taking below `5ms`.

PERTAIN_TOSolution

first, middle, and last varies. Tension: You can add more tweaks depending on how much input latency you're ready to tolerate and how much space you're willing to spend. Outcome: Thorsten Kettner.

PERTAIN_TOSolution

`(Type) incl. Tension: first, middle, and last varies.

PERTAIN_TOSolution

So, an index for the first query would look like this: — For the first query. Tension: all those matches would already be sorted. Outcome: create index idx_1 on users (first, type, createdat desc);.

PERTAIN_TOSolution

If you only wanted to provide a single index for all queries instead — all three queries have in common. Tension: I doubt the DBMS would use the index. Outcome: then you'd index the type.

PERTAIN_TOSolution

Add separate indexes on (external_id, email) and (external_id, uuid) so the planner can use a BitmapOr operation

PERTAIN_TOProblem

The data will get removed from the index by some future vacuum. — Does PostgreSQL remove values from partial index if conditon is no longer met? Tension: freed up space is only reusable under one of two conditions. Outcome: you would need a REINDEX.

PERTAIN_TOSolution

if your goal is to currently shrink the index — If neither of those conditions are met. Tension: not just reuse space in the future to forestall future growth. Outcome: you would need a REINDEX.

PERTAIN_TOProblem

Updating a jsonb column to remove specific keys (e.g., value1 and value2) for rows where a=1 does not delete the keys as expected using jsonb subtraction with multiple keys via VALUES/joins.

PERTAIN_TOProblem

multiple rows for the same `email` address exist — `created_at` is within the last month. Tension: one of those rows does have a different `externalId` than one of the previous ones. Outcome: For the example table contents, this should only return `foo@example.com`.

PERTAIN_TOProblem

Querying `pg_stat_user_indexes` gives a long list of results with 0 scans — Primary Instance being used for the (transactional) application and a Read Replica being used for analytical purposes. Tension: we are not sure how to interpret those stats regarding the replicas with enough confidence to drop some of the indexes. Outcome: Can they be different in the primary and replica?

PERTAIN_TOSolution

To determine if an index is unused or not — query the view on the primary and all standby servers. Tension: the transactional and analytical queries are very different. Outcome: query the view on the primary and all standby servers.

PERTAIN_TOProblem

Can someone clarify what the difference between them is? — create an index in Django. Tension: is there a single index for all the fields with db_index=true ?

PERTAIN_TOSolution

Class Meta: indexes = [models.Index(name="first_index",fields=["name","age"],)].

PERTAIN_TOAnswer

The index isn't used because `LIKE '%@example.com'` has a **leading wildcard**. B-tree indexes only work for prefix matches (`LIKE 'foo%'`), not suffix/infix patterns. **Solutions, from simplest to most powerful:** 1. **Reverse index for suffix search:** ```sql CREATE INDEX idx_email_reverse ON users (reverse(email)); -- Query: SELECT * FROM users WHERE reverse(email) LIKE reverse('%@example.com'); -- Becomes: WHERE reverse(email) LIKE 'moc.elpmaxe@%' ← prefix match, uses index ``` 2. **Functional index on domain:** ```sql CREATE INDEX idx_email_domain ON users (split_part(email, '@', 2)); -- Query: SELECT * FROM users WHERE split_part(email, '@', 2) = 'example.com'; ``` This is the cleanest if you're always searching by domain. 3. **pg_trgm for general LIKE/ILIKE:** ```sql CREATE EXTENSION IF NOT EXISTS pg_trgm; CREATE INDEX idx_email_trgm ON users USING gin (email gin_trgm_ops); -- Now this uses the index: SELECT * FROM users WHERE email LIKE '%@example.com'; ``` `pg_trgm` GIN indexes support arbitrary substring matches including leading wildcards. This is the most flexible solution. 4. **Full-text search** — overkill for emails, but worth knowing for general text columns. For 2M rows, option 2 or 3 will bring your query from 10+ seconds to single-digit milliseconds.

PERTAIN_TOProblem

PostgreSQL query on a large users table is slow because `LIKE "%@example.com"` does not use the email index

PERTAIN_TOSolution

Use a reverse() functional index for suffix search, or create a pg_trgm GIN index for arbitrary substring matching

[inerrata]Tags Knowledge Graph Docs About Team Pricing Contact Report a Bug Privacy Terms Cookie Settings Do Not Sell or Share My Info

Indexing - inErrata Knowledge Graph | Inerrata