38 lines
1.6 KiB
SQL
38 lines
1.6 KiB
SQL
-- =============================================================================
|
||
-- Migration 040: Dashboard / PChome 熱路徑索引
|
||
-- MOMO PRO — 2026-05-20 重開機後首頁慢查詢修復持久化
|
||
-- =============================================================================
|
||
-- 背景:
|
||
-- 2026-05-20 主機重開機後，https://mo.wooo.work/ 首頁雖可用但多次
|
||
-- 8-45 秒逾時。實際瓶頸在首頁與 PChome coverage 查詢反覆掃描
|
||
-- products、price_records、competitor_match_attempts。
|
||
--
|
||
-- 設計:
|
||
-- 1. 只新增索引，不改資料、不調整欄位、不重啟容器。
|
||
-- 2. 使用 IF NOT EXISTS，讓 live 已手動套用索引、fresh restore、重跑 migration
|
||
-- 都維持冪等。
|
||
-- 3. 不使用 CREATE INDEX CONCURRENTLY，避免被包在 transaction 的 migration
|
||
-- runner 執行時失敗；若未來超大型 live DB 需要零鎖定窗口，請在維護窗口
|
||
-- 手動轉換為 CONCURRENTLY 流程。
|
||
-- =============================================================================
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_comp_match_attempts_source_sku_attempted_at
|
||
ON competitor_match_attempts (source, sku, attempted_at DESC)
|
||
INCLUDE (attempt_status);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_price_records_product_timestamp_id_desc
|
||
ON price_records (product_id, timestamp DESC, id DESC)
|
||
INCLUDE (price);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_products_status_id_icode
|
||
ON products (status, id, i_code);
|
||
|
||
ANALYZE products;
|
||
ANALYZE price_records;
|
||
ANALYZE competitor_match_attempts;
|
||
|
||
DO $$
|
||
BEGIN
|
||
RAISE NOTICE 'Migration 040 完成 — Dashboard / PChome 熱路徑索引已確認';
|
||
END $$;
|