Index hash, GIN et GiST

You've likely encountered B-Tree indexes, which are excellent for exact matches and range scans on single columns. But what about more complex data types or unique query patterns?

PostgreSQL offers specialized index types to supercharge these specific scenarios, allowing for efficient querying where B-Trees fall short.

A Hash Index stores a hash value for each indexed column. It's optimized for very fast equality queries (using the = operator).

• Think of it like a dictionary lookup: incredibly fast if you know the exact key.
• They can be faster than B-Trees for simple equality checks on very large tables, especially with many duplicates.

While fast for equality, Hash indexes have key limitations:

• No Range Scans: You can't use them for >, <, or BETWEEN queries.
• No Sorting: They don't store data in any particular order, so they can't help with ORDER BY clauses.
• Crash Safety: Historically, they weren't crash-safe. While improved in newer PostgreSQL versions, B-Trees are still generally preferred for critical data due to their robustness.

GIN stands for General Inverted Index. It's designed for data types that contain multiple individual values, like arrays, JSONB documents, or full-text search lexemes.

Think of it as indexing the contents of a field, not just the field itself. This allows for very fast lookups of elements within these complex structures, using operators like @> (contains).

Let's see how a GIN index helps query an array column. We'll create a table, insert some data, then add a GIN index and query it.

Notice the @> operator for checking if an array contains specific elements.

GiST stands for Generalized Search Tree. It's a highly flexible index structure that can handle many different types of queries, especially those involving non-standard data types or complex operators.

Key use cases include:

• Spatial data: e.g., finding points within a polygon or objects that overlap.
• Range types: e.g., finding overlapping time periods or numeric ranges.
• Full-text search: (though GIN is often faster for this).

Here's an example using GiST with PostgreSQL's built-in box type to find objects within a certain rectangular area. We use the && operator for "overlaps".

Both GIN and GiST can be used for full-text search (FTS) in PostgreSQL, but they have different strengths:

• GIN: Generally faster for lookups when many items contain the search term, and offers faster initial build times.
• GiST: Can be faster for updates if the data changes frequently, as GIN can be slower to update. GiST also supports more operators for FTS.

For most read-heavy FTS scenarios, GIN is the go-to choice.

Here's a quick guide to help you choose:

• B-Tree: Default, general-purpose. Good for equality, range, sorting.
• Hash: Only for exact equality (=), no range, no sorting. Less common due to limitations.
• GIN: For "inverted" data like arrays, JSONB, full-text search. Efficiently finds elements within complex types.
• GiST: Highly flexible, for spatial data (points, boxes), range types, sometimes full-text search. Good for complex operators.

Great job! You've explored PostgreSQL's advanced index types:

• Hash Indexes for fast equality checks (with limitations).
• GIN Indexes for efficiently querying elements within complex data like arrays and JSONB.
• GiST Indexes for flexible indexing of spatial data, range types, and complex operators.

These specialized indexes empower you to optimize queries that B-Trees can't handle efficiently. In the next lesson, we'll dive into partial and expression indexes!