-- ============================================================================ -- PostgreSQL 18 — Search-Optimized SQL Queries (搜索优化 SQL 查询) -- 覆盖: 全文索引(FTS) / LIKE 模式 / 函数索引 / 相关度排序 / 查询取舍 -- 场景: 万亿数据高并发检索 / 国际时区时间差 / 国际多种货币结算 -- -- 引擎: PostgreSQL 18.6 (监听 5433) -- 完全幂等: 重跑自动 DROP/CREATE; setseed 保证数据可复现; 连跑 2 次逐行一致 -- 连接: PGPASSWORD=**** psql -U postgres -h localhost -p 5433 -d jewelry_pg -f 本文件 -- (密码通过环境变量注入, 不写入任何交付物) -- ============================================================================ \set ON_ERROR_STOP off \timing off -- ---------------------------------------------------------------------------- -- 0. 幂等度量 / 自检框架 -- pg_plan_stats : 抓 EXPLAIN(ANALYZE,TIMING OFF,FORMAT JSON) 真实代价 -- pg_find_scan : 下钻到真正的"扫描"节点 (顶层常是 Limit/Sort/Bitmap) -- pg_demo_v : 登记一项自检 (pass_cnt 1=通过 0=失败) -- pg_demo_err_probe : 捕获真实报错 (用于演示限制/取舍) -- ---------------------------------------------------------------------------- DROP FUNCTION IF EXISTS pg_find_scan(json) CASCADE; CREATE OR REPLACE FUNCTION pg_find_scan(cur json) RETURNS text LANGUAGE plpgsql AS $$ DECLARE nt text; BEGIN nt := cur->>'Node Type'; IF nt LIKE '%Scan' THEN RETURN nt; END IF; -- 命中真正的扫描节点 IF cur->'Plans' IS NOT NULL AND json_array_length(cur->'Plans') > 0 THEN RETURN pg_find_scan(cur->'Plans'->0); -- 继续下钻 END IF; RETURN nt; END $$; DROP FUNCTION IF EXISTS pg_plan_stats(text) CASCADE; CREATE OR REPLACE FUNCTION pg_plan_stats( p_sql text, OUT actual_rows bigint, OUT shared_blks bigint, OUT node_type text) RETURNS record LANGUAGE plpgsql AS $$ DECLARE j json; BEGIN EXECUTE 'EXPLAIN (ANALYZE, TIMING OFF, FORMAT JSON) ' || p_sql INTO j; actual_rows := (j->0->'Plan'->>'Actual Rows')::numeric::bigint; shared_blks := COALESCE((j->0->'Plan'->>'Shared Hit Blocks')::numeric::bigint,0) + COALESCE((j->0->'Plan'->>'Shared Read Blocks')::numeric::bigint,0); node_type := pg_find_scan(j->0->'Plan'); END $$; DROP TABLE IF EXISTS pg_demo_check; CREATE TABLE pg_demo_check ( id serial PRIMARY KEY, check_name text, pass_cnt int, detail text, created_at timestamptz DEFAULT now() ); DROP FUNCTION IF EXISTS pg_demo_v(text,int,text) CASCADE; CREATE OR REPLACE FUNCTION pg_demo_v(p_name text, p_pass int, p_detail text) RETURNS void LANGUAGE plpgsql AS $$ BEGIN INSERT INTO pg_demo_check(check_name, pass_cnt, detail) VALUES (p_name, p_pass, p_detail); END $$; DROP TABLE IF EXISTS pg_demo_probe; CREATE TABLE pg_demo_probe ( id serial PRIMARY KEY, label text, sqlstate text, msg text ); DROP FUNCTION IF EXISTS pg_demo_err_probe(text,text) CASCADE; CREATE OR REPLACE FUNCTION pg_demo_err_probe(p_label text, p_sql text) RETURNS void LANGUAGE plpgsql AS $$ BEGIN BEGIN EXECUTE p_sql; INSERT INTO pg_demo_probe(label, sqlstate, msg) VALUES (p_label, 'OK', 'executed without error'); EXCEPTION WHEN OTHERS THEN INSERT INTO pg_demo_probe(label, sqlstate, msg) VALUES (p_label, SQLSTATE, SQLERRM); END; END $$; -- pg_trgm 是标准 contrib 扩展, 提供 trigram 子串/模糊检索与 gin_trgm_ops -- 用 DO 块包起来, 避免第二次重跑时打印 "已经存在" NOTICE, 保证连跑字节一致 DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_extension WHERE extname='pg_trgm') THEN CREATE EXTENSION pg_trgm; END IF; END $$; -- ---------------------------------------------------------------------------- -- 1. 八域维度表 + 交易事实表 (完全幂等 DROP/CREATE) -- ---------------------------------------------------------------------------- DROP TABLE IF EXISTS pg_txn CASCADE; DROP TABLE IF EXISTS pg_product CASCADE; DROP TABLE IF EXISTS pg_customer CASCADE; DROP TABLE IF EXISTS pg_employee CASCADE; DROP TABLE IF EXISTS pg_supplier CASCADE; DROP TABLE IF EXISTS pg_warehouse CASCADE; CREATE TABLE pg_warehouse ( warehouse_id int PRIMARY KEY, name text, country_code text ); INSERT INTO pg_warehouse SELECT n, 'WH-'||n||'-'||(ARRAY['CN','US','JP','DE','SG'])[1+(n%5)], (ARRAY['CN','US','JP','DE','SG'])[1+(n%5)] FROM generate_series(1,50) n; CREATE TABLE pg_supplier ( supplier_id int PRIMARY KEY, name text, country_code text ); INSERT INTO pg_supplier SELECT n, 'SUP-'||n||'-'||(ARRAY['CN','TH','IN','IT','US'])[1+(n%5)], (ARRAY['CN','TH','IN','IT','US'])[1+(n%5)] FROM generate_series(1,200) n; CREATE TABLE pg_employee ( emp_id int PRIMARY KEY, emp_name text, dept text, -- 八域 country_code text ); INSERT INTO pg_employee SELECT n, (ARRAY['Amy','Bob','Cara','Dan','Eve','Fan','Gus','Hana'])[1+(n%8)]||' '||n, (ARRAY['OA','ERP','CRM','HRM','MES','SCM','WMS','KMS'])[1+(n%8)], (ARRAY['CN','US','JP','DE','GB','FR','HK','SG'])[1+(n%8)] FROM generate_series(1,500) n; CREATE TABLE pg_customer ( customer_id int PRIMARY KEY, cust_name text, email text, country_code text, tz text, vip_level int ); INSERT INTO pg_customer SELECT n, (ARRAY['Acme','Globex','Initech','Umbrella','Soylent','Stark','Wayne','Wonka'])[1+(n%8)]||' Jewelry '||n, 'cust'||n||'@jewelry.example.com', (ARRAY['CN','US','JP','DE','GB','FR','HK','SG'])[1+(n%8)], (CASE (ARRAY['CN','US','JP','DE','GB','FR','HK','SG'])[1+(n%8)] WHEN 'CN' THEN 'Asia/Shanghai' WHEN 'US' THEN 'America/New_York' WHEN 'JP' THEN 'Asia/Tokyo' WHEN 'DE' THEN 'Europe/Berlin' WHEN 'GB' THEN 'Europe/London' WHEN 'FR' THEN 'Europe/Paris' WHEN 'HK' THEN 'Asia/Hong_Kong' WHEN 'SG' THEN 'Asia/Singapore' END), (1 + (n % 5)) FROM generate_series(1,10000) n; -- KMS 商品目录 (含多语言名称/描述 + 生成式 tsvector 全文向量) CREATE TABLE pg_product ( product_id int PRIMARY KEY, sku text, name_en text, name_zh text, category text, material text, desc_en text, desc_zh text, specs jsonb, search_vec tsvector GENERATED ALWAYS AS ( to_tsvector('english', coalesce(desc_en,'')||' '||coalesce(name_en,'')) ) STORED ); INSERT INTO pg_product SELECT n, 'SKU-'||lpad(n::text,6,'0'), (ARRAY['Diamond','Ruby','Sapphire','Emerald','Pearl','Gold','Platinum','Tanzanite'])[1+(n%8)]||' '|| (ARRAY['Necklace','Ring','Bracelet','Earrings','Pendant','Brooch'])[1+(n%6)], (ARRAY['钻石','红宝石','蓝宝石','祖母绿','珍珠','黄金','铂金','坦桑石'])[1+(n%8)]||' '|| (ARRAY['项链','戒指','手链','耳环','吊坠','胸针'])[1+(n%6)], (ARRAY['Fine Jewelry','Gemstone','Gold','Silver','Luxury'])[1+(n%5)], (ARRAY['18K Gold','Platinum','Silver','Rose Gold'])[1+(n%4)], 'Certified '||(ARRAY['Diamond','Ruby','Sapphire','Emerald','Pearl','Gold','Platinum','Tanzanite'])[1+(n%8)] ||' '||(ARRAY['Necklace','Ring','Bracelet','Earrings','Pendant','Brooch'])[1+(n%6)] ||' with GIA report, wholesale price.', '认证 '||(ARRAY['钻石','红宝石','蓝宝石','祖母绿','珍珠','黄金','铂金','坦桑石'])[1+(n%8)] ||(ARRAY['项链','戒指','手链','耳环','吊坠','胸针'])[1+(n%6)]||' 含 GIA 证书', jsonb_build_object('carat', round((0.5 + (n%50)/10.0)::numeric,2), 'color', (ARRAY['D','E','F','G','H'])[1+(n%5)], 'clarity', (ARRAY['VVS1','VVS2','VS1','VS2','SI1'])[1+(n%5)]) FROM generate_series(1,2000) n; -- 交易事实表 (八域 system_code + 客户/商品/国家/币种 + 时区/本地日 生成列 + 全文向量) CREATE TABLE pg_txn ( txn_id bigint PRIMARY KEY, system_code text NOT NULL, customer_id int, product_id int, country_code text, currency_code text, amount_orig numeric(14,2), amount_usd numeric(14,4), created_utc timestamptz, reference_no text, note text, note_tsv tsvector GENERATED ALWAYS AS (to_tsvector('english', note)) STORED, tz text GENERATED ALWAYS AS ( CASE country_code WHEN 'CN' THEN 'Asia/Shanghai' WHEN 'US' THEN 'America/New_York' WHEN 'JP' THEN 'Asia/Tokyo' WHEN 'DE' THEN 'Europe/Berlin' WHEN 'GB' THEN 'Europe/London' WHEN 'FR' THEN 'Europe/Paris' WHEN 'HK' THEN 'Asia/Hong_Kong' WHEN 'SG' THEN 'Asia/Singapore' END) STORED, local_date date GENERATED ALWAYS AS ( (created_utc AT TIME ZONE ( CASE country_code WHEN 'CN' THEN 'Asia/Shanghai' WHEN 'US' THEN 'America/New_York' WHEN 'JP' THEN 'Asia/Tokyo' WHEN 'DE' THEN 'Europe/Berlin' WHEN 'GB' THEN 'Europe/London' WHEN 'FR' THEN 'Europe/Paris' WHEN 'HK' THEN 'Asia/Hong_Kong' WHEN 'SG' THEN 'Asia/Singapore' END))::date) STORED, rand_val double precision ); -- setseed 保证每次重跑数据完全一致 -> 输出幂等 SELECT setseed(0.123); INSERT INTO pg_txn (txn_id, system_code, customer_id, product_id, country_code, currency_code, amount_orig, amount_usd, created_utc, reference_no, note, rand_val) SELECT n, (ARRAY['OA','ERP','CRM','HRM','MES','SCM','WMS','KMS'])[1 + (n % 8)], (1 + ((n + n/8) % 10000))::int, -- 解耦: 避免 customer_id 与 note 的 n%8 耦合, 否则跨域 JOIN 永不相交 (1 + (n % 2000))::int, cc, CASE cc WHEN 'CN' THEN 'CNY' WHEN 'US' THEN 'USD' WHEN 'JP' THEN 'JPY' WHEN 'DE' THEN 'EUR' WHEN 'GB' THEN 'GBP' WHEN 'FR' THEN 'EUR' WHEN 'HK' THEN 'HKD' WHEN 'SG' THEN 'SGD' END, round((100 + random()*99900)::numeric, 2), round(((100 + random()*99900) / CASE cc WHEN 'CN' THEN 7.1 WHEN 'US' THEN 1.0 WHEN 'JP' THEN 150.0 WHEN 'DE' THEN 0.92 WHEN 'GB' THEN 0.79 WHEN 'FR' THEN 0.92 WHEN 'HK' THEN 7.8 WHEN 'SG' THEN 1.35 END)::numeric, 4), ('2025-01-01 00:00:00+00'::timestamptz + (random() * interval '365 days')), 'REF-'||n||'-'||upper(substring(md5(n::text),1,6)), 'PO '||(100000+n)||' '|| (ARRAY['Diamond','Ruby','Sapphire','Emerald','Pearl','Gold','Platinum','Tanzanite'])[1+(n%8)]||' '|| (ARRAY['Necklace','Ring','Bracelet','Earrings','Pendant','Brooch'])[1+(n%6)]||' '|| (ARRAY['certified','18K','platinum','custom','wholesale','retail'])[1+(n%6)]||' '|| (CASE WHEN n % 200 = 0 THEN 'Zirconia ' ELSE '' END)|| -- 稀有词, 用于高选择性检索演示 (CASE WHEN n % 20 = 0 THEN 'Tanzanite special' ELSE '' END), random() FROM ( SELECT n, (ARRAY['CN','US','JP','DE','GB','FR','HK','SG'])[1 + (n % 8)] AS cc FROM generate_series(1,100000) n ) s; -- ---------------------------------------------------------------------------- -- 2. 支撑索引 (检索取舍的核心) -- ---------------------------------------------------------------------------- CREATE INDEX idx_txn_system ON pg_txn(system_code); CREATE INDEX idx_txn_cover ON pg_txn(system_code, amount_usd); CREATE INDEX idx_txn_created ON pg_txn(created_utc); CREATE INDEX idx_txn_cust ON pg_txn(customer_id); CREATE INDEX idx_txn_ccy ON pg_txn(currency_code); CREATE INDEX idx_txn_ref ON pg_txn(reference_no); CREATE INDEX idx_txn_ref_lower ON pg_txn(lower(reference_no)); -- 函数索引: 大小写不敏感前缀 CREATE INDEX idx_txn_note_tsv ON pg_txn USING gin(note_tsv); -- 全文索引 GIN CREATE INDEX idx_txn_localday ON pg_txn(local_date); -- 时区本地日 函数/生成列索引 CREATE INDEX idx_cust_name_lower ON pg_customer(lower(cust_name));-- 函数索引 CREATE INDEX idx_product_search ON pg_product USING gin(search_vec); VACUUM ANALYZE pg_txn; VACUUM ANALYZE pg_customer; VACUUM ANALYZE pg_product; -- ============================================================================ -- § 演示区: 搜索优化实战 (输出供阅读, 不参与自检) -- ============================================================================ -- 演示 A: 全文检索按相关度取前 5 SELECT txn_id, ts_rank(note_tsv, to_tsquery('english','zirconia')) AS rank, left(note,46) AS note_snippet FROM pg_txn WHERE note_tsv @@ to_tsquery('english','zirconia') ORDER BY rank DESC, txn_id LIMIT 5; -- 演示 B: trigram 模糊检索 (容忍拼写错误) SELECT txn_id, left(note,46) AS note_snippet FROM pg_txn WHERE note % 'Dimond' -- 相似度 >= 0.3 阈值, 命中 Diamond LIMIT 5; -- ============================================================================ -- § 自检区: V1–V17 (全部幂等可重跑) -- ============================================================================ -- V1: 无 trigram 时 '%substring%' 只能 Seq Scan; 建 trigram 后转为索引扫描 DROP INDEX IF EXISTS idx_txn_note_trgm; DO $$ DECLARE r1 record; BEGIN SELECT * INTO r1 FROM pg_plan_stats( $q$SELECT count(*) FROM pg_txn WHERE note LIKE '%Zirconia%'$q$); PERFORM pg_demo_v('V1_before_no_trigram', CASE WHEN r1.node_type = 'Seq Scan' THEN 1 ELSE 0 END, 'node='||r1.node_type||' blocks='||r1.shared_blks||' rows='||r1.actual_rows); END $$; -- 建 trigram GIN 索引 (pg_trgm 已在顶部创建); 然后 ANALYZE 让优化器采用 CREATE INDEX idx_txn_note_trgm ON pg_txn USING gin(note gin_trgm_ops); ANALYZE pg_txn; DO $$ DECLARE r2 record; BEGIN SELECT * INTO r2 FROM pg_plan_stats( $q$SELECT count(*) FROM pg_txn WHERE note LIKE '%Zirconia%'$q$); PERFORM pg_demo_v('V1_after_trigram', CASE WHEN r2.node_type LIKE '%Index%' OR r2.node_type LIKE '%Bitmap%' THEN 1 ELSE 0 END, 'node='||r2.node_type||' blocks='||r2.shared_blks||' rows='||r2.actual_rows ||' (btree 无法服务 %子串%, trigram 可以)'); END $$; -- V2: 左锚定 LIKE 'prefix%' 走 btree 索引 (左锚定最关键) DO $$ DECLARE r record; BEGIN SELECT * INTO r FROM pg_plan_stats( $q$SELECT txn_id FROM pg_txn WHERE reference_no LIKE 'REF-1234%'$q$); PERFORM pg_demo_v('V2_left_anchored_btree', CASE WHEN r.node_type LIKE '%Index%' OR r.node_type LIKE '%Bitmap%' THEN 1 ELSE 0 END, 'node='||r.node_type||' blocks='||r.shared_blks||' rows='||r.actual_rows); END $$; -- V3: 全文检索 @@ 走 GIN + 相关度 ts_rank DO $$ DECLARE r record; rk float; BEGIN SELECT * INTO r FROM pg_plan_stats( $q$SELECT txn_id FROM pg_txn WHERE note_tsv @@ to_tsquery('english','zirconia')$q$); SELECT ts_rank(note_tsv, to_tsquery('english','zirconia')) INTO rk FROM pg_txn WHERE note_tsv @@ to_tsquery('english','zirconia') LIMIT 1; PERFORM pg_demo_v('V3_fts_gin_index', CASE WHEN r.node_type LIKE '%Index%' OR r.node_type LIKE '%Bitmap%' THEN 1 ELSE 0 END, 'node='||r.node_type||' blocks='||r.shared_blks||' sample_rank='||round(rk::numeric,4)); END $$; -- V4: 函数索引 lower(cust_name) 服务大小写不敏感前缀 DO $$ DECLARE r record; BEGIN SELECT * INTO r FROM pg_plan_stats( $q$SELECT customer_id FROM pg_customer WHERE lower(cust_name) LIKE 'acme%'$q$); PERFORM pg_demo_v('V4_functional_index_lower', CASE WHEN (r.node_type LIKE '%Index%' OR r.node_type LIKE '%Bitmap%') AND r.actual_rows > 0 THEN 1 ELSE 0 END, 'node='||r.node_type||' blocks='||r.shared_blks||' rows='||r.actual_rows); END $$; -- V5: trigram 相似度 (%) 容错检索 (拼写错误 Dimond -> Diamond) DO $$ DECLARE cnt int; BEGIN SELECT count(*) INTO cnt FROM pg_txn WHERE word_similarity('Dimond', note) > 0.3; PERFORM pg_demo_v('V5_trigram_similarity', CASE WHEN cnt > 0 THEN 1 ELSE 0 END, 'fuzzy(Dimond->Diamond) matched='||cnt); END $$; -- V6: 复合相关度 (FTS rank + 时效 + 金额) 排序确定性 DO $$ DECLARE arr1 int[]; arr2 int[]; BEGIN SELECT array_agg(txn_id ORDER BY score DESC, txn_id) INTO arr1 FROM ( SELECT txn_id, ts_rank(note_tsv, to_tsquery('english','zirconia')) * 1.0 + (extract(epoch from (now()-created_utc))/86400.0)*(-0.001) + (amount_usd/100000.0)*0.1 AS score FROM pg_txn WHERE note_tsv @@ to_tsquery('english','zirconia') ORDER BY score DESC, txn_id LIMIT 20 ) t; SELECT array_agg(txn_id ORDER BY score DESC, txn_id) INTO arr2 FROM ( SELECT txn_id, ts_rank(note_tsv, to_tsquery('english','zirconia')) * 1.0 + (extract(epoch from (now()-created_utc))/86400.0)*(-0.001) + (amount_usd/100000.0)*0.1 AS score FROM pg_txn WHERE note_tsv @@ to_tsquery('english','zirconia') ORDER BY score DESC, txn_id LIMIT 20 ) t; PERFORM pg_demo_v('V6_combined_relevance_deterministic', CASE WHEN arr1 = arr2 AND array_length(arr1,1)=20 THEN 1 ELSE 0 END, 'rows='||coalesce(array_length(arr1,1),0)||' identical='||(CASE WHEN arr1=arr2 THEN 'yes' ELSE 'no' END)); END $$; -- V7: 时区感知检索 — 按"本地日"生成列索引, 与 UTC 日口径不同 DO $$ DECLARE r record; cnt_local int; cnt_utc int; BEGIN SELECT * INTO r FROM pg_plan_stats( $q$SELECT count(*) FROM pg_txn WHERE local_date = '2025-06-15'$q$); SELECT count(*) INTO cnt_local FROM pg_txn WHERE local_date = '2025-06-15'; SELECT count(*) INTO cnt_utc FROM pg_txn WHERE created_utc::date = '2025-06-15'; PERFORM pg_demo_v('V7_timezone_local_date_index', CASE WHEN (r.node_type LIKE '%Index%' OR r.node_type LIKE '%Bitmap%') THEN 1 ELSE 0 END, 'node='||r.node_type||' local_cnt='||cnt_local||' utc_cnt='||cnt_utc ||' (本地日与UTC日口径不同, 体现时区差)'); END $$; -- V8: 多币种过滤 + 结算口径 (统一 amount_usd, 绝不用 SUM(amount_orig)) DO $$ DECLARE r record; usd_sum numeric; orig_sum numeric; BEGIN SELECT * INTO r FROM pg_plan_stats( $q$SELECT count(*) FROM pg_txn WHERE currency_code = 'USD'$q$); SELECT SUM(amount_usd) INTO usd_sum FROM pg_txn WHERE currency_code='USD'; SELECT SUM(amount_orig) INTO orig_sum FROM pg_txn WHERE currency_code='USD'; PERFORM pg_demo_v('V8_multicurrency_filter', CASE WHEN (r.node_type LIKE '%Index%' OR r.node_type LIKE '%Bitmap%') THEN 1 ELSE 0 END, 'node='||r.node_type||' usd_settle='||round(usd_sum::numeric,2) ||' orig_only(同币种才有意义)='||round(orig_sum::numeric,2)); END $$; -- V9: FTS vs trigram 取舍 — FTS 查"词素", 查不到子串 'mond'; trigram 查任意子串 DO $$ DECLARE fts_cnt int; tri_cnt int; BEGIN SELECT count(*) INTO fts_cnt FROM pg_txn WHERE note_tsv @@ to_tsquery('english','mond'); -- 'mond' 不是词素 -> 0 SELECT count(*) INTO tri_cnt FROM pg_txn WHERE note LIKE '%mond%'; -- 命中 Diamond 内 'mond' PERFORM pg_demo_v('V9_fts_vs_trigram_tradeoff', CASE WHEN fts_cnt = 0 AND tri_cnt > 0 THEN 1 ELSE 0 END, 'fts(mond)='||fts_cnt||' trigram(%mond%)='||tri_cnt ||' (FTS=词/短语; trigram=任意子串/模糊)'); END $$; -- V10: 索引体积取舍 — GIN-FTS vs GIN-trigram vs btree DO $$ DECLARE s_fts int; s_tri int; s_bt int; BEGIN SELECT pg_relation_size('idx_txn_note_tsv')/1024 INTO s_fts; SELECT pg_relation_size('idx_txn_note_trgm')/1024 INTO s_tri; SELECT pg_relation_size('idx_txn_ref')/1024 INTO s_bt; PERFORM pg_demo_v('V10_index_size_tradeoff', CASE WHEN s_fts>0 AND s_tri>0 AND s_bt>0 THEN 1 ELSE 0 END, 'fts_gin_kb='||s_fts||' trigram_gin_kb='||s_tri||' btree_kb='||s_bt); END $$; -- V11: 万亿级深度检索代价 — 无过滤全扫不可接受 DO $$ DECLARE r record; BEGIN SELECT * INTO r FROM pg_plan_stats( $q$SELECT sum(amount_orig) FROM pg_txn$q$); -- 未索引列 -> 必走 Seq Scan 全表 PERFORM pg_demo_v('V11_fullscan_cost', CASE WHEN r.node_type = 'Seq Scan' AND r.shared_blks > 1000 THEN 1 ELSE 0 END, 'node='||r.node_type||' blocks='||r.shared_blks ||' (万亿级须分区+覆盖索引, 全扫不可接受)'); END $$; -- V12: 搜索结果稳定性 — 必须加唯一 tiebreak (txn_id) 才能幂等翻页 DO $$ DECLARE arr1 int[]; arr2 int[]; BEGIN SELECT array_agg(txn_id ORDER BY txn_id) INTO arr1 FROM (SELECT txn_id FROM pg_txn WHERE note_tsv @@ to_tsquery('english','zirconia') ORDER BY txn_id LIMIT 50 OFFSET 0) t; SELECT array_agg(txn_id ORDER BY txn_id) INTO arr2 FROM (SELECT txn_id FROM pg_txn WHERE note_tsv @@ to_tsquery('english','zirconia') ORDER BY txn_id LIMIT 50 OFFSET 0) t; PERFORM pg_demo_v('V12_search_stability', CASE WHEN arr1 = arr2 AND array_length(arr1,1)=50 THEN 1 ELSE 0 END, 'rows='||coalesce(array_length(arr1,1),0)||' identical='||(CASE WHEN arr1=arr2 THEN 'yes' ELSE 'no' END)); END $$; -- V13: 大小写不敏感前缀 — 函数索引 lower(reference_no) 服务 ILIKE/lower DO $$ DECLARE r record; BEGIN SELECT * INTO r FROM pg_plan_stats( $q$SELECT txn_id FROM pg_txn WHERE lower(reference_no) LIKE 'ref-1234%'$q$); PERFORM pg_demo_v('V13_ci_prefix_functional_index', CASE WHEN (r.node_type LIKE '%Index%' OR r.node_type LIKE '%Bitmap%') THEN 1 ELSE 0 END, 'node='||r.node_type||' blocks='||r.shared_blks||' rows='||r.actual_rows ||' (大小写不敏感前缀须函数索引或 C 排序)'); END $$; -- V14: CJK 全文限制 — 中文需 zhparser/jieba 提供 'chinese' 配置 (默认无) SELECT pg_demo_err_probe('V14_cjk_needs_parser', $q$SELECT to_tsvector('chinese', '钻石项链') FROM pg_product LIMIT 1$q$); DO $$ DECLARE st text; BEGIN SELECT sqlstate INTO st FROM pg_demo_probe WHERE label='V14_cjk_needs_parser'; PERFORM pg_demo_v('V14_cjk_needs_parser', CASE WHEN st = '42704' THEN 1 ELSE 0 END, 'sqlstate='||coalesce(st,'none')||' (中文需安装 zhparser 提供 chinese 配置)'); END $$; -- V15: KMS 商品目录全文检索 (GIN on search_vec) DO $$ DECLARE r record; cnt int; BEGIN SELECT * INTO r FROM pg_plan_stats( $q$SELECT product_id FROM pg_product WHERE search_vec @@ to_tsquery('english','diamond')$q$); SELECT count(*) INTO cnt FROM pg_product WHERE search_vec @@ to_tsquery('english','diamond'); PERFORM pg_demo_v('V15_kms_product_fts', CASE WHEN (r.node_type LIKE '%Index%' OR r.node_type LIKE '%Bitmap%') AND cnt>0 THEN 1 ELSE 0 END, 'node='||r.node_type||' matches='||cnt||' (KMS 商品目录全文检索)'); END $$; -- V16: 跨域 JOIN 检索 — CRM 客户名 + ERP 交易关键字 联合检索 DO $$ DECLARE cnt int; BEGIN SELECT count(*) INTO cnt FROM pg_txn t JOIN pg_customer c ON c.customer_id = t.customer_id WHERE lower(c.cust_name) LIKE 'acme%' AND t.note_tsv @@ to_tsquery('english','diamond'); PERFORM pg_demo_v('V16_cross_domain_join_search', CASE WHEN cnt > 0 THEN 1 ELSE 0 END, 'crm_x_erp_matches='||cnt||' (CRM 客户名 + 交易关键字 联合检索)'); END $$; -- V17: 覆盖索引 -> Index Only Scan (万亿级减少回表极其关键) DROP INDEX IF EXISTS idx_txn_ccy_cover; CREATE INDEX idx_txn_ccy_cover ON pg_txn(currency_code) INCLUDE (amount_usd); ANALYZE pg_txn; DO $$ DECLARE r record; BEGIN SELECT * INTO r FROM pg_plan_stats( $q$SELECT currency_code, amount_usd FROM pg_txn WHERE currency_code='USD'$q$); PERFORM pg_demo_v('V17_covering_index_only_scan', CASE WHEN r.node_type = 'Index Only Scan' THEN 1 ELSE 0 END, 'node='||r.node_type||' blocks='||r.shared_blks||' rows='||r.actual_rows); END $$; -- ============================================================================ -- § 自检汇总 (幂等输出) -- ============================================================================ SELECT check_name, pass_cnt, detail FROM pg_demo_check ORDER BY id; SELECT CASE WHEN pass = tot THEN 'ALL PASS ('||pass||'/'||tot||')' ELSE '未全部通过 ('||pass||'/'||tot||')' END AS result FROM ( SELECT count(*) tot, count(*) FILTER (WHERE pass_cnt = 1) pass FROM pg_demo_check ) s;
