Indexing and Optimization

PostgreSQL does not immediately reclaim space from page splits. Empty space on B-tree pages is Reused by future inserts, but the pages themselves are not returned to the OS until VACUUM FULL or pg_repack. This is why B-tree indexes can become bloated after heavy UPDATE/DELETE workloads.

Hash indexes use a hash function to map keys directly to bucket locations, providing $O(1)$ average Lookup for exact equality checks.

• Workload is exclusively point lookups (WHERE key = value)
• No range queries needed (hash indexes cannot do WHERE key > value)
• No sorting needed (hash indexes cannot do ORDER BY key)
• No prefix matching needed

• No range scans
• No ORDER BY
• No partial matches or LIKE
• No partial indexes
• In PostgreSQL prior to 10, hash indexes were not WAL-logged (not crash-safe)
• Generally not recommended in PostgreSQL; use B-tree instead unless you have a specific reason

CREATE INDEX idx_users_email_hash ON users USING hash (email);

A covering index (also called an index-only scan) contains all the columns needed by a query, so the Database never needs to access the table itself (the “heap” in PostgreSQL terminology).

-- Query:
SELECT first_name, last_name, email FROM employees WHERE department_id = 5;

-- Covering index (includes the selected columns):
CREATE INDEX idx_emp_dept_covering ON employees (department_id, first_name, last_name, email);

The benefits are significant:

• No heap access: the query is satisfied entirely from the index
• Less I/O: index pages are much smaller than table pages
• Better cache utilisation: more index entries fit in the buffer pool

In PostgreSQL, an index-only scan requires that the visibility map indicates all pages are All-visible. If any referenced page has dirty visibility information, the database falls back to a Regular index scan that checks the heap. This is why VACUUM matters for index-only scan Performance.

A composite (multi-column) index is an index on two or more columns. The order of columns in a Composite index matters critically.

A composite index (A, B, C) can satisfy queries that filter on:

• A
• A, B
• A, B, C

But not queries that filter on:

• B alone
• C alone
• B, C

CREATE INDEX idx_emp_dept_hire ON employees (department_id, hire_date, salary);

-- Uses the index:
SELECT * FROM employees WHERE department_id = 3;
SELECT * FROM employees WHERE department_id = 3 AND hire_date > '2023-01-01';
SELECT * FROM employees WHERE department_id = 3 AND hire_date > '2023-01-01' AND salary > 100000;

-- Does NOT use the index effectively (falls back to seq scan or partial index scan):
SELECT * FROM employees WHERE hire_date > '2023-01-01';
SELECT * FROM employees WHERE salary > 100000;

The general rule for ordering columns in a composite index:

1. Equality columns first — columns used with = go before range columns
2. Range columns after equality — columns used with >``<``BETWEEN``LIKE come after
3. Most selective columns first among equality columns (reduces the search space faster)

-- Good: equality (department_id) before range (hire_date)
CREATE INDEX idx_emp_dept_hire ON employees (department_id, hire_date);

-- Bad: range column first makes the equality column unusable
CREATE INDEX idx_emp_hire_dept ON employees (hire_date, department_id);
-- Query: WHERE department_id = 3 AND hire_date > '2023-01-01'
-- The department_id filter cannot use this index effectively

A partial index is an index built on a subset of rows defined by a WHERE clause. This reduces index Size and maintenance overhead.

-- Index only active users (99% of queries filter on this anyway)
CREATE INDEX idx_users_active_email ON users (email) WHERE is_active = TRUE;

-- Index only unshipped orders
CREATE INDEX idx_orders_unshipped ON orders (customer_id, created_at)
    WHERE status = 'pending';

-- Index only high-value transactions for fraud detection
CREATE INDEX idx_transactions_large ON transactions (account_id, created_at)
    WHERE amount >= 10000;

REFRESH MATERIALIZED VIEW CONCURRENTLY requires a UNIQUE index on the materialized view. It Refreshes by scanning the new data and updating existing rows, which is slower than a full refresh But does not block concurrent reads.

The query planner often converts subqueries into joins automatically. However, some subquery Patterns prevent this optimisation:

-- This may not be flattened (correlated subquery with LIMIT):
SELECT * FROM customers
WHERE customer_id IN (
    SELECT customer_id FROM orders
    WHERE total > 1000
    LIMIT 100
);

-- Rewrite as a JOIN:
SELECT DISTINCT c.*
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id AND o.total > 1000
LIMIT 100;

For bulk data loading, individual INSERT statements are extremely slow due to per-statement Overhead (parsing, planning, WAL flush, index maintenance). Use batch inserts or COPY:

-- Multi-row INSERT (batch):
INSERT INTO sensor_readings (sensor_id, timestamp, value)
VALUES
    (1, '2024-03-15T10:00:00Z', 23.5),
    (1, '2024-03-15T10:01:00Z', 23.7),
    (1, '2024-03-15T10:02:00Z', 23.6);

-- COPY (fastest for bulk loads):
COPY sensor_readings (sensor_id, timestamp, value)
FROM '/data/sensor_data.csv'
WITH (FORMAT csv, HEADER true);

# For large imports, tune these settings during the load:
psql -c "SET maintenance_work_mem = '2GB';"
psql -c "SET max_wal_size = '4GB';"
psql -c "ALTER TABLE sensor_readings SET (autovacuum_enabled = false);"
psql -c "COPY sensor_readings FROM '/data/sensor_data.csv' WITH (FORMAT csv);"
psql -c "ALTER TABLE sensor_readings SET (autovacuum_enabled = true);"
psql -c "ANALYZE sensor_readings;"

Prepared statements parse and plan a query once, then execute it multiple times with different Parameters. This avoids repeated parsing overhead and allows the planner to cache the execution Plan.

-- Prepare:
PREPARE get_orders_by_customer AS
SELECT * FROM orders WHERE customer_id = $1 AND status = $2 ORDER BY created_at DESC LIMIT 50;

-- Execute (multiple times with different parameters):
EXECUTE get_orders_by_customer(42, 'pending');
EXECUTE get_orders_by_customer(42, 'completed');

-- Deallocate when done:
DEALLOCATE get_orders_by_customer;