JSON
Indexing Hot JSON Properties for Dashboards in PostgreSQL
Expression, partial and GIN indexes on a 2.25 million row jsonb event table: a selective lookup fell from 65 ms to 5 ms, a 4 percent filter got slower.
6 min read
Tag
tuning
5 posts
PostgreSQL
Tuning PostgreSQL for SSDs With random_page_cost and More
Tune PostgreSQL for SSDs: lower random_page_cost, set effective_io_concurrency, check changed plans, use per tablespace values and keep scans that win.
7 min read
XML
Indexing XML Columns in PostgreSQL Without Wrecking Inserts
An expression index on xpath turned a 245 ms scan into 1 ms on 50,000 documents and made loading 2.8 times slower. A generated column does the same.
6 min read
PostgreSQL
Why the Planner Estimates the Wrong Number of Rows
Why the PostgreSQL planner estimates the wrong number of rows: stale statistics, skew, correlated columns, expressions and parameters, with the fix for each.
7 min read
PostgreSQL
Indexing Full Text Search With GIN in PostgreSQL Tables
Index PostgreSQL full text search with GIN: how it works, GIN versus GiST, fastupdate and the pending list, build time, size and when the planner skips it.
7 min read