yss-project/cloud/functions/optimize.sql

-- ============================================================
-- project-load 云函数性能优化索引
-- 执行前请先备份数据库！
-- ============================================================

-- 1. Project 表索引 - 核心查询优化
-- 用于按公司+状态+时间范围查询
CREATE INDEX IF NOT EXISTS "idx_project_company_status_deleted"
ON "Project" ("company", "status", "isDeleted")
WHERE "isDeleted" IS NOT TRUE AND "status" != '已完成';

-- 用于时间范围查询（deadline 和 createdAt）
CREATE INDEX IF NOT EXISTS "idx_project_company_deadline"
ON "Project" ("company", "deadline")
WHERE "isDeleted" IS NOT TRUE;

CREATE INDEX IF NOT EXISTS "idx_project_company_created"
ON "Project" ("company", "createdAt")
WHERE "isDeleted" IS NOT TRUE;

-- 复合索引：公司 + 更新时间（用于排序）
CREATE INDEX IF NOT EXISTS "idx_project_company_updated"
ON "Project" ("company", "updatedAt" DESC)
WHERE "isDeleted" IS NOT TRUE;

-- 2. Profile 表索引 - 工作负载查询优化
CREATE INDEX IF NOT EXISTS "idx_profile_company_role"
ON "Profile" ("company", "roleName")
WHERE "isDeleted" IS NOT TRUE;

-- 3. ProjectTeam 表索引 - 团队成员关联查询
CREATE INDEX IF NOT EXISTS "idx_projectteam_project"
ON "ProjectTeam" ("project")
WHERE "isDeleted" IS NOT TRUE;

CREATE INDEX IF NOT EXISTS "idx_projectteam_profile"
ON "ProjectTeam" ("profile")
WHERE "isDeleted" IS NOT TRUE;

-- 4. Product 表索引 - 空间查询优化
CREATE INDEX IF NOT EXISTS "idx_product_project"
ON "Product" ("project")
WHERE ("isDeleted" IS NULL OR "isDeleted" = false);

-- 5. ProjectFile 表索引 - 交付物查询优化
CREATE INDEX IF NOT EXISTS "idx_projectfile_project_type"
ON "ProjectFile" ("project", "fileType")
WHERE ("isDeleted" IS NULL OR "isDeleted" = false)
  AND ("fileType" LIKE 'delivery_%' OR "data"->>'uploadStage' = 'delivery');

-- 用于 spaceId/productId 查找
CREATE INDEX IF NOT EXISTS "idx_projectfile_spaceid"
ON "ProjectFile" (("data"->>'spaceId'))
WHERE ("isDeleted" IS NULL OR "isDeleted" = false);

-- 6. ProjectIssue 表索引
CREATE INDEX IF NOT EXISTS "idx_issue_project_status"
ON "ProjectIssue" ("project", "status")
WHERE ("isDeleted" IS NULL OR "isDeleted" = false)
  AND "status" IN ('待处理', '处理中');

-- ============================================================
-- 分析表统计信息（执行后让查询优化器更好地选择索引）
-- ============================================================
ANALYZE "Project";
ANALYZE "Profile";
ANALYZE "ProjectTeam";
ANALYZE "Product";
ANALYZE "ProjectFile";
ANALYZE "ProjectIssue";

-- ============================================================
-- 查看索引使用情况（诊断用）
-- ============================================================
-- SELECT
--   schemaname, tablename, indexname,
--   idx_scan as index_scans,
--   idx_tup_read as tuples_read,
--   idx_tup_fetch as tuples_fetched
-- FROM pg_stat_user_indexes
-- WHERE tablename IN ('Project', 'Profile', 'ProjectTeam', 'Product', 'ProjectFile', 'ProjectIssue')
-- ORDER BY tablename, idx_scan DESC;