Unconference - Stream 1 - 15:45 - 16:45 - PGCon 2022

Please provide the slide or text from the talk, and I'll do my best to identify the speaker name. (waiting for the text...)

Explore the power of the key space in optimizing index scans, from B-trees and GIN indexes to Brin indexes, and learn how to simplify complex queries using these techniques.

Key takeaways
  • The key space is the main intuition generating thing, not physical blocks.
  • Hash indexes and regular indexes use the same index tuple representation.
  • Index scans can be optimized by considering the key space and using a sort node on top of the index scan.
  • The B-tree code can be used to implement MDAM type things like skip scan.
  • The GIN index code has similar machinery to the B-tree code.
  • The Brin index can be used to provide accurate statistics on what is not in blocks or what is in blocks but not necessarily because of the index.
  • The key space can be used to preserve page-at-a-time processing, which is key to not requiring duplicate elimination.
  • The B-tree scan can be optimized by using a cap on the number of concurrent scans in the tree.
  • The SQL query can be optimized by using a top-level sort and then merging the results.
  • The Gist index can be used to provide a smaller index that is orders of magnitude smaller than the original index.
  • The Brin index can be used to provide a range index on top of the BrinMinMaxes.
  • The B-tree code can be used to implement a range index on top of the BrinMinMaxes.
  • The key space can be used to simplify the index scan by considering only two block ranges.
  • The GIN index code has a lot of machinery that can be used to implement the Brin index.
  • The Brin index can be used to provide a range index on top of the BrinMinMaxes.
  • The B-tree code can be used to implement a range index on top of the BrinMinMaxes.
  • The key space can be used to simplify the index scan by considering only two block ranges.
  • The GIN index code has a lot of machinery that can be used to implement the Brin index.

More talks

r2cloud - Decode satellite signals on Raspberry PI Marwan Fathy Aurélie Violette - Developing Weweb: VueJs Performance optimization on a large webapp Aurélie Violette Archibald & Seibert - PIXIE: Blending Just-in-time and Ahead-of-time compilation | SciPy 2024 Archibald & Seibert Unlocking Mixture of Experts : From 1 Know-it-all to group of Jedi Masters — Pranjal Biyani Pranjal Biyani Stephen Macke - Python as a Hackable Language for Interactive Data Science | PyData Global 2023 Stephen Macke Itamar Turner-Trauring - Optimize first, parallelize second: a better path to faster data processing Itamar Turner-Trauring DevOps for Java Developers (or maybe against them?!) by Baruch Sadogursky Baruch Sadogursky Shaurya Agarwal - All Them Data Engines: Data Munging with Python circa 2023 | PyData Global 2023 Shaurya Agarwal