[Performance] ProjectsController#index - Slow response time (2181619.40 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	2181619.40 s
Affected Actions	ProjectsController#index
State	CLOSED
Severity	UNTRIAGED

Performance Metrics

Based on 10 sample(s):

Statistic	Duration
Minimum	622.83 ms
Maximum	827.94 ms
Average	674.51 ms
Median	657.95 ms

Sample Analysis

Analyzed 10 sample(s) from recent occurrences.

Sample 1

• Time: 2025-12-10 10:51:24 UTC
• Duration: 698.68 ms
• Action: ProjectsController#index
• Queue Duration: 0.00 μs

Time Breakdown

Component	Duration	Percentage
active_record	429.52 ms	63.0%
action_view	236.79 ms	34.7%
action_controller	11.06 ms	1.6%
active_support	4.03 ms	0.6%
redis	500.74 μs	0.1%

Memory Allocation

Component	Allocation
action_view	79.42 KB
active_record	2.73 KB
action_controller	2.27 KB
active_support	74.00 B
redis	22.00 B

---

Sample 2

• Time: 2025-12-10 10:50:44 UTC
• Duration: 627.75 ms
• Action: ProjectsController#index
• Queue Duration: 0.00 μs

Time Breakdown

Component	Duration	Percentage
active_record	434.47 ms	69.9%
action_view	175.33 ms	28.2%
action_controller	8.20 ms	1.3%
active_support	3.68 ms	0.6%
redis	257.55 μs	0.0%

Memory Allocation

Component	Allocation
action_view	79.42 KB
active_record	2.73 KB
action_controller	2.55 KB
active_support	75.00 B
redis	22.00 B

---

Sample 3

• Time: 2025-12-10 10:49:36 UTC
• Duration: 827.94 ms
• Action: ProjectsController#index
• Queue Duration: 0.00 μs

Time Breakdown

Component	Duration	Percentage
active_record	460.19 ms	55.8%
action_view	330.85 ms	40.1%
action_controller	28.98 ms	3.5%
active_support	3.72 ms	0.5%
redis	381.57 μs	0.0%

Memory Allocation

Component	Allocation
action_view	79.63 KB
action_controller	4.41 KB
active_record	2.77 KB
active_support	74.00 B
redis	22.00 B

---

Sample 4

• Time: 2025-12-10 10:48:17 UTC
• Duration: 662.65 ms
• Action: ProjectsController#index
• Queue Duration: 0.00 μs

Time Breakdown

Component	Duration	Percentage
active_record	416.79 ms	63.3%
action_view	222.16 ms	33.7%
action_controller	13.81 ms	2.1%
active_support	5.09 ms	0.8%
redis	784.00 μs	0.1%

Memory Allocation

Component	Allocation
action_view	79.74 KB
action_controller	3.40 KB
active_record	2.74 KB
active_support	75.00 B
redis	22.00 B

---

Sample 5

• Time: 2025-12-10 10:47:00 UTC
• Duration: 686.62 ms
• Action: ProjectsController#index
• Queue Duration: 0.00 μs

Time Breakdown

Component	Duration	Percentage
active_record	441.15 ms	64.9%
action_view	223.80 ms	32.9%
action_controller	8.43 ms	1.2%
active_support	5.59 ms	0.8%
redis	280.59 μs	0.0%

Memory Allocation

Component	Allocation
action_view	79.73 KB
active_record	2.78 KB
action_controller	2.27 KB
active_support	74.00 B
redis	22.00 B

---

Sample 6

• Time: 2025-12-10 10:46:59 UTC
• Duration: 622.83 ms
• Action: ProjectsController#index
• Queue Duration: 0.00 μs

Time Breakdown

Component	Duration	Percentage
active_record	429.57 ms	70.0%
action_view	161.36 ms	26.3%
action_controller	11.89 ms	1.9%
active_support	10.90 ms	1.8%
redis	360.07 μs	0.1%

Memory Allocation

Component	Allocation
action_view	79.42 KB
action_controller	3.58 KB
active_record	2.81 KB
active_support	2.38 KB
redis	59.00 B

---

Sample 7

• Time: 2025-12-10 10:45:36 UTC
• Duration: 647.45 ms
• Action: ProjectsController#index
• Queue Duration: 0.00 μs

Time Breakdown

Component	Duration	Percentage
active_record	477.97 ms	74.6%
action_view	153.20 ms	23.9%
action_controller	7.39 ms	1.2%
active_support	1.98 ms	0.3%
redis	238.92 μs	0.0%

Memory Allocation

Component	Allocation
action_view	80.06 KB
active_record	2.82 KB
action_controller	2.55 KB
active_support	74.00 B
redis	22.00 B

---

Sample 8

• Time: 2025-12-10 10:44:40 UTC
• Duration: 653.25 ms
• Action: ProjectsController#index
• Queue Duration: 0.00 μs

Time Breakdown

Component	Duration	Percentage
active_record	466.61 ms	71.7%
action_view	161.37 ms	24.8%
active_support	12.57 ms	1.9%
action_controller	9.85 ms	1.5%
redis	202.08 μs	0.0%

Memory Allocation

Component	Allocation
action_view	79.43 KB
action_controller	3.58 KB
active_record	2.74 KB
active_support	74.00 B
redis	22.00 B

---

Sample 9

• Time: 2025-12-10 10:43:22 UTC
• Duration: 636.01 ms
• Action: ProjectsController#index
• Queue Duration: 0.00 μs

Time Breakdown

Component	Duration	Percentage
active_record	413.77 ms	65.3%
action_view	201.94 ms	31.9%
action_controller	12.54 ms	2.0%
active_support	4.70 ms	0.7%
redis	319.10 μs	0.1%

Memory Allocation

Component	Allocation
action_view	80.12 KB
action_controller	4.35 KB
active_record	2.85 KB
active_support	75.00 B
redis	22.00 B

---

Sample 10

• Time: 2025-12-10 10:42:58 UTC
• Duration: 681.89 ms
• Action: ProjectsController#index
• Queue Duration: 0.00 μs

Time Breakdown

Component	Duration	Percentage
active_record	505.82 ms	74.8%
action_view	159.86 ms	23.6%
action_controller	6.28 ms	0.9%
active_support	3.94 ms	0.6%
redis	219.19 μs	0.0%

Memory Allocation

Component	Allocation
action_view	79.41 KB
active_record	2.73 KB
action_controller	2.55 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.64 KB	10
action_controller	3.15 KB	10
active_record	2.77 KB	10
active_support	311.00 B	10
redis	25.70 B	10

[github-actions]

🔍 Performance Analysis Results

Analysis completed for repository IMhide/Blast

---

Performance Analysis Report: ProjectsController#index

Issue Summary

Repository: IMhide/Blast
Issue: GitHub #11
Endpoint: ProjectsController#index
Critical Metrics:

• Sample Average: 674.51ms (range: 622.83ms - 827.94ms)
• Active Record: 55.8-74.8% of execution time (413-505ms)
• Action View: 23.6-40.1% of execution time (153-330ms)

Root Cause Analysis

After thorough code analysis, I've identified the primary performance bottlenecks:

🔴 Critical Issue #1: N+1 Queries in ProjectDecorator

Problem: Multiple database queries executed for each project during rendering
Location: app/decorators/project_decorator.rb:38-51
Impact: 30+ additional queries per request (1 per project)

Current Problematic Code:

def collected_amount
  @__collected_amount ||= object.subscriptions.countable_in_gauge.sum(:amount)
end

def subscribers_count
  @__subscribers_count ||= object.subscriptions.countable_in_gauge.pluck(:users_profile_id).uniq.size
end

Each project in the listing (30 per page) triggers separate queries for subscription data, resulting in:

• 30 queries for collected_amount calculations
• 30 queries for subscribers_count calculations
• Total: 60+ additional database queries per page load

🔴 Critical Issue #2: Inefficient with_tags Scope

Problem: Complex SQL aggregation executed multiple times
Location: app/models/project.rb:450-467
Impact: Expensive array_agg operations for tag data

Current Implementation:

SELECT 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

This subquery executes for every project, creating nested SELECT operations that are computationally expensive.

🔴 Critical Issue #3: Missing Eager Loading

Problem: No preloading of subscription associations
Location: app/query_sets/projects/index_query_set.rb:51-61
Impact: Lazy loading triggers N+1 queries in decorator

Current Code:

def fetch_financed_projects
  projects = Project
    .with_tags
    .includes(:text_translations)  # ✅ Translations loaded
    .published_or_succeeded
    .where(intentions_ends_at: ..Time.current)
    .order(position: :asc)
    # ❌ Missing: .includes(:subscriptions)

Recommended Fixes (Prioritized)

Fix #1: Add Subscription Eager Loading

Priority: CRITICAL
Expected Improvement: Reduce queries from ~60 to ~3, save 200-300ms
Effort: Low

Location: app/query_sets/projects/index_query_set.rb:51-61

CURRENT CODE:

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

RECOMMENDED FIX:

def fetch_financed_projects
  projects = Project
    .with_tags
    .includes(:text_translations, :countable_subscriptions)  # Add subscription preloading
    .published_or_succeeded
    .where(intentions_ends_at: ..Time.current)
    .order(position: :asc)

Also apply to published_projects:

def fetch_published_projects
  projects = Project
    .with_tags
    .includes(:text_translations, :countable_subscriptions)  # Add here too
    .published
    .where(intentions_ends_at: Time.current..)
    .order(position: :asc)

Fix #2: Optimize ProjectDecorator Calculations

Priority: HIGH
Expected Improvement: Eliminate N+1 queries, save 100-200ms
Effort: Medium

Location: app/decorators/project_decorator.rb:37-51

CURRENT CODE (causing N+1):

def collected_amount
  @__collected_amount ||= object.subscriptions.countable_in_gauge.sum(:amount)
end

def subscribers_count
  @__subscribers_count ||= object.subscriptions.countable_in_gauge.pluck(:users_profile_id).uniq.size
end

RECOMMENDED FIX:

def collected_amount
  @__collected_amount ||=
    if object.association(:countable_subscriptions).loaded?
      object.countable_subscriptions.sum(&:amount)
    else
      object.subscriptions.countable_in_gauge.sum(:amount)
    end
end

def subscribers_count
  @__subscribers_count ||=
    if object.association(:countable_subscriptions).loaded?
      object.countable_subscriptions.map(&:users_profile_id).uniq.size
    else
      object.subscriptions.countable_in_gauge.pluck(:users_profile_id).uniq.size
    end
end

Fix #3: Optimize with_tags Scope with Database Solution

Priority: HIGH
Expected Improvement: Reduce complex subquery overhead, save 50-100ms
Effort: Medium

Problem: Current implementation uses expensive subquery aggregation

Option A: Database Index Optimization

-- Add composite index for better tag query performance
CREATE INDEX CONCURRENTLY idx_projects_projects_tags_project_tag
ON projects_projects_tags (project_id, projects_tag_id);

-- Add index on projects_tags.name for sorting
CREATE INDEX CONCURRENTLY idx_projects_tags_name
ON projects_tags (name);

Option B: Alternative Implementation (if tags are not frequently changing)

# In Project model, add cached tag names column
scope :with_tags, -> do
  includes(:projects_tags).select('projects.*,
    ARRAY(
      SELECT projects_tags.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 tag_names'
  )
end

Fix #4: Add Database Indexes for Query Optimization

Priority: MEDIUM
Expected Improvement: Faster filtering and ordering, save 25-50ms
Effort: Low

Add performance indexes:

-- Optimize WHERE clauses in fetch methods
CREATE INDEX CONCURRENTLY idx_projects_intentions_ends_at_status
ON projects (intentions_ends_at, status)
WHERE status IN ('published', 'succeeded');

-- Optimize ORDER BY position
CREATE INDEX CONCURRENTLY idx_projects_position_published
ON projects (position)
WHERE status = 'published';

-- Optimize visibility filtering
CREATE INDEX CONCURRENTLY idx_projects_visibility_status
ON projects (visibility, status);

Fix #5: View Template Optimization

Priority: LOW
Expected Improvement: Reduce view rendering time, save 10-30ms
Effort: Low

Location: app/views/projects/_project_card.html.erb:52-56

CURRENT CODE:

<% project.tag_names.each do |tag_name| %>
  <span class="bg-primary-50 rounded p-2 pb-1 text-xs">
    <%= tag_name %>
  </span>
<% end %>

RECOMMENDED OPTIMIZATION:

<% if project.tag_names.present? %>
  <% project.tag_names.each do |tag_name| %>
    <span class="bg-primary-50 rounded p-2 pb-1 text-xs">
      <%= tag_name %>
    </span>
  <% end %>
<% end %>

Additional Optimizations

Caching Strategy

Consider adding fragment caching for project cards:

<% cache(project, expires_in: 1.hour) do %>
  <!-- project card content -->
<% end %>

Monitoring Enhancements

1. Add performance monitoring for specific query components
2. Set up alerts for response times > 500ms
3. Monitor N+1 query detection with bullet gem in staging

Implementation Priority

Phase 1 (Critical - Implement Immediately)

1. Fix Add Claude Code GitHub Workflow #1: Add subscription eager loading - saves 200-300ms
2. Fix [Performance] Mailchimp::SyncUserWorker#perform - Slow response time (5171797.61 s) #2: Optimize decorator calculations - eliminates N+1 queries

Phase 2 (High Priority - Next Sprint)

3. Fix [Performance] Brevo::SyncContactWorker#perform - Slow response time (218884.54 s) #3: Optimize with_tags scope or add indexes
4. Fix [Performance] Brevo::SyncContactWorker#perform - Slow response time (218884.54 s) #4: Add database performance indexes

Phase 3 (Nice to Have)

5. Fix [Performance] Brevo::SyncContactWorker#perform - Slow response time (218884.54 s) #5: View template optimizations
6. Fragment caching implementation

Testing Recommendations

Before Implementation

1. Benchmark current performance:

   # In Rails console
   Project.with_tags.includes(:text_translations).limit(30).each(&:decorate)

2. Enable query logging:

   ActiveRecord::Base.logger = Logger.new(STDOUT)

After Implementation

1. Verify query count reduction:

   • Should see ~3 queries instead of 60+
   • No N+1 queries in bullet gem reports

2. Performance verification:

   • Response times should drop to 400-500ms range
   • Active Record component should drop to 30-40% of total time

Load Testing

# Test with realistic load
ab -n 100 -c 10 http://localhost:3000/projects

Expected Results

Current Performance:

• Average Response Time: 674ms
• Database Time: 413-505ms (55-75%)
• Query Count: ~60+ per request

After Optimization:

• Expected Response Time: 300-400ms (~40% improvement)
• Expected Database Time: 150-200ms (~60% reduction)
• Expected Query Count: 3-5 per request (~90% reduction)

This optimization should address the core performance bottlenecks identified in the AppSignal report and significantly improve the user experience for the projects listing page.

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