我可以使用带有变量字符串的postgres text\u pattern\u ops索引吗？

我正在改进postgres文本搜索查询，以确保尽可能使用所有可用的索引。我在一个文本列上有一个text\u pattern\u obs btree索引，该索引使用了一个简单的查询，如：

set enable_seqscan = off; -- ensure that indexes are used even on small tables for this demo

select * from search_table where text_column like 'example'

^^这给了我一个使用适当索引的超快速查询。
但是，当我想用一个字符串值替换“example”文本时，该字符串值是前一个函数或select的结果，它将恢复为完整的顺序表扫描。

with user_input as (select 'example%' as query_string)
select * from search_table, user_input where text_column like query_string

^^这太慢了
我试过键入casting query\u string：：text等，但没有成功。我不能直接将字符串插入到sql中，因为在其他查询执行之前它是未知的。
我使用的是postgresv11，所以应该有所有最新的查询计划聪明可用。

尝试使用准备好的语句而不是联接：
使用：

create table t(x int, y text);
create index fts on t(y text_pattern_ops);
insert into t values(1, 'example');
insert into t values(2, 'examples');
insert into t values(3, 'aaa');
insert into t values(4, 'zzz zzz');
analyze t;
set enable_seqscan = off;

我有：

select * from t;
     x |    y     
    ---+----------
     1 | example
     2 | examples
     3 | aaa
     4 | zzz zzz
    (4 rows)

   explain analyze select * from t where y like 'example';
                                               QUERY PLAN                                               
--------------------------------------------------------------------------------------------------------
 Index Scan using fts on t  (cost=0.13..8.15 rows=1 width=11) (actual time=0.012..0.013 rows=1 loops=1)
   Index Cond: (y = 'example'::text)
   Filter: (y ~~ 'example'::text)
 Planning Time: 0.111 ms
 Execution Time: 0.030 ms
(5 rows)

explain analyze select * from t where y like 'example%';
                                               QUERY PLAN                                               
--------------------------------------------------------------------------------------------------------
 Index Scan using fts on t  (cost=0.13..8.15 rows=1 width=11) (actual time=0.006..0.007 rows=2 loops=1)
   Index Cond: ((y ~>=~ 'example'::text) AND (y ~<~ 'examplf'::text))
   Filter: (y ~~ 'example%'::text)
 Planning Time: 0.140 ms
 Execution Time: 0.014 ms
(5 rows)

explain analyze with user_input as (select 'example%' as query_string)
select * from t, user_input where y like query_string;
                                                      QUERY PLAN                                                  

------------------------------------------------------------------------------------------------------------------
----
 Nested Loop  (cost=10000000000.01..10000000001.15 rows=1 width=43) (actual time=0.008..0.011 rows=2 loops=1)
   Join Filter: (t.y ~~ user_input.query_string)
   Rows Removed by Join Filter: 2
   CTE user_input
     ->  Result  (cost=0.00..0.01 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=1)
   ->  Seq Scan on t  (cost=10000000000.00..10000000001.04 rows=4 width=11) (actual time=0.003..0.003 rows=4 loops
=1)
   ->  CTE Scan on user_input  (cost=0.00..0.02 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=4)
 Planning Time: 0.049 ms
 Execution Time: 0.031 ms
(9 rows)

prepare s1(text) as select * from t where y like $1;
PREPARE
explain analyze execute s1('example%');
                                               QUERY PLAN                                               
--------------------------------------------------------------------------------------------------------
 Index Scan using fts on t  (cost=0.13..8.15 rows=1 width=11) (actual time=0.012..0.014 rows=2 loops=1)
   Index Cond: ((y ~>=~ 'example'::text) AND (y ~<~ 'examplf'::text))
   Filter: (y ~~ 'example%'::text)
 Planning Time: 0.089 ms
 Execution Time: 0.023 ms
(5 rows)

我可以使用带有变量字符串的postgres text\u pattern\u ops索引吗？

1条答案

相关问题

热门标签

最新问答