借助AI工具优化SQL递归查询：从性能瓶颈到高效解决方案

CREATE TABLE organizations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    parent_id INTEGER REFERENCES organizations(id),
    is_active BOOLEAN DEFAULT true
);

CREATE INDEX idx_organizations_parent_id ON organizations(parent_id);

WITH RECURSIVE org_tree AS (
    SELECT id, name, parent_id, 1 as level
    FROM organizations 
    WHERE parent_id IS NULL AND is_active = true
    
    UNION ALL
    
    SELECT o.id, o.name, o.parent_id, ot.level + 1
    FROM organizations o
    INNER JOIN org_tree ot ON o.parent_id = ot.id
    WHERE o.is_active = true
)
SELECT * FROM org_tree;

-- 添加了覆盖索引
CREATE INDEX idx_organizations_parent_active ON organizations(parent_id, is_active);
CREATE INDEX idx_organizations_active_parent ON organizations(is_active, parent_id);

-- 分析查询计划
EXPLAIN ANALYZE WITH RECURSIVE org_tree AS (
    -- 原有查询语句
);

-- 添加路径字段
ALTER TABLE organizations ADD COLUMN path VARCHAR(1000);

-- 更新路径数据（一次性操作）
WITH RECURSIVE org_path AS (
    SELECT id, name, parent_id, CAST(id AS VARCHAR(1000)) as path
    FROM organizations 
    WHERE parent_id IS NULL
    
    UNION ALL
    
    SELECT o.id, o.name, o.parent_id, CAST(op.path || '/' || o.id AS VARCHAR(1000))
    FROM organizations o
    INNER JOIN org_path op ON o.parent_id = op.id
)
UPDATE organizations o
SET path = op.path
FROM org_path op
WHERE o.id = op.id;

-- 创建路径索引
CREATE INDEX idx_organizations_path ON organizations(path);

SELECT id, name, parent_id, 
       (LENGTH(path) - LENGTH(REPLACE(path, '/', ''))) + 1 as level
FROM organizations
WHERE is_active = true
ORDER BY path;

CREATE OR REPLACE FUNCTION update_organization_path()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.parent_id IS NULL THEN
        NEW.path = CAST(NEW.id AS VARCHAR);
    ELSE
        SELECT CONCAT(path, '/', NEW.id) 
        INTO NEW.path
        FROM organizations 
        WHERE id = NEW.parent_id;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_organization_path
BEFORE INSERT ON organizations
FOR EACH ROW EXECUTE FUNCTION update_organization_path();