建表&數據初始化可參考PostgreSQL 分區表——范圍分區SQL實踐

背景：

給t_common_work_order_log的handle_user_name新建索引后，使用LIKE右模糊匹配查詢時，發現走的全表掃描

CREATE INDEX order_log_handle_user_name_index ON t_common_work_order_log (handle_user_name);

EXPLAIN ANALYZE SELECT COUNT( * ) 
FROMt_common_work_order_log 
WHEREhandle_user_name LIKE'張秀%';

分析：

由于handle_user_name已經建了索引，查詢資料發現B-tree索引需要特定的運算符類別（如text_pattern_ops 或 varchar_pattern_ops ）才能讓LIKE右模糊生效

PostgreSQL 索引運算符類：text_ops 與 text_pattern_ops 的區別

在 PostgreSQL 中，text_ops 和 text_pattern_ops 是兩種不同的運算符類(operator class)，它們決定了索引如何支持不同類型的文本比較操作。

text_ops (默認運算符類)

• 使用數據庫的默認排序規則(LC_COLLATE)
• 支持所有標準的文本比較操作(=, <, >, <=, >=)
• 適用于常規的相等性檢查和排序操作
• 對于使用 LIKE 或正則表達式等模式匹配操作的查詢效率較低

text_pattern_ops

• 忽略語言環境特定的排序規則，使用簡單的逐字符比較
• 專門優化了以 LIKE 或 ~ 開頭的模式匹配查詢
• 特別適合前綴搜索(如 column LIKE 'abc%')
• 不支持常規的 <, > 等比較操作
• 不適用于需要遵循語言特定排序規則的查詢

使用場景示例

-- 使用默認的 text_ops (適合常規比較)
CREATE INDEX idx_name ON users (name);-- 使用 text_pattern_ops (適合模式匹配)
CREATE INDEX idx_name_pattern ON users (name text_pattern_ops);

注意事項

1. 如果查詢混合了常規比較和模式匹配，可能需要創建兩個索引
2. text_pattern_ops 索引對于 LIKE '%suffix' 這樣的后綴搜索沒有幫助
3. 對于不區分大小寫的模式匹配，考慮使用 citext 類型或表達式索引

選擇哪種運算符類取決于具體查詢模式。如果主要進行前綴搜索或模式匹配，text_pattern_ops 會提供更好的性能。

確認指定索引的運算符類別

relname輸入實際的索引名稱，通過查詢結果可知當前的handle_user_name索引的運算符類別為默認的text_ops

SELECTi.relname AS index_name,a.attname AS column_name,opc.opcname AS operator_class
FROMpg_index xJOIN pg_class i ON i.oid = x.indexrelidJOIN pg_attribute a ON a.attrelid = x.indrelid AND a.attnum = ANY(x.indkey)JOIN pg_opclass opc ON opc.oid = ANY(x.indclass)
WHEREi.relname = 'order_log_handle_user_name_index';

index_name	column_name	operator_class
order_log_handle_user_name_index	handle_user_name	text_ops

修改運算符類別為text_pattern_ops

-- 刪除舊索引
DROP INDEX IF EXISTS order_log_handle_user_name_index;-- 創建新索引(使用text_pattern_ops)
CREATE INDEX order_log_handle_user_name_index ON t_common_work_order_log (handle_user_name text_pattern_ops);

回退sql

-- 刪除舊索引
DROP INDEX IF EXISTS order_log_handle_user_name_index;-- 創建新索引(使用text_pattern_ops)
CREATE INDEX order_log_handle_user_name_index ON t_common_work_order_log (handle_user_name text_ops);

驗證

EXPLAIN ANALYZE驗證

修改運算符類別為text_pattern_ops再次執行EXPLAIN ANALYZE，可知LIKE右模糊查詢索引生效

查詢速度對比

計算方法：查詢10次，去掉最大和最小取平均值

默認運算符類別 3.585s

-- 3.510s 3.722s 3.485s 3.732s 3.478s 3.558s 3.729s 3.511s 3.599s 3.564s
SELECT *
FROMt_common_work_order_log 
WHEREhandle_user_name LIKE'張秀%';

text_pattern_ops運算符類別 2.116s

-- 1.753s 2.296s 2.102s 2.159s 2.167s 2.055s 2.048s 2.169s 2.334s 1.934s
SELECT *
FROMt_common_work_order_log 
WHEREhandle_user_name LIKE'張秀%';