PostgreSQL Indexing Techniques

Indexes in PostgreSQL are database objects that improve the speed of data retrieval operations. They are essential for performance optimization, especially on large tables, and can drastically reduce query execution time.

1. Why Use Indexes?

• Faster search and retrieval of rows.
• Improve performance of WHERE, JOIN, ORDER BY, and GROUP BY queries.
• Enable unique constraints for data integrity.

2. Basic Index

-- Create an index on a single column
            CREATE INDEX idx_employee_name ON employees(name);

3. Unique Index

-- Create a unique index to enforce uniqueness
            CREATE UNIQUE INDEX idx_employee_email ON employees(email);

4. Composite Index

Composite indexes are created on multiple columns to optimize queries that filter using multiple fields.

-- Composite index on department_id and salary
            CREATE INDEX idx_dept_salary ON employees(department_id, salary);

5. Partial Index

Partial indexes are created with a WHERE clause to index only a subset of rows.

-- Index only active employees
            CREATE INDEX idx_active_employees ON employees(id)
            WHERE status = 'active';

6. Expression Index

Expression indexes are based on the result of an expression rather than a column value.

-- Index on lower-case name for case-insensitive search
            CREATE INDEX idx_lower_name ON employees(LOWER(name));

7. GIN and GiST Indexes

• GIN (Generalized Inverted Index): Useful for array, JSONB, and full-text search.
• GiST (Generalized Search Tree): Useful for geometric data and full-text search.

-- GIN index on JSONB column
            CREATE INDEX idx_employee_data ON employees USING GIN (data);

            -- GiST index on point column
            CREATE TABLE locations (
                id SERIAL PRIMARY KEY,
                name VARCHAR(100),
                coord POINT
            );

            CREATE INDEX idx_locations_coord ON locations USING GiST (coord);

8. Indexing Best Practices

• Index columns that are frequently used in WHERE, JOIN, ORDER BY, and GROUP BY.
• Avoid excessive indexes on a table as it can slow down INSERT, UPDATE, and DELETE.
• Use partial or expression indexes to optimize specific query patterns.
• Monitor query performance using EXPLAIN and EXPLAIN ANALYZE.
• Regularly maintain indexes with REINDEX if necessary.

9. Dropping Indexes

-- Drop an index
            DROP INDEX IF EXISTS idx_employee_name;

Conclusion

Indexing is a critical aspect of PostgreSQL performance tuning. By using the appropriate type of index—single-column, composite, partial, expression, GIN, or GiST—you can significantly optimize query speed and efficiency. In the next tutorial, we will explore PostgreSQL Full-Text Search, which allows fast and flexible search capabilities within text data.