indexes

14 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.

Index WHERE clause columns for filtering

Indexing columns used in WHERE clauses can improve query performance by speeding up the filtering process. For example, if filtering by sign_up_date is common, create an index on that column. Similarly, indexing status may be beneficial if the column has diverse values.

Index ORDER BY columns for sorting optimization

An index on columns in an ORDER BY clause can improve the sorting process. This is particularly beneficial when a subset of rows is being returned with a LIMIT clause.

Postgres index types: B-tree, Hash, GIN, BRIN

Postgres offers various index types including B-tree (default), Hash, GIN, and others. Select the type that best suits your data and query pattern. Using the right index type can make a significant difference. For example, using a BRIN index on a field that always increases and lives within a table that updates infrequently like created_at on an orders table routinely results in indexes that are +10x smaller than the equivalent default B-tree index.

BRIN index syntax for always-increasing columns

BRIN indexes are optimal for columns with monotonically increasing values and infrequent updates. Example: CREATE INDEX idx_orders_created_at ON customers USING brin(created_at);

Partial index syntax with WHERE clause

A partial index contains a WHERE clause to filter the values included in the index. For queries that frequently target a subset of data, a partial index can be faster and smaller than indexing the entire column. Note that a query's WHERE clause must match the index for it to be used. Example: CREATE INDEX idx_orders_status ON orders (status) WHERE status = 'shipped';

Composite index for multiple column filtering and joining

If filtering or joining on multiple columns, a composite index prevents Postgres from referring to multiple indexes when identifying the relevant rows. Example: CREATE INDEX idx_customers_sign_up_date_priority ON customers (sign_up_date, priority);

Index trade-off: indexes speed up reads but slow down writes

While indexes can speed up reads, they also slow down writes. It is important to balance these factors when making indexing decisions. Avoid the urge to index columns you operate on infrequently.

Use ANALYZE to update table statistics

Postgres maintains statistics about the contents of tables, which the query planner uses to decide when it is more efficient to use an index versus scanning the entire table. If collected statistics drift too far from reality, the query planner may make poor decisions. Periodically run ANALYZE on tables to avoid this risk. Example: ANALYZE customers;

index_advisor tool for automatic index detection

Supabase provides the index_advisor tool for automatically detecting indexes that improve performance on a given query. This is helpful when starting your optimization journey.

Create index on sign_up_date column

Example: CREATE INDEX idx_customers_sign_up_date ON customers (sign_up_date);

Create index on status column

Example: CREATE INDEX idx_orders_status ON orders (status);

Create index on customer_id foreign key column

Example: CREATE INDEX idx_orders_customer_id ON orders (customer_id);

Create index on date_of_purchase column

Example: CREATE INDEX idx_orders_date_of_purchase ON orders (date_of_purchase);

Using EXPLAIN to test hypothetical indexes

After creating a hypothetical index with HypoPG, run EXPLAIN on the target query to see if the query planner would use the index. Compare the query plan before and after creating the hypothetical index to determine if creating a real index would improve performance.

Give your agent this brain