udb添加索引时报错

报错如下：

ERROR: index row size 2728 exceeds btree version4 maximun2704 for index "idx_app"
DETAIL: Index row references tuple(508199,31) in relation 'unify_work_extend_value'.
HINT: Values larger than 1/3 of a buffer page cannot be indexed.
Consider a function index of an MD5 hash of the value,or use full text indexing.

 

首先看一下SQL和表结构：

explain
SELECT COUNT
( 1 ) AS totalCount,
SUM ( CASE WHEN wo.order_status IN ( '0', '1' ) THEN 1 ELSE 0 END ) AS waitAllocateCount,
SUM ( CASE WHEN wo.order_status IN ( '2', '3' ) THEN 1 ELSE 0 END ) AS processingCount,
SUM ( CASE WHEN wo.order_status IN ( '4', '5', '7' ) THEN 1 ELSE 0 END ) AS waitAuditCount,
SUM ( CASE WHEN wo.order_status IN ( '8', '9' ) THEN 1 ELSE 0 END ) AS finishCount,
COALESCE ( SUM ( CASE WHEN wo.property_value :: INTEGER > 0 THEN 1 ELSE 0 END ), 0 ) AS superviseCount,
'0' AS expiredCount
FROM
(
WITH work_order AS (
SELECT
wo_code,
order_status
FROM
unify_work_order
WHERE
app_id = 'wghxtjs'
AND state_time >= to_timestamp( '2023-10-01', 'YYYY-MM-DD HH24:MI:SS' )
AND state_time <= to_timestamp( '2023-11-01', 'YYYY-MM-DD HH24:MI:SS' )
AND del_flag = '0'
) SELECT
tmp.order_status,
dbcs.property_value
FROM
work_order tmp
LEFT JOIN ( SELECT wo_code, property_value FROM unify_work_extend_value WHERE app_id = 'wghxtjs' AND property_code = 'DuBanCiShu' ) dbcs ON tmp.wo_code = dbcs.wo_code
) wo

 

 添加app_id  property_code wo_code property_value) / length(所有入参字节总和)  > 1/3

则认为组合索引长度太大，就数据库就放弃检索组合索引。

解决办法，参看：

Consider a function index of an MD5 hash of the value, or use full text indexing.