liveBook · Manning

B-tree

As explained in PostgreSQL Mistakes and How to Avoid Them

A B-tree index is an ordered database index structure that efficiently supports equality, ordering, and compatible prefix-pattern lookups.

A B-tree is PostgreSQL’s default ordered index. It accelerates equality and ordering comparisons, but choosing it for arbitrary substring search is a common mistake. For JSONB titles, index the exact expression used by the query. section 4.4

Prefix matching works; containment does not

CREATE INDEX ON test.arxiv (lower(data->>'title') text_pattern_ops);\n\nThe matching prefix uses an Index Scan and finishes in 0.134 ms. Changing the predicate to LIKE '%modeling of hydrogen%' forces a Sequential Scan: 2,417,688 rows are filtered and execution takes 8,624 ms.

How PostgreSQL Mistakes and How to Avoid Them covers it

How deep it goes
Demonstrates expression indexes and EXPLAIN plans, but defers the B-tree algorithm’s internal mechanics.
Examples
Uses ArXiv JSONB metadata, title-prefix searches, substring searches, oversized abstracts, and DOI equality lookups.
What it assumes
Assumes SQL, PostgreSQL indexes, JSONB operators, and query plans; teaches selection through concrete performance contrasts.
What you can do after
Diagnose/debug with it by comparing predicates against index expressions and reading EXPLAIN ANALYZE output.
How it connects
Connects B-tree with expression indexes, LIKE, JSONB, GIN, and full-text indexing.
Cover of PostgreSQL Mistakes and How to Avoid Them
liveBookmanning.com
Read PostgreSQL Mistakes and How to Avoid Them on liveBook Get the book at manning.com

Also explained in

Compare how 4 books explain B-tree →

Related concepts

This explainer was generated by Manning's knowledge graph from the text of PostgreSQL Mistakes and How to Avoid Them. The section links point to the parts of the book it draws on.