bug：接口查询多半是没命中索引导致服务器cpu性能被吃满

[HanBBQovo]

[github-actions]

Thank you for reporting this issue.

Analysis:

After a thorough investigation of the schema (src/drizzle/schema.ts) and query patterns across the repository layer, there are indeed several missing indexes that could cause full table scans and high CPU usage. Here are the findings:

---

1. CRITICAL: Missing Index on keys.key (API Key Lookup)

The keys table has no index on the key column (src/drizzle/schema.ts, lines 125-130). The only indexes defined are on user_id, created_at, and deleted_at.

However, every single API request triggers a lookup by key:

• src/app/v1/_lib/proxy/auth-guard.ts:90 calls validateApiKeyAndGetUser(apiKey)
• src/repository/key.ts:493 queries eq(keys.key, keyString) (in validateApiKeyAndGetUser)
• src/repository/key.ts:428 queries eq(keys.key, keyString) (in findActiveKeyByKeyString)

This means every API request performs a full table scan on the keys table. No migration in drizzle/ adds this index either.

Suggested fix:

CREATE INDEX idx_keys_key ON keys (key) WHERE deleted_at IS NULL;

---

2. HIGH: AT TIME ZONE Conversions Prevent Index Usage

Multiple queries in src/repository/overview.ts and src/repository/statistics.ts use AT TIME ZONE conversions on created_at, which prevents PostgreSQL from using B-tree indexes:

• src/repository/overview.ts:58:

  (created_at AT TIME ZONE $tz)::date = (CURRENT_TIMESTAMP AT TIME ZONE $tz)::date

• src/repository/statistics.ts (lines 44, 80, 116): Similar patterns in hourly/daily time-series queries with generate_series.

These transformations force sequential scans because the index is on created_at directly, not on the timezone-converted value.

Suggested approach: Convert to range conditions on the raw created_at column instead, e.g.:

WHERE created_at >= (CURRENT_DATE AT TIME ZONE $tz) AT TIME ZONE $tz
  AND created_at <  (CURRENT_DATE + INTERVAL '1 day' AT TIME ZONE $tz) AT TIME ZONE $tz

---

3. HIGH: Joins on Unindexed keys.key in Cost Aggregation Queries

Several cost aggregation queries join message_request to keys on the key column (which lacks an index on the keys side):

• src/repository/key.ts:297: eq(messageRequest.key, keys.key) in findKeyUsageToday()
• src/lib/proxy-status-tracker.ts:162: LEFT JOIN on eq(keys.key, messageRequest.key)
• src/repository/activity-stream.ts:97: LEFT JOIN on eq(messageRequest.key, keysTable.key)

---

4. MEDIUM: Missing Composite Indexes for Time-Range Cost Aggregations

The message_request table has an index on (userId, createdAt, costUsd) with deletedAt IS NULL filter, but many queries also filter by blockedBy (excluding warmup requests). Key queries affected:

• src/repository/statistics.ts:777 - sumKeyTotalCost(): filters by key + createdAt range
• src/repository/statistics.ts:806 - sumUserTotalCost(): filters by userId + createdAt range
• src/repository/statistics.ts:863 - sumUserCostInTimeRange(): filters by userId + time range
• src/repository/statistics.ts:899 - sumKeyCostInTimeRange(): filters by key + time range
• src/repository/leaderboard.ts:214 - Leaderboard ranking with time window aggregations

These could benefit from composite indexes that include blockedBy in the WHERE clause.

---

Summary of Missing Indexes

Table	Column(s)	Impact	Frequency
keys	key	Full table scan per API request	Every request
message_request	key, createdAt (composite)	Cost aggregation by key	Rate limiting, dashboard
message_request	providerId, createdAt (composite)	Provider cost queries	Provider stats, leaderboard

Recommended Immediate Action

Adding an index on keys.key is the highest priority fix since it affects every API request:

CREATE INDEX idx_keys_key ON keys (key) WHERE deleted_at IS NULL;

For the AT TIME ZONE queries, refactoring to use range conditions on the raw created_at column would allow existing indexes to be used.

---

Automated response from Claude AI