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

Performance Metrics

Based on 10 sample(s):

Statistic	Duration
Minimum	621.20 ms
Maximum	772.47 ms
Average	669.52 ms
Median	650.37 ms

Sample Analysis

Analyzed 10 sample(s) from recent occurrences.

Sample 1

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

Time Breakdown

Component	Duration	Percentage
active_record	534.70 ms	77.2%
action_view	147.93 ms	21.4%
action_controller	6.58 ms	1.0%
active_support	3.20 ms	0.5%
redis	190.52 μs	0.0%

Memory Allocation

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

---

Sample 2

• Time: 2025-12-10 11:33:30 UTC
• Duration: 772.47 ms
• Action: ProjectsController#index
• Queue Duration: 0.00 μs

Time Breakdown

Component	Duration	Percentage
active_record	439.32 ms	57.2%
action_view	304.89 ms	39.7%
action_controller	19.29 ms	2.5%
active_support	3.74 ms	0.5%
redis	245.88 μs	0.0%

Memory Allocation

Component	Allocation
action_view	79.41 KB
action_controller	3.37 KB
active_record	2.73 KB
active_support	74.00 B
redis	22.00 B

---

Sample 3

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

Time Breakdown

Component	Duration	Percentage
active_record	431.37 ms	69.6%
action_view	174.50 ms	28.2%
action_controller	6.90 ms	1.1%
active_support	6.70 ms	1.1%
redis	223.72 μs	0.0%

Memory Allocation

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

---

Sample 4

• Time: 2025-12-10 11:31:25 UTC
• Duration: 718.89 ms
• Action: ProjectsController#index
• Queue Duration: 0.00 μs

Time Breakdown

Component	Duration	Percentage
active_record	421.33 ms	58.9%
action_view	238.22 ms	33.3%
action_controller	52.93 ms	7.4%
active_support	2.69 ms	0.4%
redis	204.32 μs	0.0%

Memory Allocation

Component	Allocation
action_view	80.33 KB
action_controller	4.51 KB
active_record	2.88 KB
active_support	75.00 B
redis	22.00 B

---

Sample 5

• Time: 2025-12-10 11:30:58 UTC
• Duration: 656.41 ms
• Action: ProjectsController#index
• Queue Duration: 0.00 μs

Time Breakdown

Component	Duration	Percentage
active_record	425.46 ms	65.4%
action_view	213.40 ms	32.8%
action_controller	7.99 ms	1.2%
active_support	3.14 ms	0.5%
redis	300.38 μ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 6

• Time: 2025-12-10 11:29:50 UTC
• Duration: 644.34 ms
• Action: ProjectsController#index
• Queue Duration: 0.00 μs

Time Breakdown

Component	Duration	Percentage
active_record	411.52 ms	64.5%
action_view	210.80 ms	33.0%
action_controller	7.92 ms	1.2%
active_support	7.32 ms	1.1%
redis	730.19 μs	0.1%

Memory Allocation

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

---

Sample 7

• Time: 2025-12-10 11:28:47 UTC
• Duration: 627.79 ms
• Action: ProjectsController#index
• Queue Duration: 0.00 μs

Time Breakdown

Component	Duration	Percentage
active_record	417.14 ms	67.1%
action_view	188.76 ms	30.4%
active_support	8.20 ms	1.3%
action_controller	7.12 ms	1.1%
redis	336.54 μs	0.1%

Memory Allocation

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

---

Sample 8

• Time: 2025-12-10 11:27:48 UTC
• Duration: 621.55 ms
• Action: ProjectsController#index
• Queue Duration: 0.00 μs

Time Breakdown

Component	Duration	Percentage
active_record	450.75 ms	72.9%
action_view	154.14 ms	24.9%
action_controller	9.82 ms	1.6%
active_support	2.96 ms	0.5%
redis	231.66 μs	0.0%

Memory Allocation

Component	Allocation
action_view	77.88 KB
action_controller	3.96 KB
active_record	2.98 KB
active_support	74.00 B
redis	22.00 B

---

Sample 9

• Time: 2025-12-10 11:26:57 UTC
• Duration: 707.06 ms
• Action: ProjectsController#index
• Queue Duration: 0.00 μs

Time Breakdown

Component	Duration	Percentage
active_record	432.60 ms	62.1%
action_view	245.18 ms	35.2%
action_controller	15.64 ms	2.2%
active_support	2.88 ms	0.4%
redis	248.79 μs	0.0%

Memory Allocation

Component	Allocation
action_view	79.21 KB
action_controller	3.25 KB
active_record	2.72 KB
active_support	75.00 B
redis	22.00 B

---

Sample 10

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

Time Breakdown

Component	Duration	Percentage
active_record	442.73 ms	71.7%
action_view	160.65 ms	26.0%
action_controller	11.20 ms	1.8%
active_support	2.70 ms	0.4%
redis	220.19 μs	0.0%

Memory Allocation

Component	Allocation
action_view	79.41 KB
action_controller	3.30 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.27 KB	10
action_controller	3.00 KB	10
active_record	2.76 KB	10
active_support	74.30 B	10
redis	22.00 B	10

[github-actions]

🔍 Performance Analysis Results

Analysis completed for repository IMhide/Blast

---

Performance Analysis Report: ProjectsController#index

Repository: IMhide/Blast
Issue: GitHub #13
Affected Endpoint: ProjectsController#index
Current Performance: 621-772ms response time (avg 669ms)
Primary Bottleneck: Database queries consuming 57-77% of response time (411-534ms)

Executive Summary

Analysis of the ProjectsController#index action reveals three critical performance bottlenecks that collectively cause the 400-500ms database query overhead reported in issue #13. The primary culprit is a custom .with_tags scope that executes a correlated subquery for every project, creating a disguised N+1 query pattern. Additionally, decorator methods trigger additional N+1 queries for subscription data during view rendering.

Root Cause Analysis

Critical Issue #1: Correlated Subquery N+1 Pattern in .with_tags Scope

Location: app/models/project.rb:450-467
Impact: 🔴 CRITICAL - Major contributor to 400ms+ database time

The .with_tags scope executes a correlated subquery for every single project loaded:

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  # <- Executes per project
          ORDER BY projects_tags.name
        ) AS tags
      ),
      '{}'::text[]
    ) AS tag_names
  SQL
end

Problem: For 30 projects (pagination limit), this executes 30 additional subqueries - one per project.

Critical Issue #2: Decorator N+1 Queries for Subscription Data

Location: app/decorators/project_decorator.rb:37-50
Impact: 🔴 CRITICAL - Triggers additional queries during rendering

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

Problem: Each method executes a separate database query per project when accessed in views.

Secondary Issue #3: Complex Visibility Filtering

Location: app/query_sets/projects/index_query_set.rb:69-78
Impact: 🟡 MODERATE - Adds query complexity

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

Problem: Creates complex OR query with subquery, preventing optimal index usage.

Recommended Fixes (Prioritized by Impact)

Fix #1: Replace Correlated Subquery with JOIN-based Tag Loading

Priority: 🔴 CRITICAL - Fix immediately
Expected Improvement: ~200-300ms reduction
Effort: Medium

Problem: The .with_tags scope creates a correlated subquery that executes once per project.

Recommended Solution: Replace with a proper JOIN and aggregation:

# CURRENT CODE (app/models/project.rb: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

# RECOMMENDED FIX
scope :with_tags, -> do
  left_joins(projects_tags: :projects_tags)
    .select('projects.*')
    .select('COALESCE(array_agg(projects_tags.name ORDER BY projects_tags.name) FILTER (WHERE projects_tags.name IS NOT NULL), ARRAY[]::text[]) AS tag_names')
    .group('projects.id')
end

Alternative Approach (if JOIN becomes complex):

# Option 2: Eager load tags and build tag_names in Ruby
def fetch_published_projects
  projects = Project
    .includes(:text_translations, projects_tags: :projects_tags)
    .published.where(intentions_ends_at: Time.current..)
    .order(position: :asc)

  # Add tag_names dynamically
  projects.each do |project|
    project.define_singleton_method(:tag_names) do
      projects_tags.map(&:name).sort
    end
  end

  filter_by_visibility(projects)
end

Fix #2: Eliminate Decorator N+1 Queries with Eager Loading

Priority: 🔴 CRITICAL - Fix immediately
Expected Improvement: ~100-150ms reduction
Effort: Low-Medium

Problem: collected_amount and subscribers_count trigger separate queries per project.

Recommended Solution: Precompute subscription data with database aggregations:

# CURRENT CODE (app/query_sets/projects/index_query_set.rb:42-48)
Project
  .with_tags
  .includes(:text_translations)
  .published.where(intentions_ends_at: Time.current..)
  .order(position: :asc)

# RECOMMENDED FIX
Project
  .with_tags_optimized  # Use the fixed scope from Fix #1
  .includes(:text_translations)
  .select(
    'projects.*',
    '(SELECT COALESCE(SUM(subscriptions.amount), 0) FROM subscriptions WHERE subscriptions.project_id = projects.id AND subscriptions.countable_in_gauge = true) AS collected_amount_cached',
    '(SELECT COUNT(DISTINCT subscriptions.users_profile_id) FROM subscriptions WHERE subscriptions.project_id = projects.id AND subscriptions.countable_in_gauge = true) AS subscribers_count_cached'
  )
  .published.where(intentions_ends_at: Time.current..)
  .order(position: :asc)

Update Decorator to Use Cached Values:

# app/decorators/project_decorator.rb
def collected_amount
  @__collected_amount ||= object.try(:collected_amount_cached) ||
    object.subscriptions.countable_in_gauge.sum(:amount)
end

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

Fix #3: Optimize Visibility Filtering with Better Indexing

Priority: 🟡 HIGH - Fix soon
Expected Improvement: ~20-50ms reduction
Effort: Low

Problem: Complex OR query with subqueries prevents optimal index usage.

Recommended Solution: Simplify the query pattern:

# CURRENT CODE (app/query_sets/projects/index_query_set.rb: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

# RECOMMENDED FIX
def visible_projects_for_user
  # Get IDs in a separate query to avoid complex OR
  visible_for_investors_ids = user
    .projects_with_intention
    .where(visibility: :visible_for_investors)
    .pluck(:id)

  # Use simple IN clause
  Project.where(
    "visibility = ? OR id IN (?)",
    'visible',
    visible_for_investors_ids.empty? ? [0] : visible_for_investors_ids
  )
end

Database Index Recommendations:

-- Ensure these indexes exist for optimal performance
CREATE INDEX CONCURRENTLY idx_projects_visibility_status ON projects (visibility, status);
CREATE INDEX CONCURRENTLY idx_projects_intentions_ends_at ON projects (intentions_ends_at);
CREATE INDEX CONCURRENTLY idx_subscriptions_project_countable ON subscriptions (project_id, countable_in_gauge, users_profile_id);

Additional Optimizations

Quick Win #1: Cache Published Projects Count

Effort: Low
Impact: Minor performance improvement

# app/query_sets/projects/index_query_set.rb
def published_projects
  @_published_projects ||= Rails.cache.fetch(
    "published_projects_#{user&.id}_#{admin_user.present?}",
    expires_in: 5.minutes
  ) do
    fetch_published_projects.decorate
  end
end

Quick Win #2: Add Database Query Monitoring

Effort: Low
Impact: Ongoing monitoring for regressions

# config/initializers/active_record_logging.rb
if Rails.env.development? || Rails.env.staging?
  ActiveSupport::Notifications.subscribe 'sql.active_record' do |name, started, finished, unique_id, data|
    duration = finished - started
    if duration > 0.1 # Log queries taking more than 100ms
      Rails.logger.warn "SLOW QUERY (#{duration.round(3)}s): #{data[:sql]}"
    end
  end
end

Implementation Priority

Phase 1: Critical Fixes (Immediate)

1. ✅ Fix Add Claude Code GitHub Workflow #1: Replace .with_tags correlated subquery (Expected: -250ms)
2. ✅ Fix [Performance] Mailchimp::SyncUserWorker#perform - Slow response time (5171797.61 s) #2: Eager load subscription data (Expected: -120ms)

Phase 2: High Priority (This Sprint)

3. ✅ Fix [Performance] Brevo::SyncContactWorker#perform - Slow response time (218884.54 s) #3: Optimize visibility filtering (Expected: -30ms)
4. ✅ Database indexes: Add recommended indexes

Phase 3: Optimizations (Next Sprint)

5. ⚡ Caching layer: Cache published projects
6. 📊 Monitoring: Add slow query alerts

Expected Results

Before Optimization:

• Average response time: 669ms
• Database time: 411-534ms (57-77%)
• View rendering: 147-304ms (21-40%)

After All Fixes:

• Expected average response time: ~270ms (60% improvement)
• Expected database time: ~100ms (75% improvement)
• Expected view rendering: ~100ms (50% improvement)

Testing Recommendations

Performance Benchmarking

# Before optimization
curl -w "@curl-format.txt" "http://localhost:3000/projects"

# After each fix - verify improvements
ab -n 50 -c 5 http://localhost:3000/projects

SQL Query Analysis

# In Rails console - verify query efficiency
User.first.then do |user|
  explained = Projects::IndexQuerySet.new(
    user: user,
    admin_user: nil,
    pagy_params: { page: 1, items: 30 }
  ).published_projects.explain
  puts explained
end

Monitoring Metrics

• Response time percentiles (p50, p90, p95)
• Database query count per request
• Database query time percentage
• Memory usage during project loading

---

Implementation Notes: Start with Fix #1 (.with_tags optimization) as it provides the largest performance gain. Each fix builds incrementally and can be deployed independently. Monitor query performance after each change to validate improvements.

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