[Performance] ProjectsController#index - Slow response time (2181691.29 s)

[IMhide]

---

repository: IMhide/Blast

Performance Issue: ProjectsController#index

Repository: IMhide/Blast
Incident Number: #4
AppSignal ID: 64df5b97d2a5e475308da322
App ID: 64cb678083eb67f665b627b0

Status: CLOSED
Severity: UNTRIAGED

Description

No description provided

---

Critical Information

Metric	Value
Average Duration	2181691.29 s
Affected Actions	ProjectsController#index
State	CLOSED
Severity	UNTRIAGED

Performance Metrics

Based on 10 sample(s):

Statistic	Duration
Minimum	607.24 ms
Maximum	833.86 ms
Average	702.62 ms
Median	687.22 ms

Sample Analysis

Analyzed 10 sample(s) from recent occurrences.

Sample 1

• Time: 2025-12-10 11:08:11 UTC
• Duration: 743.76 ms
• Action: ProjectsController#index
• Queue Duration: 0.00 μs

Time Breakdown

Component	Duration	Percentage
active_record	437.94 ms	59.5%
action_view	283.81 ms	38.5%
action_controller	10.09 ms	1.4%
active_support	4.26 ms	0.6%
redis	242.19 μs	0.0%

Memory Allocation

Component	Allocation
action_view	79.42 KB
action_controller	3.71 KB
active_record	2.74 KB
active_support	74.00 B
redis	22.00 B

---

Sample 2

• Time: 2025-12-10 11:07:37 UTC
• Duration: 722.85 ms
• Action: ProjectsController#index
• Queue Duration: 0.00 μs

Time Breakdown

Component	Duration	Percentage
active_record	456.76 ms	63.7%
action_view	249.44 ms	34.8%
action_controller	7.71 ms	1.1%
active_support	2.30 ms	0.3%
redis	306.22 μs	0.0%

Memory Allocation

Component	Allocation
action_view	79.65 KB
active_record	2.73 KB
action_controller	2.27 KB
active_support	75.00 B
redis	22.00 B

---

Sample 3

• Time: 2025-12-10 11:06:59 UTC
• Duration: 833.86 ms
• Action: ProjectsController#index
• Queue Duration: 0.00 μs

Time Breakdown

Component	Duration	Percentage
active_record	634.83 ms	77.0%
action_view	178.25 ms	21.6%
action_controller	7.46 ms	0.9%
active_support	3.91 ms	0.5%
redis	213.03 μs	0.0%

Memory Allocation

Component	Allocation
action_view	79.41 KB
active_record	2.71 KB
action_controller	2.55 KB
active_support	73.00 B
redis	22.00 B

---

Sample 4

• Time: 2025-12-10 11:05:34 UTC
• Duration: 633.96 ms
• Action: ProjectsController#index
• Queue Duration: 0.00 μs

Time Breakdown

Component	Duration	Percentage
active_record	424.09 ms	67.9%
action_view	186.60 ms	29.9%
action_controller	10.21 ms	1.6%
active_support	3.00 ms	0.5%
redis	266.43 μs	0.0%

Memory Allocation

Component	Allocation
action_view	79.50 KB
active_record	2.71 KB
action_controller	2.37 KB
active_support	73.00 B
redis	22.00 B

---

Sample 5

• Time: 2025-12-10 11:04:05 UTC
• Duration: 607.24 ms
• Action: ProjectsController#index
• Queue Duration: 0.00 μs

Time Breakdown

Component	Duration	Percentage
active_record	418.69 ms	69.7%
action_view	171.75 ms	28.6%
action_controller	7.89 ms	1.3%
active_support	2.58 ms	0.4%
redis	226.46 μs	0.0%

Memory Allocation

Component	Allocation
action_view	79.42 KB
active_record	2.71 KB
action_controller	2.56 KB
active_support	75.00 B
redis	22.00 B

---

Sample 6

• Time: 2025-12-10 11:04:44 UTC
• Duration: 646.86 ms
• Action: ProjectsController#index
• Queue Duration: 0.00 μs

Time Breakdown

Component	Duration	Percentage
active_record	446.13 ms	69.3%
action_view	174.53 ms	27.1%
active_support	11.60 ms	1.8%
action_controller	11.06 ms	1.7%
redis	558.85 μs	0.1%

Memory Allocation

Component	Allocation
action_view	80.36 KB
action_controller	3.70 KB
active_record	2.88 KB
active_support	2.43 KB
redis	79.00 B

---

Sample 7

• Time: 2025-12-10 11:03:44 UTC
• Duration: 651.58 ms
• Action: ProjectsController#index
• Queue Duration: 0.00 μs

Time Breakdown

Component	Duration	Percentage
active_record	437.05 ms	68.1%
action_view	185.38 ms	28.9%
action_controller	15.40 ms	2.4%
active_support	3.26 ms	0.5%
redis	241.73 μs	0.0%

Memory Allocation

Component	Allocation
action_view	80.06 KB
action_controller	4.64 KB
active_record	2.86 KB
active_support	74.00 B
redis	22.00 B

---

Sample 8

• Time: 2025-12-10 11:02:40 UTC
• Duration: 626.41 ms
• Action: ProjectsController#index
• Queue Duration: 0.00 μs

Time Breakdown

Component	Duration	Percentage
active_record	445.17 ms	71.8%
action_view	165.51 ms	26.7%
action_controller	6.79 ms	1.1%
active_support	2.42 ms	0.4%
redis	243.38 μs	0.0%

Memory Allocation

Component	Allocation
action_view	79.20 KB
active_record	2.70 KB
action_controller	2.27 KB
active_support	74.00 B
redis	22.00 B

---

Sample 9

• Time: 2025-12-10 11:01:21 UTC
• Duration: 733.79 ms
• Action: ProjectsController#index
• Queue Duration: 0.00 μs

Time Breakdown

Component	Duration	Percentage
active_record	488.78 ms	67.5%
action_view	221.26 ms	30.6%
action_controller	10.47 ms	1.4%
active_support	2.98 ms	0.4%
redis	231.24 μs	0.0%

Memory Allocation

Component	Allocation
action_view	79.64 KB
active_record	2.71 KB
action_controller	2.55 KB
active_support	74.00 B
redis	22.00 B

---

Sample 10

• Time: 2025-12-10 11:00:59 UTC
• Duration: 825.89 ms
• Action: ProjectsController#index
• Queue Duration: 0.00 μs

Time Breakdown

Component	Duration	Percentage
active_record	506.35 ms	61.6%
action_view	242.64 ms	29.5%
active_support	59.78 ms	7.3%
action_controller	13.30 ms	1.6%
redis	253.39 μs	0.0%

Memory Allocation

Component	Allocation
action_view	79.41 KB
action_controller	3.71 KB
active_record	2.74 KB
active_support	74.00 B
redis	22.00 B

---

N+1 Query Detection

✅ No N+1 query patterns detected in samples.

Resource Allocation Analysis

Average memory allocation by category:

Category	Average Allocation	Samples
action_view	79.61 KB	10
action_controller	3.03 KB	10
active_record	2.75 KB	10
active_support	315.10 B	10
redis	27.70 B	10

[github-actions]

🔍 Performance Analysis Results

Analysis completed for repository IMhide/Blast

---

Performance Analysis Report: ProjectsController#index

Executive Summary

Analysis of GitHub issue #12 reveals that the ProjectsController#index endpoint is experiencing severe performance issues with an average response time of 702.62ms, significantly higher than acceptable standards. The investigation identified multiple database and view rendering bottlenecks that need immediate attention.

Performance Issue Summary

Metric	Value
Average Duration	702.62 ms
Minimum Duration	607.24 ms
Maximum Duration	833.86 ms
Median Duration	687.22 ms
Sample Size	10 requests

Root Cause Analysis

Primary Bottlenecks Identified

1. Database Operations (59.5%-77.0% of request time)

   • Impact: 418.69ms - 634.83ms per request
   • Root Cause: Multiple inefficient queries and missing database indexes

2. View Rendering (21.6%-38.5% of request time)

   • Impact: 171.75ms - 283.81ms per request
   • Root Cause: Complex template rendering with nested queries

Critical Issues Found

Issue #1: Missing Database Indexes on Date Range Queries

Location: target-repo/app/query_sets/projects/index_query_set.rb:45,57

Problem: The queries filtering by intentions_ends_at time ranges lack proper indexes:

# Current problematic queries (lines 45, 57)
.where(intentions_ends_at: Time.current..)  # Line 45
.where(intentions_ends_at: ..Time.current)  # Line 57

Current Impact: Full table scans on date range filters causing 200-400ms delays

Recommended Solution:
Add composite indexes for the date range queries:

-- Add to migration file
add_index :projects, [:status, :intentions_ends_at], name: 'index_projects_on_status_and_intentions_ends_at'
add_index :projects, [:visibility, :intentions_ends_at], name: 'index_projects_on_visibility_and_intentions_ends_at'
add_index :projects, [:position, :intentions_ends_at], name: 'index_projects_on_position_and_intentions_ends_at'

Expected Improvement: Reduce database query time by ~60-70% (~300ms faster)
Effort: Low

Issue #2: Inefficient Subquery in User Visibility Filter

Location: target-repo/app/query_sets/projects/index_query_set.rb:69-78

Problem: The visible_projects_for_user method creates expensive nested subqueries:

# Current inefficient implementation (lines 69-78)
def visible_projects_for_user
  visible_for_investors_subquery =
    user
    .projects_with_intention
    .where(visibility: :visible_for_investors)

  Project
    .where(visibility: :visible)
    .or(Project.where(id: visible_for_investors_subquery))
end

Current Impact: Complex subquery execution taking 100-200ms per request

Recommended Solution:
Optimize the visibility filtering with better query structure:

# RECOMMENDED FIX
def visible_projects_for_user
  return Project.none if user.blank?

  user_project_ids = user.not_canceled_subscription_intentions
                        .joins(:project)
                        .where(projects: {visibility: :visible_for_investors})
                        .select(:project_id)
                        .distinct
                        .pluck(:project_id)

  Project.where(visibility: :visible)
         .or(Project.where(id: user_project_ids))
end

Expected Improvement: Reduce subquery complexity by ~40-50% (~80-100ms faster)
Effort: Medium

Issue #3: Missing Eager Loading for Text Translations

Location: target-repo/app/query_sets/projects/index_query_set.rb:44,55

Problem: Text translations are included but may trigger additional queries:

# Current implementation (lines 44, 55)
.includes(:text_translations)

Current Impact: Potential N+1 queries for translations during rendering

Recommended Solution:
Enhance eager loading and add preloading for view rendering:

# RECOMMENDED FIX
def fetch_published_projects
  projects = Project
    .includes(:text_translations, :project_owner, tags: [])
    .with_tags
    .published
    .where(intentions_ends_at: Time.current..)
    .order(position: :asc)

  filter_by_visibility(projects)
end

def fetch_financed_projects
  projects = Project
    .includes(:text_translations, :project_owner, tags: [])
    .with_tags
    .published_or_succeeded
    .where(intentions_ends_at: ..Time.current)
    .order(position: :asc)

  filter_by_visibility(projects)
end

Expected Improvement: Eliminate potential N+1 queries (~50-100ms faster)
Effort: Low

Issue #4: Complex Tags Scope with Raw SQL

Location: target-repo/app/models/project.rb:450-467

Problem: The with_tags scope uses complex raw SQL subquery:

# Current implementation (lines 450-467)
scope :with_tags, -> do
  select <<~SQL.squish
    projects.*,
    COALESCE(
      (
        SELECT array_agg(tags.tag_name) FROM (
          SELECT projects_tags.name AS tag_name
          FROM projects_tags
          INNER JOIN projects_projects_tags
            ON projects_tags.id = projects_projects_tags.projects_tag_id
          WHERE projects_projects_tags.project_id = projects.id
          ORDER BY projects_tags.name
        ) AS tags
      ),
      '{}'::text[]
    ) AS tag_names
  SQL
end

Current Impact: Complex aggregation query executed for each row

Recommended Solution:
Add index for the tags join and consider caching:

-- Add to migration file
add_index :projects_projects_tags, [:project_id, :projects_tag_id],
          name: 'index_projects_projects_tags_composite'
add_index :projects_tags, [:name], name: 'index_projects_tags_on_name'

Alternative approach - Cache tag names in projects table:

# Add to Project model
after_save :cache_tag_names, if: :saved_change_to_id?

private

def cache_tag_names
  update_column(:cached_tag_names, tags.pluck(:name))
end

Expected Improvement: Optimize tags aggregation by ~30-40% (~50-80ms faster)
Effort: Medium

Issue #5: View Rendering Optimization

Location: target-repo/app/views/projects/_project_card.html.erb

Problem: Multiple method calls on project objects during rendering:

<!-- Multiple calls that could trigger DB queries -->
<% if project.upcoming? || project.ongoing? %>  <!-- Lines 3, 13, 17 -->
<% if project.club_deal? || project.tag_names.count.positive? %>  <!-- Line 49 -->
<% if project.published? || project.succeeded? %>  <!-- Line 66 -->
<% unless project.incubator? %>  <!-- Lines 68, 94 -->
<% if project.optf? %>  <!-- Line 79 -->

Current Impact: Potential repeated database calls and calculations

Recommended Solution:
Add view helpers and optimize status checks:

# Add to ApplicationHelper or ProjectsHelper
module ProjectsHelper
  def project_status_indicators(project)
    @status_cache ||= {}
    @status_cache[project.id] ||= {
      upcoming: project.upcoming?,
      ongoing: project.ongoing?,
      published: project.published?,
      succeeded: project.succeeded?,
      club_deal: project.club_deal?,
      incubator: project.incubator?,
      optf: project.optf?
    }
  end

  def cached_project_status(project, status)
    project_status_indicators(project)[status]
  end
end

Expected Improvement: Reduce view rendering time by ~20-30% (~50-80ms faster)
Effort: Low

Implementation Priority

1. Critical - Fix immediately (highest impact)

• Issue Add Claude Code GitHub Workflow #1: Add database indexes for date range queries
• Issue [Performance] Brevo::SyncContactWorker#perform - Slow response time (218884.54 s) #3: Enhance eager loading for associations

2. High - Fix soon (addresses major bottlenecks)

• Issue [Performance] Mailchimp::SyncUserWorker#perform - Slow response time (5171797.61 s) #2: Optimize user visibility filtering subquery
• Issue [Performance] Brevo::SyncContactWorker#perform - Slow response time (218884.54 s) #4: Add indexes for tags joins

3. Medium - Valuable improvements

• Issue [Performance] Brevo::SyncContactWorker#perform - Slow response time (218884.54 s) #5: Optimize view rendering with helpers

Additional Optimizations

Database Configuration

# Consider adding to application.rb for development
config.active_record.strict_loading_by_default = true  # Detect N+1 queries

Monitoring Setup

# Add to ApplicationController for performance tracking
around_action :track_performance, only: [:index]

private

def track_performance
  start_time = Time.current
  yield
ensure
  duration = (Time.current - start_time) * 1000
  Rails.logger.info "Action #{action_name} took #{duration.round(2)}ms"
end

Testing Recommendations

Before/After Performance Benchmarks

1. Measure baseline performance with current code
2. Apply fixes incrementally and measure each improvement
3. Monitor database query counts and execution times
4. Load test with realistic data volumes (1000+ projects)

Key Metrics to Monitor

• Total response time (target: <200ms)
• Database query time (target: <100ms)
• Number of SQL queries (target: <10 queries)
• View rendering time (target: <50ms)

Expected Overall Impact

Conservative Estimate: 400-500ms reduction in response time
Optimistic Estimate: 500-600ms reduction in response time

Target Performance After Fixes:

• Average Duration: ~150-200ms (down from 702.62ms)
• Database Time: <100ms (down from 418-634ms)
• View Rendering: <50ms (down from 171-283ms)

Migration Scripts

# Create migration: rails generate migration OptimizeProjectsPerformance

class OptimizeProjectsPerformance < ActiveRecord::Migration[7.0]
  def change
    # Issue #1: Date range query indexes
    add_index :projects, [:status, :intentions_ends_at],
              name: 'index_projects_on_status_and_intentions_ends_at'
    add_index :projects, [:visibility, :intentions_ends_at],
              name: 'index_projects_on_visibility_and_intentions_ends_at'
    add_index :projects, [:position, :intentions_ends_at],
              name: 'index_projects_on_position_and_intentions_ends_at'

    # Issue #4: Tags optimization
    add_index :projects_projects_tags, [:project_id, :projects_tag_id],
              name: 'index_projects_projects_tags_composite'
    add_index :projects_tags, [:name],
              name: 'index_projects_tags_on_name'

    # Optional: Add cached tag names column
    # add_column :projects, :cached_tag_names, :text, array: true, default: []
  end
end

This analysis provides a comprehensive roadmap to resolve the performance issues in the ProjectsController#index endpoint, with specific code fixes and measurable improvement targets.

_{🤖 Analyzed by Claude Code • Triggered by issue creation • Workflow Run}