On Beyond Data Types
summary
Postgres proves the claims with numbers: creating an expression index on plot area turns a sequential scan into an index scan and speeds queries about 15×; a GIST containment index improves spatial containment lookups about 4×; building a KNN GIST for k‑nearest on a million geocodes drops a location query from ~300 ms to ~0.2 ms; jsonb containment lookups hit the GIN index and run in ~0.043 ms versus seconds otherwise. Ranges support exclusion constraints for scheduling (with practical exceptions), SP-GiST and GIN are used in production, and uuid_ossp can be thorny on macOS.
AI-written summary · about
chapters (56)
- 0:00Start
- 0:36A Brief Note on NYCPUG
- 2:17Data Types
- 3:35PostgreSQL Primitives Oversimplified Summary
- 6:01Summary of PostgreSQL Date/Time Types
- 6:40PostgreSQL is a ORDBMS
- 7:51Let's Start Easy: Geometry
- 8:16PostgreSQL Geometric Types
- 9:30Geometric Operators
- 10:40Geometric Functions
- 11:09Geometric Performance
- 12:24Expression Indexes
- 14:09GIST Indexes
- 15:08Solution #2+: KNN-Gist
- 17:55PostGIS
- 18:47Let's Take a Break With UUIDS
- 19:14UUID Functions
- 20:47Networks can do Math
- 21:20Postgres Can Help Manage Your Routing Tables
- 22:36Array Facts
- 24:31Arrays Are Malleable
- 24:54Array Operations
- 25:38Integer Arrays Use GIN
- 27:43Array Functions
- 28:30array_agg
- 30:18Why Range Overlaps Are Difficult
- 30:43Before Postgres 9.2
- 31:07Postgres 9.2+
- 31:25Range Type Size
- 31:57Range Bounds
- 32:11Infinite Ranges
- 32:39Constructing Ranges
- 33:38Finding Overlapping Ranges
- 34:14Ranges + GIST
- 34:28Large Search Range?
- 36:42Scheduling
- 37:50Extending Ranges
- 38:36Making hstore objects
- 38:47Accessing hstore
- 39:27hstore Performance
- 40:10JSON and PostgreSQL
- 40:43What Happened?
- 41:16Foreshadowing
- 41:36PostgreSQL 9.2: JSON
- 42:08PostgreSQL 9.3: JSON Ups its Game
- 42:26Operator Gotchas
- 42:58Performance?
- 43:36Performance Revisited
- 44:14The Relation vs JSON
- 44:33JSON Aggregates
- 45:21Nested hstore
- 45:43How JSONB Came to Be
- 46:24JSONB Gives Us More Operators
- 47:28JSONB Gives Us Speed
- 47:56JSONB Gives Us WTF: A Note On Operator Indexability
- 48:43In Summary
related talks
49mFlexible Indexing with Postgres
53mAdvanced Indexing Techniques in PostgreSQL
29mPostgres Index types and where to find them
45mA Deep Dive into PostgreSQL Indexing
34mGiST news and prospects
42mPostgreSQL Tips & Tricks For App Devs
46mA Deep Dive Into PostgreSQL Indexing
37mRules of Indexing in PostgreSQL