我有一个用户表，列键上有一个text_pattern_ops索引。问题是键列中的数据包含需要转义的下划线。有两种方法（据我所知）转义下划线，其中只有一种实际使用了索引。有人能解释为什么会这样吗？
我已经粘贴了下面两个查询的解释分析结果。
查询1：

EXPLAIN ANALYZE
select distinct userid from user
where userstatus IN ('Active')
and ( key like E'999999999_434153_%' or parentid = 434153) ;

字符串
查询计划：

HashAggregate  (cost=340685.17..340687.84 rows=267 width=4) (actual time=22678.760..22678.760 rows=0 loops=1)
  ->  Seq Scan on user  (cost=0.00..340684.50 rows=267 width=4) (actual time=22678.754..22678.754 rows=0 loops=1)
        Filter: (((userstatus)::text = 'Active'::text) AND (((key)::text ~~ '999999999_434153_%'::text) OR (parentid = 434153)))
Total runtime: 22678.879 ms

型
查询二：

EXPLAIN ANALYZE
select distinct userid from user
where userstatus IN ('Active')
and ( key like '999999999\\_434153\\_%' or parentid = 434153) ;

型
生成警告：

WARNING:  nonstandard use of \\ in a string literal
LINE 1: ...userstatus IN ('Active') and ( key like '999999999...
                                                             ^
HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.

型
查询计划：

HashAggregate  (cost=344.50..347.17 rows=267 width=4) (actual time=226.127..226.127 rows=0 loops=1)
  ->  Bitmap Heap Scan on user  (cost=11.09..343.83 rows=267 width=4) (actual time=226.123..226.123 rows=0 loops=1)
        Recheck Cond: (((key)::text ~~ '999999999\\_434153\\_%'::text) OR (parentid = 434153))
        Filter: (((userstatus)::text = 'Active'::text) AND (((key)::text ~~ '999999999\\_434153\\_%'::text) OR (parentid = 434153)))
        ->  BitmapOr  (cost=11.09..11.09 rows=84 width=0) (actual time=226.121..226.121 rows=0 loops=1)
              ->  Bitmap Index Scan on user_key_idx  (cost=0.00..5.44 rows=1 width=0) (actual time=145.758..145.758 rows=0 loops=1)
                    Index Cond: (((key)::text ~>=~ '999999999_434153_'::text) AND ((key)::text ~<~ '999999999_434153`'::text))
              ->  Bitmap Index Scan on user_parentid_key1  (cost=0.00..5.52 rows=84 width=0) (actual time=80.358..80.358 rows=0 loops=1)
                    Index Cond: (parentid = 434153)
Total runtime: 226.256 ms

型