Changes between Initial Version and Version 1 of Indexes

Timestamp:

01/29/26 22:52:58 (10 days ago)

Author:

233194

Comment:

--

Indexes

@@ +1 @@
+== 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 this admin queries that only target pending builds.
+
+
+
+
+