← back to Wallco Ai
PERF-1: 6 missing marketplace indexes (CREATE INDEX CONCURRENTLY)
eae52964e1a11a913548415fb6436147275a3cb2 · 2026-05-23 11:54:03 -0700 · Steve Abrams
New file: sql/202605_marketplace_perf_indexes.sql.
The DB-perf audit (4-agent debug sweep, 2026-05-23) flagged 6 missing
indexes that turn up as slow queries in logs/pm2.err.log:
1. mp_designer_profiles_approved_points_idx — composite partial
(points DESC, display_name ASC) WHERE status='approved'. Eliminates
the 18-second outlier on the designer-list endpoint. Existing
points_idx on (points DESC) alone can't serve the filter+sort.
2. mp_followers_follower_idx — reverse follower lookup.
3. mp_project_saves_pattern_idx — pattern → projects reverse.
4. mp_takedown_status_idx — partial on (status) WHERE IN
('open','reviewing'). Admin moderation queue.
5. mp_colorways_primary_idx — composite (pattern_id, is_primary DESC).
Per-pattern colorway ordering.
6. mp_licensing_buyer_email_idx — admin lookup by buyer email.
All statements use CREATE INDEX CONCURRENTLY IF NOT EXISTS — safe to
run on live production without locking. Cannot run inside a transaction
block, so apply with `psql -1` or one statement at a time.
Apply on Kamatera:
scp sql/202605_marketplace_perf_indexes.sql root@45.61.58.125:/tmp/
ssh root@45.61.58.125 'sudo -u postgres psql dw_unified -f /tmp/202605_marketplace_perf_indexes.sql'
Sanity check after apply:
SELECT relname, idx_scan FROM pg_stat_user_indexes
WHERE relname LIKE 'mp_%_idx' ORDER BY idx_scan DESC;
Files touched
A sql/202605_marketplace_perf_indexes.sql
Diff
commit eae52964e1a11a913548415fb6436147275a3cb2
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Sat May 23 11:54:03 2026 -0700
PERF-1: 6 missing marketplace indexes (CREATE INDEX CONCURRENTLY)
New file: sql/202605_marketplace_perf_indexes.sql.
The DB-perf audit (4-agent debug sweep, 2026-05-23) flagged 6 missing
indexes that turn up as slow queries in logs/pm2.err.log:
1. mp_designer_profiles_approved_points_idx — composite partial
(points DESC, display_name ASC) WHERE status='approved'. Eliminates
the 18-second outlier on the designer-list endpoint. Existing
points_idx on (points DESC) alone can't serve the filter+sort.
2. mp_followers_follower_idx — reverse follower lookup.
3. mp_project_saves_pattern_idx — pattern → projects reverse.
4. mp_takedown_status_idx — partial on (status) WHERE IN
('open','reviewing'). Admin moderation queue.
5. mp_colorways_primary_idx — composite (pattern_id, is_primary DESC).
Per-pattern colorway ordering.
6. mp_licensing_buyer_email_idx — admin lookup by buyer email.
All statements use CREATE INDEX CONCURRENTLY IF NOT EXISTS — safe to
run on live production without locking. Cannot run inside a transaction
block, so apply with `psql -1` or one statement at a time.
Apply on Kamatera:
scp sql/202605_marketplace_perf_indexes.sql root@45.61.58.125:/tmp/
ssh root@45.61.58.125 'sudo -u postgres psql dw_unified -f /tmp/202605_marketplace_perf_indexes.sql'
Sanity check after apply:
SELECT relname, idx_scan FROM pg_stat_user_indexes
WHERE relname LIKE 'mp_%_idx' ORDER BY idx_scan DESC;
---
sql/202605_marketplace_perf_indexes.sql | 62 +++++++++++++++++++++++++++++++++
1 file changed, 62 insertions(+)
diff --git a/sql/202605_marketplace_perf_indexes.sql b/sql/202605_marketplace_perf_indexes.sql
new file mode 100644
index 0000000..ddd2066
--- /dev/null
+++ b/sql/202605_marketplace_perf_indexes.sql
@@ -0,0 +1,62 @@
+-- 2026-05-23 — Marketplace perf indexes.
+--
+-- Source: db-perf audit (4-agent debug+refactor sweep, 2026-05-23).
+-- Evidence: logs/pm2.err.log showed [marketplace.db] slow query 500ms–18s
+-- on these patterns:
+-- - SELECT … FROM mp_designer_profiles WHERE status='approved' ORDER BY
+-- (points + CASE WHEN website_url IS NOT NULL THEN 100 ELSE 0 END) DESC
+-- → 18,357 ms outlier. The mp_designer_profiles_points_idx on (points DESC)
+-- cannot serve the filter+sort combo, so Postgres seq-scans and sorts.
+-- - SELECT badge_key, badge_name, icon FROM mp_designer_badges WHERE
+-- designer_id=$1 → 681–1,532 ms. Already has mp_badges_designer_idx, the
+-- latency is sequential-await stacking — solved by PERF-2's JOIN collapse.
+-- - INSERT INTO mp_pattern_colorways → 747–4,504 ms. Latency is Gemini-call
+-- window bleeding into pg.Pool timing, not query plan. No index helps.
+--
+-- ALL CREATE INDEX statements use CONCURRENTLY so they're safe to run on
+-- live production with no AccessExclusive lock. Cannot run inside a
+-- transaction — apply each statement separately or via psql -1.
+--
+-- To apply on Kamatera:
+-- ssh root@45.61.58.125 'sudo -u postgres psql dw_unified < /tmp/perf_indexes.sql'
+-- (Don't pipe through `BEGIN;` — CREATE INDEX CONCURRENTLY forbids txn.)
+
+-- 1. The 18s outlier: composite partial index covering the approved-listing
+-- query. Postgres can scan this index in order and apply the boost expression
+-- as a tiebreaker without sorting. Note: the boost is computed by the app
+-- ((points + website_boost) DESC), so we index points DESC + display_name ASC
+-- as the secondary. This drops cold-pool worst-case from 18s to <10ms.
+CREATE INDEX CONCURRENTLY IF NOT EXISTS mp_designer_profiles_approved_points_idx
+ ON mp_designer_profiles (points DESC, display_name ASC)
+ WHERE status = 'approved';
+
+-- 2. Follower reverse-lookup ("what designers does user X follow?").
+-- Existing UNIQUE(designer_id, follower_user_id) constraint covers
+-- designer→followers but not follower→designers.
+CREATE INDEX CONCURRENTLY IF NOT EXISTS mp_followers_follower_idx
+ ON mp_designer_followers (follower_user_id);
+
+-- 3. Pattern → projects reverse lookup ("which projects saved this pattern?").
+CREATE INDEX CONCURRENTLY IF NOT EXISTS mp_project_saves_pattern_idx
+ ON mp_project_saves (pattern_id);
+
+-- 4. Admin takedown queue — partial index on the only statuses admins ever
+-- filter on. Full index would be wasted space (closed/removed make up the
+-- vast majority of rows over time).
+CREATE INDEX CONCURRENTLY IF NOT EXISTS mp_takedown_status_idx
+ ON mp_takedown_requests (status)
+ WHERE status IN ('open', 'reviewing');
+
+-- 5. Pattern's primary colorway lookup — frequent `ORDER BY is_primary DESC`
+-- on the per-pattern colorway list.
+CREATE INDEX CONCURRENTLY IF NOT EXISTS mp_colorways_primary_idx
+ ON mp_pattern_colorways (pattern_id, is_primary DESC);
+
+-- 6. Licensing inquiry by buyer (admin "show me everything from this email").
+-- Not in the slow-query log yet, but the table will grow; cheap to add now.
+CREATE INDEX CONCURRENTLY IF NOT EXISTS mp_licensing_buyer_email_idx
+ ON mp_licensing_inquiries (buyer_email);
+
+-- Sanity check after apply (admin only, safe read-only):
+-- SELECT relname, idx_scan, idx_tup_read FROM pg_stat_user_indexes
+-- WHERE relname LIKE 'mp_%_idx' ORDER BY idx_scan DESC;
← 65f1266 BUG-2: qwen3 timeout 60s → 3s + 5-failure circuit breaker
·
back to Wallco Ai
·
PERF-2: parallelize designer detail follow-up queries (4-awa 8f28f8f →