Breakout Session
summary
Concrete methods and numbers: uses PostgreSQL extensions like pg_trgm and fuzzy_string_match, builds GIN/GiST and expression indexes, and tests on a messages table of ~1M emails (~85 GB). Exact-match with btree returns in microseconds; anchored LIKE with text_pattern_ops is sub-millisecond; unanchored searches go from 2s sequential to ~30 ms with trigram GIN; regex sequential scans can be ~27s but trigram index drops that to ~200 ms; full‑text ts_vector queries return ~22 ms and ranking/highlighting requires careful planning.
AI-written summary · about
chapters (30)
- 0:00Where's Waldo?
- 0:49Caveats
- 2:11Text Search Methods
- 3:07Note on Extensions
- 4:08Sample Data
- 5:13LIKE Syntax
- 6:18Negating LIKE
- 6:46Wildcards
- 7:30Alternate Index Op Classes
- 8:56ESCAPE Example
- 9:31SIMILAR TO Syntax
- 10:46SIMILAR TO Examples
- 11:32Regular Expression Syntax
- 14:01Regular Expression Example
- 19:43Double Metaphone
- 21:15Trigram Matching Example
- 22:40Overview
- 23:29Preprocessing
- 24:47Weighting
- 26:10Creating tsvector
- 26:49Writing tsquery
- 31:18Match Operator
- 34:29Relevance Ranking
- 35:27Highlighting
- 36:32Pattern Matching: Example Use Cases
- 40:56Reverse Anchored Case-Insensitive
- 42:02Unanchored Case-Insensitive
- 44:34Fuzzy
- 45:29Complex Requirements
- 46:00Questions?
related talks
43mFull-Text-Search Explained from A to Z with French Food
59mFull-text search - seek and ye shall find
46mA Deep Dive Into PostgreSQL Indexing
22mFrom Postgres full text search to Retrieval Augmented Generative search
53mOn Beyond Data Types
45mSQL Indexing Beyond Simple Columns: Expressions, Text Fragments, Attributes J...
27mMulti-table Full Text Search with Postgres
53mAdvanced Indexing Techniques in PostgreSQL