#!/usr/bin/env bash
# 盈尺（Surinch）PostgreSQL LIKE '%关键词%' / pg_trgm 演示脚本 v1.0
# 实测环境：PostgreSQL 17.11，库排序规则 C.UTF-8，2026-10-29。
# 只许在测试实例运行：脚本会 DROP/CREATE 库 ${LAB_DB}（默认 pg_trgm_lab）及 ${LAB_DB}_c。
# 用法：PGUSER=postgres ./postgresql-pg-trgm-like-demo-v1.0.sh
set -euo pipefail
LAB_DB="${LAB_DB:-pg_trgm_lab}"
case "$LAB_DB" in *_lab) ;; *) echo "LAB_DB 必须以 _lab 结尾，拒绝运行" >&2; exit 1;; esac
P() { psql -X -v ON_ERROR_STOP=1 "$@"; }

P -d postgres -c "DROP DATABASE IF EXISTS ${LAB_DB}" -c "CREATE DATABASE ${LAB_DB}"
P -d "$LAB_DB" <<'SQL'
\timing on
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS pgstattuple;
CREATE TABLE products (id bigint PRIMARY KEY, sku text NOT NULL, name text NOT NULL, created_at timestamptz NOT NULL);
INSERT INTO products
SELECT g, 'SKU-' || lpad((g*7919 % 1000003)::text, 7, '0'),
       (ARRAY['wireless','bamboo','steel','cotton','silicone','portable','vintage','smart','organic','foldable'])[1 + (g*31 % 10)]
       || ' ' || (ARRAY['charger','cutting board','water bottle','yoga mat','lamp','backpack','kettle','phone case','notebook','speaker','towel','mug'])[1 + (g*17 % 12)]
       || ' ' || substr(md5(g::text), 1, 6)
       || ' ' || (ARRAY['数据线','砧板','保温杯','瑜伽垫','台灯','双肩包','电热水壶','手机壳','笔记本','音箱','毛巾','马克杯'])[1 + (g*13 % 12)],
       timestamptz '2026-01-01' + (g % 300) * interval '1 day'
FROM generate_series(1::bigint, 1000000::bigint) g;
UPDATE products SET name = name || ' sunflower 向日葵' WHERE id % 20000 = 7;
VACUUM ANALYZE products;
SELECT datcollate, datctype FROM pg_database WHERE datname = current_database();
SELECT count(*) AS total, pg_size_pretty(pg_table_size('products')) AS heap FROM products;

\echo '== 1. 无索引：%sunflower%（跑两次取第二次）'
EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM products WHERE name LIKE '%sunflower%';
EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM products WHERE name LIKE '%sunflower%';

\echo '== 2. 默认 btree 在 C.UTF-8 下不能服务前缀 LIKE（关 seqscan 也不用）'
BEGIN;
CREATE INDEX products_name_btree ON products (name);
SET LOCAL enable_seqscan = off;
EXPLAIN (ANALYZE) SELECT id FROM products WHERE name LIKE 'wireless charger 0%';
ROLLBACK;

\echo '== 3. text_pattern_ops 前缀索引'
CREATE INDEX products_name_pattern ON products (name text_pattern_ops);
SELECT pg_size_pretty(pg_relation_size('products_name_pattern')) AS pattern_size;
EXPLAIN (ANALYZE) SELECT id FROM products WHERE name LIKE 'wireless charger 0%';
EXPLAIN (ANALYZE) SELECT id FROM products WHERE name LIKE '%sunflower%';
DROP INDEX products_name_pattern;

\echo '== 4. GIN gin_trgm_ops'
SET maintenance_work_mem = '256MB';
CREATE INDEX products_name_trgm_gin ON products USING gin (name gin_trgm_ops);
SELECT pg_size_pretty(pg_relation_size('products_name_trgm_gin')) AS gin_size;
ANALYZE products;
SELECT show_trgm('sunflower') AS en, show_trgm('向日葵') AS cn, show_trgm('a1') AS two_char;
EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM products WHERE name LIKE '%sunflower%';
EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM products WHERE name ILIKE '%SunFlower%';
EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM products WHERE name LIKE '%向日葵%';
EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM products WHERE name LIKE '%bottle%';
\echo '== 5. 两个字符的关键词：优化器选 Seq Scan；强制走 GIN 反而更慢'
EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM products WHERE name LIKE '%台灯%';
SET enable_seqscan = off;
EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM products WHERE name LIKE '%台灯%';
EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM products WHERE name LIKE '%a1%';
RESET enable_seqscan;

\echo '== 6. GiST gist_trgm_ops 对比'
DROP INDEX products_name_trgm_gin;
CREATE INDEX products_name_trgm_gist ON products USING gist (name gist_trgm_ops);
SELECT pg_size_pretty(pg_relation_size('products_name_trgm_gist')) AS gist_size;
EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM products WHERE name LIKE '%sunflower%';
EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM products WHERE name LIKE '%sunflower%';
DROP INDEX products_name_trgm_gist;

\echo '== 7. 写入代价：10 万行批量 / 2000 次逐行'
CREATE TABLE w_noidx (id bigint PRIMARY KEY, sku text, name text, created_at timestamptz);
CREATE TABLE w_gin (id bigint PRIMARY KEY, sku text, name text, created_at timestamptz);
CREATE INDEX w_gin_trgm ON w_gin USING gin (name gin_trgm_ops);
CREATE TABLE w_gin_nofu (id bigint PRIMARY KEY, sku text, name text, created_at timestamptz);
CREATE INDEX w_gin_nofu_trgm ON w_gin_nofu USING gin (name gin_trgm_ops) WITH (fastupdate = off);
INSERT INTO w_noidx SELECT * FROM products WHERE id <= 100000;
INSERT INTO w_gin SELECT * FROM products WHERE id <= 100000;
INSERT INTO w_gin_nofu SELECT * FROM products WHERE id <= 100000;
DO $$ BEGIN FOR i IN 1..2000 LOOP INSERT INTO w_noidx SELECT id+2000000, sku, name, created_at FROM products WHERE id = i; END LOOP; END $$;
DO $$ BEGIN FOR i IN 1..2000 LOOP INSERT INTO w_gin SELECT id+2000000, sku, name, created_at FROM products WHERE id = i; END LOOP; END $$;
DO $$ BEGIN FOR i IN 1..2000 LOOP INSERT INTO w_gin_nofu SELECT id+2000000, sku, name, created_at FROM products WHERE id = i; END LOOP; END $$;
SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid)) FROM pg_stat_user_indexes WHERE relname LIKE 'w_%' ORDER BY 1;
SELECT * FROM pgstatginindex('w_gin_trgm');
SELECT gin_clean_pending_list('w_gin_trgm') AS pages_cleaned;
DROP TABLE w_noidx, w_gin, w_gin_nofu;
SQL

echo '== 8. LC_CTYPE=C 的库：中文不出三元组'
P -d postgres -c "DROP DATABASE IF EXISTS ${LAB_DB}_c" -c "CREATE DATABASE ${LAB_DB}_c TEMPLATE template0 ENCODING 'UTF8' LC_COLLATE 'C' LC_CTYPE 'C'"
P -d "${LAB_DB}_c" <<'SQL'
CREATE EXTENSION IF NOT EXISTS pg_trgm;
SELECT datcollate, datctype FROM pg_database WHERE datname = current_database();
SELECT show_trgm('向日葵') AS cn, show_trgm('sunflower') AS en;
CREATE TABLE t AS SELECT g AS id, CASE WHEN g % 20000 = 7 THEN '马克杯 向日葵 款' ELSE '马克杯 普通 款 ' || g END AS name FROM generate_series(1,200000) g;
CREATE INDEX ON t USING gin (name gin_trgm_ops);
ANALYZE t;
EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM t WHERE name LIKE '%向日葵%';
SET enable_seqscan = off;
EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM t WHERE name LIKE '%向日葵%';
SQL
echo "完成。测试库保留：${LAB_DB}、${LAB_DB}_c（不需要可手动 DROP）"