一、复杂查询技巧

1. 递归查询高级应用

-- 递归查询：组织架构树（带深度和路径）
WITH RECURSIVE org_tree AS (
    -- 基础查询：根节点（CEO）
    SELECT 
        id,
        name,
        manager_id,
        1 AS level,
        name AS path,
        ARRAY[id] AS path_ids,
        1 AS sort_order
    FROM employees
    WHERE manager_id IS NULL
  
    UNION ALL
  
    -- 递归查询：下属
    SELECT 
        e.id,
        e.name,
        e.manager_id,
        ot.level + 1,
        ot.path || ' > ' || e.name,
        ot.path_ids || e.id,
        ot.sort_order * 10 + ROW_NUMBER() OVER (PARTITION BY e.manager_id ORDER BY e.id)
    FROM employees e
    INNER JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT 
    id,
    name,
    level,
    path,
    repeat('  ', level - 1) || name AS tree_display
FROM org_tree
ORDER BY path_ids;

-- 递归查询：物料清单（BOM）展开
WITH RECURSIVE bom AS (
    -- 基础：顶层产品
    SELECT 
        product_id,
        component_id,
        quantity,
        1 AS level,
        quantity AS total_quantity
    FROM product_components
    WHERE product_id = 100
  
    UNION ALL
  
    -- 递归：展开子组件
    SELECT 
        pc.product_id,
        pc.component_id,
        pc.quantity,
        bom.level + 1,
        bom.total_quantity * pc.quantity
    FROM product_components pc
    INNER JOIN bom ON pc.product_id = bom.component_id
    WHERE bom.level < 10  -- 防止无限递归
)
SELECT 
    component_id,
    SUM(total_quantity) AS total_required
FROM bom
GROUP BY component_id
ORDER BY component_id;

-- 递归查询：查找所有下属（用于权限管理）
WITH RECURSIVE subordinates AS (
    SELECT id, name, manager_id, 0 AS depth
    FROM employees
    WHERE id = 123  -- 当前用户ID
  
    UNION ALL
  
    SELECT e.id, e.name, e.manager_id, s.depth + 1
    FROM employees e
    INNER JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates ORDER BY depth, id;

-- 递归查询：图遍历（社交网络关系）
WITH RECURSIVE friends AS (
    -- 起点用户
    SELECT 
        user_id,
        friend_id,
        1 AS degree,
        ARRAY[user_id, friend_id] AS path
    FROM friendships
    WHERE user_id = 1
  
    UNION ALL
  
    -- 二度、三度好友
    SELECT 
        f.friend_id,
        fs.friend_id,
        f.degree + 1,
        f.path || fs.friend_id
    FROM friends f
    INNER JOIN friendships fs ON fs.user_id = f.friend_id
    WHERE 
        fs.friend_id != ALL(f.path)  -- 避免循环
        AND f.degree < 5              -- 限制深度
)
SELECT DISTINCT 
    friend_id,
    MIN(degree) AS shortest_path_degree
FROM friends
WHERE user_id != friend_id
GROUP BY friend_id
ORDER BY shortest_path_degree;

2. 分组与聚合高级技巧

-- 分组集的高级应用：多维度数据分析
SELECT 
    COALESCE(region, 'ALL') AS region,
    COALESCE(product_category, 'ALL') AS category,
    COALESCE(sale_month::TEXT, 'ALL') AS month,
    COUNT(*) AS sales_count,
    SUM(amount) AS total_amount,
    AVG(amount) AS avg_amount,
    -- 计算占比
    ROUND(100.0 * SUM(amount) / SUM(SUM(amount)) OVER (), 2) AS percentage
FROM sales
GROUP BY GROUPING SETS (
    (region, product_category, sale_month),  -- 详细数据
    (region, product_category),               -- 按区域和类别
    (region, sale_month),                     -- 按区域和月份
    (product_category, sale_month),           -- 按类别和月份
    (region),                                 -- 按区域汇总
    (product_category),                       -- 按类别汇总
    (sale_month),                             -- 按月份汇总
    ()                                        -- 总计
)
HAVING GROUPING(sale_month) = 0 OR sale_month IS NOT NULL
ORDER BY region, category, month;

-- ROLLUP 用于层次报告（如组织结构）
SELECT 
    department,
    sub_department,
    team,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary,
    GROUPING(department) AS is_dept_total,
    GROUPING(sub_department) AS is_sub_dept_total,
    GROUPING(team) AS is_team_total
FROM employees
GROUP BY ROLLUP (department, sub_department, team)
ORDER BY department NULLS LAST, sub_department NULLS LAST, team NULLS LAST;

-- CUBE 用于交叉表分析
SELECT 
    product,
    region,
    quarter,
    SUM(sales) AS total_sales,
    -- 计算小计占比
    ROUND(100.0 * SUM(sales) / SUM(SUM(sales)) OVER (PARTITION BY product), 2) AS product_share
FROM sales_data
GROUP BY CUBE (product, region, quarter);

3. 窗口函数高级应用

-- 移动窗口和滑动平均
SELECT 
    date,
    sales,
    -- 7天移动平均
    AVG(sales) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma_7d,
    -- 30天移动平均
    AVG(sales) OVER (ORDER BY date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS ma_30d,
    -- 累计总和
    SUM(sales) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative,
    -- 相对于上周的增长
    sales - LAG(sales, 7) OVER (ORDER BY date) AS weekly_growth,
    -- 百分比变化
    ROUND(100.0 * (sales - LAG(sales, 7) OVER (ORDER BY date)) / 
          NULLIF(LAG(sales, 7) OVER (ORDER BY date), 0), 2) AS weekly_growth_pct
FROM daily_sales
WHERE date >= CURRENT_DATE - INTERVAL '90 days';

-- 窗口函数与条件聚合
SELECT 
    user_id,
    date,
    amount,
    -- 最近3笔交易的平均值
    AVG(amount) OVER (PARTITION BY user_id ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS recent_avg,
    -- 当前行相对于历史最高点的比例
    ROUND(100.0 * amount / MAX(amount) OVER (PARTITION BY user_id), 2) AS pct_of_max,
    -- 相对于前一笔的变化趋势
    CASE 
        WHEN amount > LAG(amount) OVER (PARTITION BY user_id ORDER BY date) THEN 'UP'
        WHEN amount < LAG(amount) OVER (PARTITION BY user_id ORDER BY date) THEN 'DOWN'
        ELSE 'SAME'
    END AS trend,
    -- 会话分组（连续访问）
    SUM(CASE WHEN gap > INTERVAL '30 minutes' THEN 1 ELSE 0 END) 
        OVER (PARTITION BY user_id ORDER BY date) AS session_id
FROM (
    SELECT 
        user_id,
        date,
        amount,
        date - LAG(date) OVER (PARTITION BY user_id ORDER BY date) AS gap
    FROM user_activities
) t;

-- 排名函数的各种变体
SELECT 
    product_name,
    category,
    sales,
    -- 全局排名
    RANK() OVER w AS global_rank,
    -- 分类内排名
    RANK() OVER (PARTITION BY category ORDER BY sales DESC) AS category_rank,
    -- 百分位排名
    PERCENT_RANK() OVER w AS percent_rank,
    -- 累积分布
    CUME_DIST() OVER w AS cumulative_dist,
    -- 分位数（四分位、十分位）
    NTILE(4) OVER w AS quartile,
    NTILE(10) OVER w AS decile
FROM products
WINDOW w AS (ORDER BY sales DESC);

4. 数据透视表（Crosstab）

-- 安装 tablefunc 扩展
CREATE EXTENSION IF NOT EXISTS tablefunc;

-- 基本交叉表：月份为列，产品为行
SELECT * FROM crosstab(
    'SELECT product_name, EXTRACT(MONTH FROM sale_date) AS month, SUM(amount) 
     FROM sales 
     WHERE EXTRACT(YEAR FROM sale_date) = 2024
     GROUP BY product_name, month 
     ORDER BY product_name, month',
    'SELECT generate_series(1, 12)'
) AS ct(product_name TEXT, jan NUMERIC, feb NUMERIC, mar NUMERIC, apr NUMERIC,
        may NUMERIC, jun NUMERIC, jul NUMERIC, aug NUMERIC, sep NUMERIC, 
        oct NUMERIC, nov NUMERIC, dec NUMERIC);

-- 动态交叉表（使用函数）
CREATE OR REPLACE FUNCTION dynamic_crosstab(
    table_name TEXT,
    row_column TEXT,
    col_column TEXT,
    value_column TEXT
) RETURNS TEXT AS $$
DECLARE
    columns TEXT;
    result TEXT;
BEGIN
    -- 动态获取列名
    SELECT string_agg(DISTINCT format('%I TEXT', col_column), ', ')
    INTO columns
    FROM execute_format('SELECT DISTINCT %I FROM %I ORDER BY 1', col_column, table_name);
  
    -- 构建查询
    result := format('
        SELECT * FROM crosstab(
            ''SELECT %I, %I, %I FROM %I ORDER BY 1,2'',
            ''SELECT DISTINCT %I FROM %I ORDER BY 1''
        ) AS ct(%I, %s)',
        row_column, col_column, value_column, table_name,
        col_column, table_name, row_column, columns
    );
  
    RETURN result;
END;
$$ LANGUAGE plpgsql;

二、JSON 数据分析

1. JSON/JSONB 查询

-- 创建示例表
CREATE TABLE orders_json (
    id SERIAL PRIMARY KEY,
    order_data JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入 JSON 数据
INSERT INTO orders_json (order_data) VALUES
('{
    "order_id": "ORD-001",
    "customer": {
        "id": 1,
        "name": "Alice Chen",
        "email": "alice@example.com"
    },
    "items": [
        {"product_id": 101, "name": "Laptop", "quantity": 1, "price": 999.99},
        {"product_id": 102, "name": "Mouse", "quantity": 2, "price": 29.99}
    ],
    "total": 1059.97,
    "status": "completed"
}');

-- JSON 基本操作符
SELECT 
    order_data->>'order_id' AS order_id,           -- 获取为文本
    order_data->'customer'->>'name' AS customer_name,  -- 嵌套访问
    order_data->'items'->0->>'product_id' AS first_product,  -- 数组索引
    order_data @> '{"status": "completed"}' AS is_completed,  -- 包含检查
    order_data ? 'discount' AS has_discount        -- 键存在检查
FROM orders_json;

-- JSONB 路径查询（JSONPath）
SELECT order_data FROM orders_json
WHERE order_data @? '$.items[*].price > 500';

-- 使用 jsonb_path_exists
SELECT order_data FROM orders_json
WHERE jsonb_path_exists(order_data, '$.items[*].price ? (@ > 500)');

-- 展开 JSON 数组（横向展开）
SELECT 
    order_id,
    item->>'product_id' AS product_id,
    item->>'name' AS product_name,
    (item->>'quantity')::INT AS quantity,
    (item->>'price')::DECIMAL AS price
FROM orders_json,
LATERAL jsonb_array_elements(order_data->'items') AS item;

-- JSON 聚合
SELECT 
    order_data->'customer'->>'id' AS customer_id,
    jsonb_agg(order_data->'order_id') AS orders,
    jsonb_object_agg(
        order_data->>'order_id', 
        order_data->'total'
    ) AS order_totals
FROM orders_json
GROUP BY customer_id;

2. JSON/JSONB 索引

-- GIN 索引（支持 @>、?、?&、?| 操作符）
CREATE INDEX idx_orders_jsonb ON orders_json USING gin(order_data);

-- JSONB 路径索引
CREATE INDEX idx_orders_status ON orders_json USING gin (
    (order_data->'status')
);

-- 表达式索引
CREATE INDEX idx_orders_customer_email ON orders_json (
    (order_data->'customer'->>'email')
);

-- 使用索引优化查询
SELECT * FROM orders_json 
WHERE order_data @> '{"status": "completed"}';

-- JSONB 数据类型特有操作
UPDATE orders_json 
SET order_data = jsonb_set(
    order_data, 
    '{status}', 
    '"shipped"'
)
WHERE order_data->>'order_id' = 'ORD-001';

-- 删除 JSON 键
UPDATE orders_json 
SET order_data = order_data - 'discount'
WHERE order_data ? 'discount';

-- JSONB 合并
UPDATE orders_json 
SET order_data = order_data || '{"priority": "high", "tags": ["urgent"]}'
WHERE order_data->>'total' > '1000';

3. JSON 与关系型数据转换

-- 行转 JSON
SELECT row_to_json(u) FROM users u WHERE id = 1;

-- 聚合为 JSON 数组
SELECT 
    department_id,
    json_agg(json_build_object('id', id, 'name', name, 'salary', salary)) AS employees
FROM employees
GROUP BY department_id;

-- 构建嵌套 JSON
SELECT 
    json_build_object(
        'order_id', o.id,
        'customer', json_build_object('id', c.id, 'name', c.name),
        'items', (
            SELECT json_agg(json_build_object('product', p.name, 'quantity', oi.quantity))
            FROM order_items oi
            JOIN products p ON p.id = oi.product_id
            WHERE oi.order_id = o.id
        )
    )
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.id = 100;

-- JSON 转表
SELECT * FROM jsonb_to_record('{
    "name": "John Doe",
    "age": 30,
    "address": {"city": "Beijing", "street": "Main St"}
}') AS x(name TEXT, age INT, address JSONB);

三、全文搜索

1. 全文搜索基础

-- 创建文档向量列
ALTER TABLE articles ADD COLUMN document tsvector;
UPDATE articles SET document = 
    setweight(to_tsvector('english', title), 'A') ||
    setweight(to_tsvector('english', content), 'B');

-- 创建 GIN 索引
CREATE INDEX idx_articles_document ON articles USING gin(document);

-- 基础搜索
SELECT title, content 
FROM articles 
WHERE document @@ to_tsquery('english', 'database & performance');

-- 搜索并排名
SELECT 
    title,
    ts_rank(document, query) AS relevance
FROM articles, 
     to_tsquery('english', 'PostgreSQL | MySQL') AS query
WHERE document @@ query
ORDER BY relevance DESC
LIMIT 10;

-- 高亮显示
SELECT 
    title,
    ts_headline(content, query, 'StartSel=<mark>, StopSel=</mark>') AS highlighted
FROM articles,
     to_tsquery('english', 'index & query') AS query
WHERE document @@ query;

-- 使用 websearch 风格搜索（Google 风格）
SELECT * FROM articles
WHERE document @@ websearch_to_tsquery('english', 'PostgreSQL "performance tuning" -MySQL');

-- 短语搜索
SELECT * FROM articles
WHERE document @@ phraseto_tsquery('english', 'database management system');

2. 中文全文搜索

-- 安装中文分词扩展
CREATE EXTENSION IF NOT EXISTS zhparser;

-- 创建中文全文搜索配置
CREATE TEXT SEARCH CONFIGURATION chinese (PARSER = zhparser);
ALTER TEXT SEARCH CONFIGURATION chinese 
    ADD MAPPING FOR n,v,a,i,e,l WITH simple;  -- 名词、动词等

-- 使用中文搜索
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title TEXT,
    content TEXT,
    document tsvector GENERATED ALWAYS AS (
        setweight(to_tsvector('chinese', COALESCE(title, '')), 'A') ||
        setweight(to_tsvector('chinese', COALESCE(content, '')), 'B')
    ) STORED
);

CREATE INDEX idx_documents_search ON documents USING gin(document);

-- 中文搜索查询
SELECT * FROM documents
WHERE document @@ to_tsquery('chinese', '数据库 & 性能');

-- 词频分析
SELECT 
    word,
    ndoc,
    nentry
FROM ts_stat('SELECT document FROM documents')
ORDER BY nentry DESC
LIMIT 20;

四、时序数据分析

-- 生成时间序列数据
WITH time_series AS (
    SELECT generate_series(
        '2024-01-01'::TIMESTAMP,
        '2024-12-31'::TIMESTAMP,
        '1 hour'::INTERVAL
    ) AS ts
)
SELECT 
    ts,
    EXTRACT(HOUR FROM ts) AS hour,
    EXTRACT(DOW FROM ts) AS day_of_week,
    -- 模拟数据
    random() * 100 AS metric
FROM time_series;

-- 时间间隔聚合（按小时、天、周、月）
SELECT 
    date_trunc('hour', created_at) AS hour,
    COUNT(*) AS count,
    AVG(value) AS avg_value
FROM metrics
WHERE created_at > NOW() - INTERVAL '7 days'
GROUP BY hour
ORDER BY hour;

-- 滚动窗口统计
SELECT 
    created_at,
    value,
    AVG(value) OVER (ORDER BY created_at RANGE BETWEEN INTERVAL '1 hour' PRECEDING AND CURRENT ROW) AS rolling_1h_avg,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY value) 
        OVER (ORDER BY created_at RANGE BETWEEN INTERVAL '24 hours' PRECEDING AND CURRENT ROW) AS p95_24h
FROM metrics
WHERE metric_name = 'cpu_usage';

-- 时间序列补全（填充缺失值）
WITH time_range AS (
    SELECT generate_series(
        (SELECT MIN(created_at) FROM metrics),
        (SELECT MAX(created_at) FROM metrics),
        '1 hour'::INTERVAL
    ) AS ts
),
full_data AS (
    SELECT 
        tr.ts,
        m.value
    FROM time_range tr
    LEFT JOIN metrics m ON date_trunc('hour', m.created_at) = tr.ts
)
SELECT 
    ts,
    value,
    COALESCE(value, LAG(value) OVER (ORDER BY ts)) AS filled_forward,
    AVG(value) OVER (ORDER BY ts ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS smoothed
FROM full_data;

五、地理空间查询（PostGIS）

-- 启用 PostGIS 扩展
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS postgis_topology;

-- 创建地理数据表
CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    geom GEOMETRY(Point, 4326),  -- WGS84 坐标系
    area GEOMETRY(Polygon, 4326),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建空间索引
CREATE INDEX idx_locations_geom ON locations USING gist(geom);

-- 插入点数据
INSERT INTO locations (name, geom) VALUES
    ('Beijing', ST_SetSRID(ST_MakePoint(116.4074, 39.9042), 4326)),
    ('Shanghai', ST_SetSRID(ST_MakePoint(121.4737, 31.2304), 4326)),
    ('Guangzhou', ST_SetSRID(ST_MakePoint(113.2644, 23.1291), 4326));

-- 距离查询（找到100公里内的地点）
SELECT 
    l1.name AS from_location,
    l2.name AS to_location,
    ST_Distance(
        ST_Transform(l1.geom, 3857),  -- 转换为米制投影
        ST_Transform(l2.geom, 3857)
    ) AS distance_meters
FROM locations l1, locations l2
WHERE l1.id = 1 
  AND l1.id != l2.id
  AND ST_DWithin(
        ST_Transform(l1.geom, 3857),
        ST_Transform(l2.geom, 3857),
        100000  -- 100公里
      );

-- 面积计算
SELECT 
    name,
    ST_Area(ST_Transform(geom, 3857)) / 1000000 AS area_sqkm
FROM locations
WHERE geom IS NOT NULL;

-- 点是否在多边形内
SELECT name
FROM locations
WHERE ST_Within(
    ST_SetSRID(ST_MakePoint(116.4074, 39.9042), 4326),
    area
);

-- 创建缓冲区（5公里范围）
SELECT 
    name,
    ST_Buffer(ST_Transform(geom, 3857), 5000) AS buffer_5km
FROM locations;

-- 路径规划（使用 pgRouting）
CREATE EXTENSION IF NOT EXISTS pgrouting;

-- 最短路径计算
SELECT * FROM pgr_dijkstra(
    'SELECT id, source, target, cost FROM roads',
    1,  -- 起点ID
    10, -- 终点ID
    directed := true
);

六、数据分析函数

1. 统计函数

-- 基础统计分析
SELECT 
    COUNT(*) AS n,
    MIN(value) AS min_value,
    MAX(value) AS max_value,
    AVG(value) AS mean,
    STDDEV(value) AS stddev,
    VARIANCE(value) AS variance,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value) AS median,
    MODE() WITHIN GROUP (ORDER BY value) AS mode
FROM measurements;

-- 相关系数计算
SELECT 
    corr(temperature, sales) AS temp_sales_correlation,
    corr(advertising, sales) AS ad_sales_correlation
FROM daily_sales_data;

-- 线性回归
SELECT 
    regr_slope(temperature, sales) AS slope,
    regr_intercept(temperature, sales) AS intercept,
    regr_r2(temperature, sales) AS r_squared
FROM daily_sales_data;

-- 移动平均和指数平滑
WITH sales_data AS (
    SELECT 
        date,
        sales,
        AVG(sales) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS sma_7d,
        -- 指数平滑（α=0.3）
        FIRST_VALUE(sales) OVER (ORDER BY date) AS initial_smooth,
        AVG(sales) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_avg
    FROM daily_sales
)
SELECT 
    date,
    sales,
    sma_7d,
    cumulative_avg
FROM sales_data;

2. 异常检测

-- Z-Score 异常检测
WITH stats AS (
    SELECT 
        AVG(value) AS mean,
        STDDEV(value) AS stddev
    FROM metrics
),
z_scores AS (
    SELECT 
        m.*,
        (m.value - s.mean) / NULLIF(s.stddev, 0) AS z_score
    FROM metrics m, stats s
)
SELECT * FROM z_scores
WHERE ABS(z_score) > 3  -- 3σ 原则

-- IQR 异常检测
WITH quartiles AS (
    SELECT 
        PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY value) AS q1,
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY value) AS q3
    FROM metrics
),
iqr_stats AS (
    SELECT 
        q1,
        q3,
        q3 - q1 AS iqr,
        q1 - 1.5 * (q3 - q1) AS lower_bound,
        q3 + 1.5 * (q3 - q1) AS upper_bound
    FROM quartiles
)
SELECT m.*
FROM metrics m, iqr_stats i
WHERE m.value < i.lower_bound OR m.value > i.upper_bound;

3. 漏斗分析

-- 用户转化漏斗
WITH funnel AS (
    SELECT 
        'visit' AS step,
        COUNT(DISTINCT user_id) AS users,
        COUNT(*) AS events
    FROM page_views
    WHERE page = 'home'
  
    UNION ALL
  
    SELECT 
        'signup' AS step,
        COUNT(DISTINCT user_id),
        COUNT(*)
    FROM user_actions
    WHERE action = 'signup'
  
    UNION ALL
  
    SELECT 
        'first_purchase' AS step,
        COUNT(DISTINCT user_id),
        COUNT(*)
    FROM orders
    WHERE order_number = 1
),
funnel_rates AS (
    SELECT 
        step,
        users,
        events,
        LAG(users) OVER (ORDER BY step) AS prev_users,
        ROUND(100.0 * users / LAG(users) OVER (ORDER BY step), 2) AS conversion_rate,
        ROUND(100.0 * users / FIRST_VALUE(users) OVER (ORDER BY step), 2) AS overall_rate
    FROM funnel
)
SELECT * FROM funnel_rates;

七、查询优化与调试

1. EXPLAIN 深入分析

-- 基础执行计划
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

-- 详细分析（实际执行）
EXPLAIN (ANALYZE, BUFFERS, TIMING, VERBOSE, COSTS)
SELECT u.*, o.total_amount
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.status = 'active'
  AND o.created_at > '2024-01-01'
ORDER BY o.total_amount DESC
LIMIT 10;

-- JSON 格式输出（便于解析）
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM large_table WHERE status = 'pending';

-- 查看实际执行统计
-- Seq Scan: 顺序扫描成本
-- Index Scan: 索引扫描
-- Bitmap Heap Scan: 位图扫描
-- Nested Loop: 嵌套循环连接
-- Hash Join: 哈希连接
-- Merge Join: 归并连接

2. 查询性能调优工具

-- 启用 pg_stat_statements
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 查看最慢的查询
SELECT 
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    rows,
    shared_blks_hit,
    shared_blks_read
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

-- 重置统计信息
SELECT pg_stat_statements_reset();

-- 查看表扫描统计
SELECT 
    schemaname,
    tablename,
    seq_scan,           -- 顺序扫描次数
    seq_tup_read,       -- 顺序扫描读取行数
    idx_scan,           -- 索引扫描次数
    idx_tup_fetch,      -- 索引扫描获取行数
    n_live_tup,         -- 存活行数
    n_dead_tup          -- 死行数
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 10;

-- 自动分析表
ANALYZE VERBOSE users;

-- 查看表统计信息
SELECT 
    attname,
    n_distinct,
    correlation,
    most_common_vals,
    most_common_freqs
FROM pg_stats
WHERE tablename = 'users';