Indexes and performance analysis

Here we will cover the indexes that are not automatically created with PK/UK constraints, but would significantly improve performance in PC Forge. The focus is on the most frequent queries, JOIN operations and critical business scenarios.
With these indexes:
-Most frequent searches are covered (including build names, component names)
-Filtering by approval status and build dates is optimized
-JOIN operations between builds and components are faster
-Rating and favorite calculations are efficient
-Component type browsing is optimized

Scenario 1: Approved Builds Browsing and Filtering

Users frequently browse approved builds, filter them by name and sort by price, date or rating. This involves filtering by is_approved, searching by name and sorting by created_at or total_price.
Optimization: A composite index on is_approved and created_at speeds up the most common query pattern. Adding total_price allows for efficient sorting without additional table lookups.

CREATE INDEX IF NOT EXISTS idx_build_approved_date
ON build(is_approved, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_build_approved_price
ON build(is_approved, total_price DESC);

CREATE INDEX IF NOT EXISTS idx_build_name_search
ON build(name);

is_approved is a low-cardinality column (TRUE/FALSE), while both created_at and total_price are high-cardinality columns often used for ORDER BY, so that's why this combination is optimal.

Scenario 2: User Dashboard - Builds and Favorites

The user dashboard displays builds created by the user and also builds they've favorited, created by other users. This requires frequent lookups by user_id in both build and favorite_build tables, often with rating aggregations.
Optimization: Indexes on user_id in both tables, plus composite indexes for the JOIN operations between builds and ratings.

CREATE INDEX IF NOT EXISTS idx_build_user_id
ON build(user_id);

CREATE INDEX IF NOT EXISTS idx_favorite_build_user_id
ON favorite_build(user_id);

CREATE INDEX IF NOT EXISTS idx_rating_build_build_id
ON rating_build(build_id);

Scenario 3: Build Components and Compatibility Checking

When users view build details or add components to builds, the application performs frequent JOIN operations between build_component and components. Compatibility checking also requires lookups by component type and specific attributes (like socket, TDP, form factor, length).
Optimization: Composite indexes on the junction table and component type filtering.

CREATE INDEX IF NOT EXISTS idx_build_component_build_id
ON build_component(build_id);

CREATE INDEX IF NOT EXISTS idx_build_component_component_id
ON build_component(component_id);

CREATE INDEX IF NOT EXISTS idx_components_type_price
ON components(type, price DESC);

CREATE INDEX IF NOT EXISTS idx_components_name_search
ON components(name);

The composite index (type, price DESC) is really useful when it comes to component browsing dialogs, because they filter by type and sort by price by default.

Scenario 4: Admin Panel - Pending Approvals

Admins frequently check for pending builds and suggestions. This requires filtering by is_approved = FALSE for builds and status = 'pending' for component suggestions.
Optimization: Indexes on status columns with date ordering for priority sorting.

CREATE INDEX IF NOT EXISTS idx_build_pending_date
ON build(is_approved, created_at DESC)
WHERE is_approved = FALSE;

CREATE INDEX IF NOT EXISTS idx_suggestions_status
ON suggestions(status);

CREATE INDEX IF NOT EXISTS idx_suggestions_user_id
ON suggestions(user_id);

The partial index on build with WHERE is_approved = FALSE is smaller and more efficient, since admins only care about unapproved builds.

Scenario 5: Reviews and User Authentication

Users leave reviews on builds, commenting their likes and dislikes, and with that the application frequently looks up usernames for authentication and display purposes.
Optimization: Indexes for review lookups and username searches.

CREATE INDEX IF NOT EXISTS idx_review_build_id
ON review(build_id);

CREATE INDEX IF NOT EXISTS idx_review_user_id
ON review(user_id);

CREATE INDEX IF NOT EXISTS idx_users_username
ON users(username);

CREATE INDEX IF NOT EXISTS idx_users_email
ON users(email);

username and email are both UNIQUE, but explicit indexes improve login query performance and case-insensitive searches if needed.

Scenario 6: Component Compatibility - Socket and Form Factor Matching

When users add components to their builds, the compatibility logic frequently queries CPU sockets, motherboard sockets and cooler compatibility.
Optimization: Indexes on compatibility-critical columns.

CREATE INDEX IF NOT EXISTS idx_cpu_socket
ON cpu(socket);

CREATE INDEX IF NOT EXISTS idx_motherboard_socket
ON motherboard(socket);

CREATE INDEX IF NOT EXISTS idx_cooler_sockets_socket
ON cooler_cpu_sockets(socket);

CREATE INDEX IF NOT EXISTS idx_motherboard_form_factor
ON motherboard(form_factor);

Testing with EXPLAIN ANALYZE

Example test for scenario 4:

EXPLAIN (ANALYZE, BUFFERS)
SELECT b.id, b.name, b.created_at, b.total_price, u.username
FROM build b
JOIN users u ON u.id = b.user_id
WHERE b.is_approved = FALSE
ORDER BY b.created_at DESC
LIMIT 50;

Performance Comparison

Without partial index:

Index Scan using idx_build_approved_date on build b

Index Cond: (is_approved = false) Buffers: shared hit=150

Execution Time: 0.351 ms

With partial index:

Index Scan using idx_build_pending_date on build b

Buffers: shared hit=68 read=2

Execution Time: 0.219 ms

Result: The partial index improved query performance by dropping the execution time from 0.351ms to 0.219ms. Partial indexes are more efficient for frequently filtered subsets of data, so that's why they are ideal for the admin queries that only target pending builds.