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);

Scenario 7: Top Performing Components Report

Users analyze which components are most popular in recent builds with high ratings. This report filters builds by date, joins multiple tables and aggregates ratings with a quality threshold.
Optimization: Without indexes, the query performs sequential scans on the build table to filter by created_at, then joins to rating_build.
A date index enables efficient range filtering, reducing the scan from 75000 builds to only those in the relevant time window.

CREATE INDEX IF NOT EXISTS idx_build_created_at 
ON build(created_at);

CREATE INDEX IF NOT EXISTS idx_rating_build_build_value 
ON rating_build(build_id, value);

The date index on build(created_at) eliminates full table scans for time-windowed queries

Scenario 8: User Reputation Leaderboard Report

This report calculates user reputation scores by aggregating approved builds, favorites received, and average ratings per user.
Optimization: The partial index on approved builds significantly reduces the working dataset. Combined with indexes on the JOIN keys, this reduces buffer reads and eliminates unnecessary data processing.

CREATE INDEX IF NOT EXISTS idx_build_approved_user 
ON build(is_approved, user_id) WHERE is_approved = TRUE;

CREATE INDEX IF NOT EXISTS idx_favorite_build_build_id 
ON favorite_build(build_id);

The composite partial index (is_approved, user_id) WHERE is_approved = TRUE is highly selective and perfect for analytics that only considers approved content.

Scenario 9: Budget Tier Popularity Report

This report segments builds into price tiers (Budget, Mid-Range, High-End, Enthusiast) and calculates engagement metrics within the last 6 months. It requires filtering by both approval status and creation date, then aggregates favorites and ratings for each tier.
Optimization: A composite index covering all three filter columns (is_approved, created_at, total_price) which enables index-only scans for the initial filter. The partial index with WHERE clause reduces index size by excluding unapproved builds entirely.

CREATE INDEX IF NOT EXISTS idx_build_approved_created_price 
ON build(is_approved, created_at, total_price) WHERE is_approved = TRUE;

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;

Example test for scenario 7:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM get_report_top_components();

Example test for scenario 8:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM get_report_user_reputation_leaderboard();

Example test for scenario 9:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM get_report_budget_tier_popularity();

Performance Comparison

Scenario 4: Admin Panel - Pending Approvals

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.

Scenario 7: Top Performing Components Report

Without indexes:
Buffers: shared hit=7684
Planning Time: 0.021 ms
Execution Time: 506.953 ms

With indexes:
Buffers: shared hit=6379 read=351
Planning Time: 0.022 ms
Execution Time: 468.190 ms

Result: The date index improved query performance, while not much, still a modest improvement reducing it from 506.953ms to 468.190ms.

Scenario 8: User Reputation Leaderboard Report

Without indexes:
Buffers: shared hit=142884 dirtied=1, temp read=495 written=849
Planning Time: 0.029 ms
Execution Time: 553.368 ms

With indexes:
Buffers: shared hit=142892 read=1, temp read=231 written=584
Planning Time: 0.022 ms
Execution Time: 345.192 ms

Result: Here the improvement is much more noticeable than on Scenario 7, reducing the time from 553.368ms to 345.192ms.

Scenario 9: Budget Tier Popularity Report

Without indexes:
Buffers: shared hit=1366 read=30
Planning Time: 0.020 ms
Execution Time: 131.234 ms

With indexes:
Buffers: shared hit=1396
Planning Time: 0.029 ms
Execution Time: 125.615 ms

Result: Modest improvement, but the index still helped out with unnecessary reads, reducing time from 131.234ms to 125.615ms.