一、JSONB 高级应用

1. JSONB 索引策略

-- 创建测试表
CREATE TABLE product_catalog (
    id SERIAL PRIMARY KEY,
    product_data JSONB NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入百万级测试数据
INSERT INTO product_catalog (product_data)
SELECT 
    jsonb_build_object(
        'id', g,
        'name', 'Product ' || g,
        'sku', 'SKU-' || LPAD(g::TEXT, 8, '0'),
        'price', (random() * 1000)::NUMERIC(10,2),
        'category', (ARRAY['Electronics', 'Clothing', 'Books', 'Home', 'Sports'])[floor(random() * 5) + 1],
        'brand', (ARRAY['Apple', 'Samsung', 'Nike', 'Sony', 'LG'])[floor(random() * 5) + 1],
        'stock', floor(random() * 1000)::INT,
        'tags', (ARRAY['new', 'sale', 'bestseller', 'clearance'])[floor(random() * 4) + 1],
        'specs', jsonb_build_object(
            'weight', (random() * 10)::NUMERIC(10,2),
            'dimensions', jsonb_build_object('width', random() * 100, 'height', random() * 100),
            'color', (ARRAY['Red', 'Blue', 'Black', 'White'])[floor(random() * 4) + 1]
        ),
        'reviews', (
            SELECT jsonb_agg(
                jsonb_build_object(
                    'user', 'user' || floor(random() * 1000),
                    'rating', floor(random() * 5) + 1,
                    'comment', 'Review comment ' || floor(random() * 100)
                )
            )
            FROM generate_series(1, floor(random() * 20)::INT)
        )
    )
FROM generate_series(1, 1000000) g;

-- 不同索引策略对比

-- 1. 默认 GIN 索引（支持所有 JSONB 操作符）
CREATE INDEX idx_product_gin ON product_catalog USING gin(product_data);

-- 2. 指定操作的 GIN 索引（更小更快）
CREATE INDEX idx_product_gin_ops ON product_catalog USING gin(product_data jsonb_path_ops);

-- 3. 针对特定字段的索引
CREATE INDEX idx_product_category ON product_catalog ((product_data->>'category'));
CREATE INDEX idx_product_price ON product_catalog ((product_data->>'price'));

-- 4. 表达式索引（用于类型转换）
CREATE INDEX idx_product_price_numeric ON product_catalog (((product_data->>'price')::NUMERIC));

-- 5. 针对嵌套对象的索引
CREATE INDEX idx_product_specs_color ON product_catalog ((product_data->'specs'->>'color'));

-- 6. 部分索引（只索引活跃产品）
CREATE INDEX idx_active_products ON product_catalog 
    USING gin(product_data) 
    WHERE product_data->>'stock' > '0';

-- 索引性能测试
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM product_catalog 
WHERE product_data @> '{"category": "Electronics"}';

EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM product_catalog 
WHERE product_data->>'price' > '500' 
  AND product_data->>'category' = 'Electronics';

2. JSONB 高级查询模式

-- 路径查询和 JSONPath
SELECT 
    product_data,
    jsonb_path_query(product_data, '$.specs.dimensions') AS dimensions,
    jsonb_path_exists(product_data, '$.reviews[*].rating ? (@ >= 4)') AS has_good_reviews
FROM product_catalog;

-- 条件聚合
SELECT 
    product_data->>'category' AS category,
    AVG((product_data->>'price')::NUMERIC) AS avg_price,
    SUM((product_data->>'stock')::INT) AS total_stock,
    COUNT(*) FILTER (WHERE product_data ? 'reviews') AS has_reviews,
    jsonb_agg(DISTINCT product_data->>'brand') AS brands
FROM product_catalog
WHERE product_data->>'price' IS NOT NULL
GROUP BY product_data->>'category'
ORDER BY avg_price DESC;

-- 复杂嵌套查询
WITH review_stats AS (
    SELECT 
        id,
        product_data->>'name' AS product_name,
        AVG((review->>'rating')::INT) AS avg_rating,
        COUNT(*) AS review_count,
        jsonb_agg(review->>'comment') AS all_comments
    FROM product_catalog,
    LATERAL jsonb_array_elements(product_data->'reviews') AS review
    GROUP BY id, product_name
)
SELECT 
    product_name,
    avg_rating,
    review_count,
    all_comments
FROM review_stats
WHERE avg_rating > 4.5 AND review_count > 10
ORDER BY avg_rating DESC
LIMIT 20;

-- 动态键值查询
SELECT 
    product_data,
    jsonb_each(product_data->'specs') AS spec_items,
    jsonb_each_text(product_data) AS all_fields
FROM product_catalog
WHERE product_data->>'id' = '100';

-- 递归 JSONB 展开
WITH RECURSIVE json_keys AS (
    SELECT 
        id,
        key,
        value,
        key AS path
    FROM product_catalog,
    LATERAL jsonb_each(product_data)
    WHERE jsonb_typeof(product_data) = 'object'
  
    UNION ALL
  
    SELECT 
        jk.id,
        elem.key,
        elem.value,
        jk.path || '.' || elem.key
    FROM json_keys jk,
    LATERAL jsonb_each(jk.value) elem
    WHERE jsonb_typeof(jk.value) = 'object'
)
SELECT DISTINCT path FROM json_keys ORDER BY path;

3. JSONB 更新与合并策略

-- 批量更新 JSONB 字段
-- 为所有电子产品添加保修信息
UPDATE product_catalog 
SET product_data = jsonb_set(
    product_data, 
    '{warranty}', 
    '"2 years"'
)
WHERE product_data->>'category' = 'Electronics';

-- 数组追加元素
UPDATE product_catalog 
SET product_data = jsonb_set(
    product_data,
    '{tags}',
    product_data->'tags' || '"new_arrival"'
)
WHERE product_data->>'category' = 'Books';

-- 条件更新（使用 CASE）
UPDATE product_catalog 
SET product_data = CASE 
    WHEN (product_data->>'price')::NUMERIC < 100 THEN
        product_data || jsonb_build_object('discount', 10, 'is_on_sale', true)
    WHEN (product_data->>'price')::NUMERIC < 500 THEN
        product_data || jsonb_build_object('discount', 5, 'is_on_sale', true)
    ELSE
        product_data - 'discount' - 'is_on_sale'
END
WHERE product_data->>'stock' > '0';

-- 使用 jsonb_patch 进行深度合并
CREATE OR REPLACE FUNCTION jsonb_deep_merge(a JSONB, b JSONB)
RETURNS JSONB AS $$
SELECT 
    jsonb_object_agg(
        COALESCE(ka, kb),
        CASE 
            WHEN ka IS NULL THEN a->kb
            WHEN kb IS NULL THEN a->ka
            WHEN jsonb_typeof(a->ka) = 'object' AND jsonb_typeof(b->kb) = 'object' 
                THEN jsonb_deep_merge(a->ka, b->kb)
            ELSE b->kb
        END
    )
FROM (SELECT jsonb_object_keys(a) AS ka) ka
FULL JOIN (SELECT jsonb_object_keys(b) AS kb) kb ON ka = kb;
$$ LANGUAGE SQL STRICT IMMUTABLE;

-- 使用深度合并更新
UPDATE product_catalog 
SET product_data = jsonb_deep_merge(
    product_data,
    '{"specs": {"grade": "A+", "warranty": {"months": 24, "type": "premium"}}}'
)
WHERE product_data->>'brand' = 'Apple';

二、数组类型高级应用

1. 数组作为标签系统

-- 创建标签系统
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT,
    content TEXT,
    tags TEXT[],
    categories TEXT[],
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建 GIN 索引加速数组查询
CREATE INDEX idx_articles_tags ON articles USING gin(tags);
CREATE INDEX idx_articles_categories ON articles USING gin(categories);

-- 插入带标签的数据
INSERT INTO articles (title, content, tags, categories) VALUES
    ('PostgreSQL Arrays Tutorial', 'Content about arrays...', 
     ARRAY['postgresql', 'arrays', 'tutorial'], ARRAY['database', 'sql']),
    ('Advanced JSONB in PG', 'Learn JSONB...', 
     ARRAY['postgresql', 'jsonb', 'advanced'], ARRAY['database', 'nosql']),
    ('Performance Tuning Tips', 'How to optimize...', 
     ARRAY['performance', 'tuning', 'postgresql'], ARRAY['dba', 'optimization']);

-- 高效标签查询
-- 查找包含任意指定标签的文章
SELECT * FROM articles 
WHERE tags && ARRAY['postgresql', 'performance'];

-- 查找包含所有指定标签的文章
SELECT * FROM articles 
WHERE tags @> ARRAY['postgresql', 'tutorial'];

-- 标签统计（最受欢迎标签）
SELECT 
    tag,
    COUNT(*) AS article_count,
    ARRAY_AGG(DISTINCT title) AS articles
FROM articles,
LATERAL unnest(tags) AS tag
GROUP BY tag
ORDER BY article_count DESC;

-- 协同过滤：查找相似文章
WITH article_tags AS (
    SELECT tags FROM articles WHERE id = 1
),
similar_articles AS (
    SELECT 
        a.id,
        a.title,
        a.tags,
        array_length(ARRAY(
            SELECT unnest(a.tags) 
            INTERSECT 
            SELECT unnest(at.tags)
        ), 1) AS common_tags_count
    FROM articles a, article_tags at
    WHERE a.id != 1
)
SELECT * FROM similar_articles 
WHERE common_tags_count > 0 
ORDER BY common_tags_count DESC, array_length(tags, 1) ASC
LIMIT 10;

2. 数组作为时间序列聚合器

-- 使用数组存储时序数据
CREATE TABLE sensor_data (
    sensor_id INTEGER,
    date DATE,
    hourly_readings NUMERIC[24],           -- 24小时数据数组
    daily_stats NUMERIC[8],                 -- 最大、最小、平均等
    anomalies BOOLEAN[],
    PRIMARY KEY (sensor_id, date)
);

-- 插入小时级数据
INSERT INTO sensor_data (sensor_id, date, hourly_readings)
VALUES (
    1, 
    CURRENT_DATE,
    ARRAY(
        SELECT (random() * 100)::NUMERIC 
        FROM generate_series(1, 24)
    )
);

-- 更新特定小时的数据
UPDATE sensor_data 
SET hourly_readings[12] = 85.5
WHERE sensor_id = 1 AND date = CURRENT_DATE;

-- 计算每日统计
UPDATE sensor_data 
SET daily_stats = ARRAY[
    (SELECT MIN(v) FROM unnest(hourly_readings) v),
    (SELECT MAX(v) FROM unnest(hourly_readings) v),
    (SELECT AVG(v) FROM unnest(hourly_readings) v),
    (SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY v) 
     FROM unnest(hourly_readings) v),
    (SELECT STDDEV(v) FROM unnest(hourly_readings) v)
];

-- 检测异常值（超出3倍标准差）
UPDATE sensor_data 
SET anomalies = ARRAY(
    SELECT 
        v > (daily_stats[2] + 3 * daily_stats[5]) 
        OR v < (daily_stats[1] - 3 * daily_stats[5])
    FROM unnest(hourly_readings) v
);

-- 滑动窗口统计
SELECT 
    sensor_id,
    date,
    hourly_readings[1:8] AS morning_readings,
    hourly_readings[9:17] AS day_readings,
    hourly_readings[18:24] AS night_readings
FROM sensor_data
WHERE date >= CURRENT_DATE - INTERVAL '7 days';

-- 生成报告
SELECT 
    sensor_id,
    date,
    unnest(hourly_readings) AS hour_value,
    generate_series(1, 24) AS hour
FROM sensor_data
CROSS JOIN LATERAL unnest(hourly_readings) WITH ORDINALITY AS t(value, idx)
WHERE date = CURRENT_DATE;

三、范围类型应用

1. 会议室预订系统

-- 启用范围索引扩展
CREATE EXTENSION IF NOT EXISTS btree_gist;

-- 会议室预定表
CREATE TABLE meeting_room_reservations (
    id SERIAL PRIMARY KEY,
    room_id INTEGER NOT NULL,
    booker TEXT NOT NULL,
    meeting_time TSTZRANGE NOT NULL,
    participants TEXT[],
    metadata JSONB,
    -- 防止同一房间时间重叠
    EXCLUDE USING gist (room_id WITH =, meeting_time WITH &&)
);

-- 插入预定数据
INSERT INTO meeting_room_reservations (room_id, booker, meeting_time, participants)
VALUES 
    (101, 'Alice', '[2024-06-15 09:00, 2024-06-15 11:00)', ARRAY['Bob', 'Charlie']),
    (101, 'David', '[2024-06-15 11:30, 2024-06-15 13:00)', ARRAY['Eve']);

-- 冲突检查
WITH check_reservation AS (
    SELECT room_id, tstzrange('2024-06-15 10:00', '2024-06-15 12:00') AS new_range
)
SELECT 
    r.room_id,
    r.meeting_time,
    r.booker,
    r.meeting_time && c.new_range AS overlaps
FROM meeting_room_reservations r, check_reservation c
WHERE r.room_id = c.room_id 
  AND r.meeting_time && c.new_range;

-- 查找可用时间段
WITH RECURSIVE available_slots AS (
    SELECT 
        room_id,
        LOWER(meeting_time) AS start_time,
        UPPER(meeting_time) AS end_time
    FROM meeting_room_reservations
    WHERE room_id = 101 AND meeting_time && tstzrange('2024-06-15 08:00', '2024-06-15 20:00')
    ORDER BY start_time
  
    UNION ALL
  
    SELECT 
        room_id,
        end_time,
        LEAD(start_time) OVER (ORDER BY start_time)
    FROM meeting_room_reservations
    WHERE room_id = 101
)
SELECT 
    room_id,
    start_time AS available_start,
    LEAD(start_time) OVER (ORDER BY start_time) AS available_end
FROM available_slots;

2. 价格区间策略

-- 价格区间策略表
CREATE TABLE price_strategies (
    id SERIAL PRIMARY KEY,
    product_type TEXT,
    price_range NUMRANGE,
    discount_rate NUMERIC(4,2),
    strategy_name TEXT,
    valid_period DATERANGE,
    EXCLUDE USING gist (product_type WITH =, price_range WITH &&)
);

-- 插入价格策略
INSERT INTO price_strategies (product_type, price_range, discount_rate, strategy_name) VALUES
    ('Electronics', '[0, 500)', 0, 'Standard'),
    ('Electronics', '[500, 1000)', 5, 'Silver'),
    ('Electronics', '[1000, 2000)', 10, 'Gold'),
    ('Electronics', '[2000, inf)', 15, 'Platinum');

-- 根据价格自动应用策略
CREATE OR REPLACE FUNCTION get_discount_rate(p_product_type TEXT, p_price NUMERIC)
RETURNS NUMERIC AS $$
DECLARE
    v_rate NUMERIC;
BEGIN
    SELECT discount_rate INTO v_rate
    FROM price_strategies
    WHERE product_type = p_product_type 
      AND p_price <@ price_range;
  
    RETURN COALESCE(v_rate, 0);
END;
$$ LANGUAGE plpgsql STABLE;

-- 查找价格区间重叠
SELECT 
    a.strategy_name AS strategy_1,
    b.strategy_name AS strategy_2,
    a.price_range * b.price_range AS overlap_range
FROM price_strategies a, price_strategies b
WHERE a.id < b.id 
  AND a.product_type = b.product_type
  AND a.price_range && b.price_range;

四、复合类型实际应用

-- 创建地址复合类型
CREATE TYPE address_type AS (
    street TEXT,
    city TEXT,
    state VARCHAR(2),
    zip_code VARCHAR(10),
    country VARCHAR(50),
    geo_location POINT
);

-- 创建联系人复合类型
CREATE TYPE contact_type AS (
    phone VARCHAR(20),
    email VARCHAR(255),
    alt_phone VARCHAR(20),
    preferred_contact_method VARCHAR(20)
);

-- 使用复合类型的客户表
CREATE TABLE customers_advanced (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200),
    billing_address address_type,
    shipping_address address_type,
    contact contact_type,
    preferences JSONB,
    metadata JSONB
);

-- 高效的复合类型插入
INSERT INTO customers_advanced (name, billing_address, shipping_address, contact)
VALUES (
    'Acme Corporation',
    ROW('123 Business St', 'New York', 'NY', '10001', 'USA', point(40.7128, -74.0060))::address_type,
    ROW('456 Warehouse Ave', 'Newark', 'NJ', '07101', 'USA', point(40.7357, -74.1724))::address_type,
    ROW('+1-212-555-0123', 'contact@acme.com', '+1-212-555-0124', 'email')::contact_type
);

-- 查询复合类型字段
SELECT 
    name,
    (billing_address).city,
    (billing_address).zip_code,
    (contact).email,
    (contact).preferred_contact_method
FROM customers_advanced
WHERE (billing_address).state = 'NY'
  AND (contact).email IS NOT NULL;

-- 更新复合类型字段
UPDATE customers_advanced 
SET shipping_address.state = 'NJ'  -- 注意括号
WHERE name = 'Acme Corporation';

-- 使用复合类型函数
CREATE OR REPLACE FUNCTION format_address(addr address_type)
RETURNS TEXT AS $$
BEGIN
    RETURN format('%s, %s, %s %s, %s', 
                  addr.street, addr.city, addr.state, addr.zip_code, addr.country);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

SELECT 
    name,
    format_address(billing_address) AS formatted_billing,
    format_address(shipping_address) AS formatted_shipping
FROM customers_advanced;

五、全文搜索实战

1. 博客系统全文搜索

-- 创建博客表
CREATE TABLE blog_posts (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    author TEXT,
    tags TEXT[],
    published_at TIMESTAMP,
    search_vector TSVECTOR,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建全文搜索索引
CREATE INDEX idx_blog_search ON blog_posts USING gin(search_vector);
CREATE INDEX idx_blog_published ON blog_posts (published_at DESC);

-- 更新搜索向量的触发器
CREATE OR REPLACE FUNCTION blog_post_search_vector_update()
RETURNS TRIGGER AS $$
BEGIN
    NEW.search_vector :=
        setweight(to_tsvector('english', COALESCE(NEW.title, '')), 'A') ||
        setweight(to_tsvector('english', COALESCE(NEW.content, '')), 'B') ||
        setweight(to_tsvector('english', COALESCE(NEW.author, '')), 'C') ||
        setweight(to_tsvector('english', array_to_string(NEW.tags, ' ')), 'D');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_blog_search_update
    BEFORE INSERT OR UPDATE ON blog_posts
    FOR EACH ROW EXECUTE FUNCTION blog_post_search_vector_update();

-- 插入测试数据
INSERT INTO blog_posts (title, content, author, tags, published_at) VALUES
    ('Getting Started with PostgreSQL Full-Text Search', 
     'This article explains how to implement full-text search in PostgreSQL...',
     'John Doe', ARRAY['postgresql', 'search', 'tutorial'], NOW()),
    ('Advanced PostgreSQL Performance Tuning', 
     'Learn how to optimize your PostgreSQL database for better performance...',
     'Jane Smith', ARRAY['performance', 'tuning', 'postgresql'], NOW());

-- 基础全文搜索
SELECT 
    title,
    author,
    ts_rank(search_vector, query) AS relevance
FROM blog_posts, 
     to_tsquery('english', 'PostgreSQL & search') AS query
WHERE search_vector @@ query
ORDER BY relevance DESC;

-- 带高亮的搜索
SELECT 
    title,
    ts_headline(content, query, 'StartSel=***, StopSel=***, MaxWords=30, MinWords=15') AS highlighted_content
FROM blog_posts,
     phraseto_tsquery('english', 'database performance tuning') AS query
WHERE search_vector @@ query;

-- 搜索建议（模糊匹配）
SELECT 
    title,
    similarity(title, 'postgress')
FROM blog_posts
WHERE title % 'postgress'  -- 使用 pg_trgm 相似度
ORDER BY similarity(title, 'postgress') DESC;

-- 分类搜索统计
SELECT 
    tag,
    COUNT(*) AS post_count,
    AVG(ts_rank(search_vector, query)) AS avg_relevance
FROM blog_posts,
LATERAL unnest(tags) AS tag,
to_tsquery('english', 'performance') AS query
WHERE search_vector @@ query
GROUP BY tag
ORDER BY post_count DESC;

-- 多语言全文搜索
CREATE TEXT SEARCH CONFIGURATION multilingual (COPY = simple);
ALTER TEXT SEARCH CONFIGURATION multilingual 
    ALTER MAPPING FOR asciiword, asciihword, hword_asciipart
    WITH english_stem, french_stem, german_stem;

2. 实时搜索提示

-- 搜索日志表
CREATE TABLE search_log (
    id SERIAL PRIMARY KEY,
    search_term TEXT NOT NULL,
    search_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    result_count INTEGER,
    user_id INTEGER
);

-- 创建 trigram 索引用于模糊匹配
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_search_term_trigram ON search_log USING gin(search_term gin_trgm_ops);

-- 热门搜索建议
SELECT 
    search_term,
    COUNT(*) AS search_count,
    AVG(result_count) AS avg_results
FROM search_log
WHERE search_time > NOW() - INTERVAL '7 days'
  AND search_term % 'databae'  -- 模糊匹配
GROUP BY search_term
ORDER BY search_count DESC, similarity(search_term, 'databae') DESC
LIMIT 10;

-- 搜索自动完成
CREATE OR REPLACE FUNCTION search_autocomplete(prefix TEXT, limit_count INT DEFAULT 10)
RETURNS TABLE(term TEXT, frequency INT, similarity_score REAL) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        search_term,
        COUNT(*) AS frequency,
        MAX(similarity(search_term, prefix)) AS similarity_score
    FROM search_log
    WHERE search_term % prefix OR search_term ILIKE prefix || '%'
    GROUP BY search_term
    ORDER BY frequency DESC, similarity_score DESC
    LIMIT limit_count;
END;
$$ LANGUAGE plpgsql STABLE;

-- 使用搜索建议
SELECT * FROM search_autocomplete('postgre', 5);

六、数据类型性能优化

1. 存储优化

-- 数据类型存储大小对比
CREATE TABLE data_type_benchmark (
    id SERIAL PRIMARY KEY,
    int_value INTEGER,
    bigint_value BIGINT,
    numeric_value NUMERIC(20,4),
    text_value TEXT,
    char50 CHAR(50),
    varchar50 VARCHAR(50),
    json_value JSONB,
    json_pretty JSON,
    array_int INTEGER[],
    timestamp_value TIMESTAMP,
    uuid_value UUID,
    bytea_value BYTEA
);

-- 查看实际存储大小
SELECT 
    pg_size_pretty(pg_total_relation_size('data_type_benchmark')) AS total_size,
    pg_size_pretty(pg_indexes_size('data_type_benchmark')) AS index_size;

-- 表压缩（使用 pg_repack 或手动 VACUUM FULL）
VACUUM FULL data_type_benchmark;

-- TOAST 管理（大字段自动压缩）
ALTER TABLE large_text_table SET (toast_tuple_target = 8160);

2. 索引选择指南

-- 不同数据类型的推荐索引

-- 1. B-Tree（默认，适用于等值和范围查询）
CREATE INDEX idx_btree_name ON users(name);           -- TEXT
CREATE INDEX idx_btree_created ON orders(created_at); -- TIMESTAMP
CREATE INDEX idx_btree_price ON products(price);       -- NUMERIC

-- 2. GIN（JSONB、数组、全文搜索）
CREATE INDEX idx_gin_data ON json_table USING gin(json_data);
CREATE INDEX idx_gin_tags ON articles USING gin(tags);
CREATE INDEX idx_gin_search ON articles USING gin(search_vector);

-- 3. GiST（范围、几何、全文搜索）
CREATE INDEX idx_gist_range ON reservations USING gist(time_range);
CREATE INDEX idx_gist_location ON locations USING gist(geo_point);
CREATE INDEX idx_gist_tsvector ON articles USING gin(search_vector); -- GIN 更优

-- 4. BRIN（大表、自然排序的列）
CREATE INDEX idx_brin_created ON logs USING brin(created_at);
CREATE INDEX idx_brin_id ON huge_table USING brin(id);

-- 5. Hash（仅等值查询，不推荐）
CREATE INDEX idx_hash_phone ON users USING hash(phone);

-- 6. SP-GiST（点、线、多边形等空间数据）
CREATE INDEX idx_spgist_location ON locations USING spgist(geo_point);

3. 查询优化示例

-- 优化 JSONB 查询
-- 不好的做法
SELECT * FROM products 
WHERE product_data->>'price' > '100'  -- 字符串比较，不会使用索引
  AND product_data->>'in_stock' = 'true';

-- 好的做法：使用正确类型和索引
CREATE INDEX idx_product_price ON products (((product_data->>'price')::NUMERIC));
CREATE INDEX idx_product_in_stock ON products ((product_data->'in_stock'));

SELECT * FROM products 
WHERE (product_data->>'price')::NUMERIC > 100
  AND product_data->'in_stock' = 'true';

-- 优化数组查询
-- 不好的做法
SELECT * FROM articles WHERE 'postgresql' = ANY(tags);  -- 不使用索引

-- 好的做法
CREATE INDEX idx_articles_tags ON articles USING gin(tags);
SELECT * FROM articles WHERE tags @> ARRAY['postgresql'];

-- 优化全文搜索
-- 不好的做法
SELECT * FROM articles WHERE content LIKE '%database%';  -- 全表扫描

-- 好的做法
CREATE INDEX idx_articles_search ON articles USING gin(to_tsvector('english', content));
SELECT * FROM articles WHERE to_tsvector('english', content) @@ to_tsquery('english', 'database');