Changes between Version 2 and Version 3 of Indexes

Timestamp:

02/04/26 20:08:29 (4 days ago)

Author:

233194

Comment:

--

Indexes

@@ -71 +71 @@
 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. //
+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 == 
@@ -109 +109 @@
 }}}
 
+== 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:
 {{{
@@ -121 +157 @@
 }}}
 
+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 == 
 
-=== Without partial index: ===
+=== Scenario 4: Admin Panel - Pending Approvals ===
+Without partial index:
 Index Scan using idx_build_approved_date on build b
   Index Cond: (is_approved = false)
@@ -129 +184 @@
 Execution Time: 0.351 ms \\
 
-=== With partial index: ===
+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.
-
-
-
-
-
+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. \\
+
+
+
+