PostgreSQL: GIN and GiST Indices

PostgreSQL 8.2 is here, and it's making other databases look like toys. While MySQL is still struggling with basic subqueries, Postgres is giving us Generalized Search Trees (GiST) and Generalized Inverted Indices (GIN).

GiST: The "Anything" Index

GiST is a template that allows you to build indices for complex data types like points, polygons, or even custom IP ranges.

-- Using GiST for geometric search
CREATE TABLE locations (
    id serial PRIMARY KEY,
    geom point
);

CREATE INDEX locations_geom_idx ON locations USING gist (geom);

-- Find points within a circle
SELECT * FROM locations WHERE geom <@ circle '<(0,0), 10>';

GIN: The Power of Inversion

GIN is designed for data where a single column contains multiple values, like arrays or full-text search documents. Unlike a B-Tree, which points to a row, GIN points to elements within the row.

-- Full-text search with GIN
CREATE TABLE articles (
    id serial PRIMARY KEY,
    body text,
    body_vector tsvector
);

-- Update the vector for search
UPDATE articles SET body_vector = to_tsvector('english', body);

-- Index the vector
CREATE INDEX articles_search_idx ON articles USING gin (body_vector);

-- Lightning fast search
SELECT * FROM articles 
WHERE body_vector @@ to_tsquery('postgres & indexing');

If you're doing full-text search with LIKE '%term%', stop. You're killing your server. GIN indices make your searches O(log N) instead of O(N).