数据库开发者社区 > 正文

PostgreSQL 模糊查询+大量重复值匹配 实践 - 分区索引 = any (array())

简介:
+关注继续查看

标签

PostgreSQL , 模糊查询 , exists , 重复值 , = any (array())


背景

在一些场景中,可能需要对大量的数据TBL_A进行A字段模糊查询,同时将匹配记录的另一个字段B,作为条件输入项,匹配另一张表TBL_B,输出另一张表TBL_B的匹配记录。

例子

小表,表示每个ID的详细信息

create table a (  
  id int primary key,  
  xx text  
);  
  
insert into a select id,'test'||id from generate_series(1,100000) t(id);  

大表,表示FEED日志,一个AID可能产生很多条记录

create table b (  
  aid int,  
  info text  
);  

写入1000万数据

insert into b select random()*9, md5(random()::text) from generate_series(1,10000000);  
  
create extension pg_trgm;  
  
create index idx_b_2 on b using gin (info gin_trgm_ops);  

如果需要用户需要输入AID字段的条件,则建议用这样的复合索引

create extension btree_gin;  
  
create index idx_b_1 on b using gin (aid, info gin_trgm_ops);  

查询info包含某个字符串(前后模糊查询)的数据,找出它们的AID对应的详细信息。

方法1、

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from a where id = any (array(  
  select aid from b where info ~~ '%abc%'  
));  
                                                             QUERY PLAN                                                                
-------------------------------------------------------------------------------------------------------------------------------------  
 Index Scan using a_pkey on public.a  (cost=22849.95..22865.95 rows=10 width=13) (actual time=72.259..72.267 rows=9 loops=1)  
   Output: a.id, a.xx  
   Index Cond: (a.id = ANY ($0))  
   Buffers: shared hit=20209  
   InitPlan 1 (returns $0)  
     ->  Bitmap Heap Scan on public.b  (cost=796.03..22849.65 rows=101010 width=4) (actual time=15.562..61.768 rows=73204 loops=1)  
           Output: b.aid  
           Recheck Cond: (b.info ~~ '%abc%'::text)  
           Heap Blocks: exact=20180  
           Buffers: shared hit=20188  
           ->  Bitmap Index Scan on idx_b_1  (cost=0.00..770.78 rows=101010 width=0) (actual time=12.658..12.658 rows=73204 loops=1)  
                 Index Cond: (b.info ~~ '%abc%'::text)  
                 Buffers: shared hit=8  
 Planning Time: 0.157 ms  
 Execution Time: 72.315 ms  
(15 rows)  

方法2、

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from a where exists (select 1 from b where info ~~ '%abc%' and a.id=b.aid);  
                                                                  QUERY PLAN                                                                     
-----------------------------------------------------------------------------------------------------------------------------------------------  
 Merge Join  (cost=23102.75..23103.04 rows=10 width=13) (actual time=74.316..74.325 rows=9 loops=1)  
   Output: a.id, a.xx  
   Inner Unique: true  
   Merge Cond: (a.id = b.aid)  
   Buffers: shared hit=20191  
   ->  Index Scan using a_pkey on public.a  (cost=0.29..1719.49 rows=100000 width=13) (actual time=0.018..0.021 rows=10 loops=1)  
         Output: a.id, a.xx  
         Buffers: shared hit=3  
   ->  Sort  (cost=23102.44..23102.47 rows=10 width=4) (actual time=74.291..74.292 rows=10 loops=1)  
         Output: b.aid  
         Sort Key: b.aid  
         Sort Method: quicksort  Memory: 25kB  
         Buffers: shared hit=20188  
         ->  HashAggregate  (cost=23102.18..23102.28 rows=10 width=4) (actual time=74.279..74.281 rows=10 loops=1)  
               Output: b.aid  
               Group Key: b.aid  
               Buffers: shared hit=20188  
               ->  Bitmap Heap Scan on public.b  (cost=796.03..22849.65 rows=101010 width=4) (actual time=15.935..61.383 rows=73204 loops=1)  
                     Output: b.aid  
                     Recheck Cond: (b.info ~~ '%abc%'::text)  
                     Heap Blocks: exact=20180  
                     Buffers: shared hit=20188  
                     ->  Bitmap Index Scan on idx_b_1  (cost=0.00..770.78 rows=101010 width=0) (actual time=13.027..13.027 rows=73204 loops=1)  
                           Index Cond: (b.info ~~ '%abc%'::text)  
                           Buffers: shared hit=8  
 Planning Time: 0.344 ms  
 Execution Time: 74.380 ms  
(27 rows)  

结果

postgres=# select * from a where id = any (array(  
  select aid from b where info ~~ '%abc%'  
));  
 id |  xx     
----+-------  
  1 | test1  
  2 | test2  
  3 | test3  
  4 | test4  
  5 | test5  
  6 | test6  
  7 | test7  
  8 | test8  
  9 | test9  
(9 rows)  
  
Time: 57.789 ms  

其他方法

1、使用AID+模糊查询,两个条件,实际效果并不好,因为GIN复合索引实际上是内部BITMAP,并不是真正意义上的多颗树结构(分区结构)。

do language plpgsql $$  
declare  
  v_id int;  
  v_xx text;  
begin  
  for v_id,v_xx in select id,xx from a loop  
    perform 1 from b where aid=v_id and info ~~ '%abc%' limit 1;  
    if found then   
      raise notice '%, %', v_id, v_xx;  
    end if;  
  end loop;  
end;  
$$;  

2、如果aid集合较小,并且可以穷举,也可以使用分区索引(多partial index)。

分区索引模拟,如下

nohup psql -c "create index idx_b_3 on b using gin (info gin_trgm_ops) where aid=1;" >/dev/null 2>&1 &  
nohup psql -c "create index idx_b_4 on b using gin (info gin_trgm_ops) where aid=2;" >/dev/null 2>&1 &  
nohup psql -c "create index idx_b_5 on b using gin (info gin_trgm_ops) where aid=3;" >/dev/null 2>&1 &  
nohup psql -c "create index idx_b_6 on b using gin (info gin_trgm_ops) where aid=4;" >/dev/null 2>&1 &  
nohup psql -c "create index idx_b_7 on b using gin (info gin_trgm_ops) where aid=5;" >/dev/null 2>&1 &  
nohup psql -c "create index idx_b_8 on b using gin (info gin_trgm_ops) where aid=6;" >/dev/null 2>&1 &  
nohup psql -c "create index idx_b_9 on b using gin (info gin_trgm_ops) where aid=7;" >/dev/null 2>&1 &  
nohup psql -c "create index idx_b_10 on b using gin (info gin_trgm_ops) where aid=8;" >/dev/null 2>&1 &  
nohup psql -c "create index idx_b_11 on b using gin (info gin_trgm_ops) where aid=9;" >/dev/null 2>&1 &  
nohup psql -c "create index idx_b_12 on b using gin (info gin_trgm_ops) where aid not in (1,2,3,4,5,6,7,8,9);" >/dev/null 2>&1 &  

查询语句模拟如下

select * from a where id = any (array(  
select * from (select aid from b where aid=1 and info ~~ '%abc%' limit 1) t  
union all  
select * from (select aid from b where aid=2 and info ~~ '%abc%' limit 1) t  
union all  
select * from (select aid from b where aid=3 and info ~~ '%abc%' limit 1) t  
union all  
select * from (select aid from b where aid=4 and info ~~ '%abc%' limit 1) t  
union all  
select * from (select aid from b where aid=5 and info ~~ '%abc%' limit 1) t  
union all  
select * from (select aid from b where aid=6 and info ~~ '%abc%' limit 1) t  
union all  
select * from (select aid from b where aid=7 and info ~~ '%abc%' limit 1) t  
union all  
select * from (select aid from b where aid=8 and info ~~ '%abc%' limit 1) t  
union all  
select * from (select aid from b where aid=9 and info ~~ '%abc%' limit 1) t  
union all  
select * from (select aid from b where aid not in (1,2,3,4,5,6,7,8,9) and info ~~ '%abc%' limit 1) t  
));  
  
 id |  xx     
----+-------  
  1 | test1  
  2 | test2  
  3 | test3  
  4 | test4  
  5 | test5  
  6 | test6  
  7 | test7  
  8 | test8  
  9 | test9  
(9 rows)  
  
Time: 4.970 ms  

执行计划

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from a where id = any (array(  
select * from (select aid from b where aid=1 and info ~~ '%abc%' limit 1) t  
union all  
select * from (select aid from b where aid=2 and info ~~ '%abc%' limit 1) t  
union all  
select * from (select aid from b where aid=3 and info ~~ '%abc%' limit 1) t  
union all  
select * from (select aid from b where aid=4 and info ~~ '%abc%' limit 1) t  
union all  
select * from (select aid from b where aid=5 and info ~~ '%abc%' limit 1) t  
union all  
select * from (select aid from b where aid=6 and info ~~ '%abc%' limit 1) t  
union all  
select * from (select aid from b where aid=7 and info ~~ '%abc%' limit 1) t  
union all  
select * from (select aid from b where aid=8 and info ~~ '%abc%' limit 1) t  
union all  
select * from (select aid from b where aid=9 and info ~~ '%abc%' limit 1) t  
union all  
select * from (select aid from b where aid not in (1,2,3,4,5,6,7,8,9) and info ~~ '%abc%' limit 1) t  
));  
                                                            QUERY PLAN                                                               
-----------------------------------------------------------------------------------------------------------------------------------  
 Index Scan using a_pkey on public.a  (cost=199.57..215.57 rows=10 width=13) (actual time=2.833..2.842 rows=9 loops=1)  
   Output: a.id, a.xx  
   Index Cond: (a.id = ANY ($0))  
   Buffers: shared hit=55  
   InitPlan 1 (returns $0)  
     ->  Append  (cost=0.00..199.27 rows=10 width=4) (actual time=0.249..2.799 rows=10 loops=1)  
           Buffers: shared hit=34  
           ->  Limit  (cost=0.00..15.31 rows=1 width=4) (actual time=0.249..0.249 rows=1 loops=1)  
                 Output: b.aid  
                 Buffers: shared hit=3  
                 ->  Seq Scan on public.b  (cost=0.00..170791.00 rows=11158 width=4) (actual time=0.247..0.247 rows=1 loops=1)  
                       Output: b.aid  
                       Filter: ((b.info ~~ '%abc%'::text) AND (b.aid = 1))  
                       Rows Removed by Filter: 1131  
                       Buffers: shared hit=3  
           ->  Limit  (cost=0.00..14.75 rows=1 width=4) (actual time=0.243..0.244 rows=1 loops=1)  
                 Output: b_1.aid  
                 Buffers: shared hit=3  
                 ->  Seq Scan on public.b b_1  (cost=0.00..170791.00 rows=11576 width=4) (actual time=0.243..0.243 rows=1 loops=1)  
                       Output: b_1.aid  
                       Filter: ((b_1.info ~~ '%abc%'::text) AND (b_1.aid = 2))  
                       Rows Removed by Filter: 1208  
                       Buffers: shared hit=3  
           ->  Limit  (cost=0.00..15.39 rows=1 width=4) (actual time=0.023..0.023 rows=1 loops=1)  
                 Output: b_2.aid  
                 Buffers: shared hit=1  
                 ->  Seq Scan on public.b b_2  (cost=0.00..170791.00 rows=11098 width=4) (actual time=0.023..0.023 rows=1 loops=1)  
                       Output: b_2.aid  
                       Filter: ((b_2.info ~~ '%abc%'::text) AND (b_2.aid = 3))  
                       Rows Removed by Filter: 70  
                       Buffers: shared hit=1  
           ->  Limit  (cost=0.00..14.96 rows=1 width=4) (actual time=0.738..0.738 rows=1 loops=1)  
                 Output: b_3.aid  
                 Buffers: shared hit=8  
                 ->  Seq Scan on public.b b_3  (cost=0.00..170791.00 rows=11414 width=4) (actual time=0.738..0.738 rows=1 loops=1)  
                       Output: b_3.aid  
                       Filter: ((b_3.info ~~ '%abc%'::text) AND (b_3.aid = 4))  
                       Rows Removed by Filter: 3563  
                       Buffers: shared hit=8  
           ->  Limit  (cost=0.00..15.18 rows=1 width=4) (actual time=0.068..0.068 rows=1 loops=1)  
                 Output: b_4.aid  
                 Buffers: shared hit=1  
                 ->  Seq Scan on public.b b_4  (cost=0.00..170791.00 rows=11253 width=4) (actual time=0.068..0.068 rows=1 loops=1)  
                       Output: b_4.aid  
                       Filter: ((b_4.info ~~ '%abc%'::text) AND (b_4.aid = 5))  
                       Rows Removed by Filter: 311  
                       Buffers: shared hit=1  
           ->  Limit  (cost=0.00..15.09 rows=1 width=4) (actual time=0.178..0.178 rows=1 loops=1)  
                 Output: b_5.aid  
                 Buffers: shared hit=2  
                 ->  Seq Scan on public.b b_5  (cost=0.00..170791.00 rows=11316 width=4) (actual time=0.178..0.178 rows=1 loops=1)  
                       Output: b_5.aid  
                       Filter: ((b_5.info ~~ '%abc%'::text) AND (b_5.aid = 6))  
                       Rows Removed by Filter: 894  
                       Buffers: shared hit=2  
           ->  Limit  (cost=0.00..15.50 rows=1 width=4) (actual time=0.323..0.323 rows=1 loops=1)  
                 Output: b_6.aid  
                 Buffers: shared hit=4  
                 ->  Seq Scan on public.b b_6  (cost=0.00..170791.00 rows=11020 width=4) (actual time=0.322..0.322 rows=1 loops=1)  
                       Output: b_6.aid  
                       Filter: ((b_6.info ~~ '%abc%'::text) AND (b_6.aid = 7))  
                       Rows Removed by Filter: 1599  
                       Buffers: shared hit=4  
           ->  Limit  (cost=0.00..15.25 rows=1 width=4) (actual time=0.449..0.449 rows=1 loops=1)  
                 Output: b_7.aid  
                 Buffers: shared hit=5  
                 ->  Seq Scan on public.b b_7  (cost=0.00..170791.00 rows=11199 width=4) (actual time=0.448..0.448 rows=1 loops=1)  
                       Output: b_7.aid  
                       Filter: ((b_7.info ~~ '%abc%'::text) AND (b_7.aid = 8))  
                       Rows Removed by Filter: 2231  
                       Buffers: shared hit=5  
           ->  Limit  (cost=0.00..32.33 rows=1 width=4) (actual time=0.130..0.131 rows=1 loops=1)  
                 Output: b_8.aid  
                 Buffers: shared hit=2  
                 ->  Seq Scan on public.b b_8  (cost=0.00..170791.00 rows=5283 width=4) (actual time=0.130..0.130 rows=1 loops=1)  
                       Output: b_8.aid  
                       Filter: ((b_8.info ~~ '%abc%'::text) AND (b_8.aid = 9))  
                       Rows Removed by Filter: 623  
                       Buffers: shared hit=2  
           ->  Limit  (cost=0.00..45.36 rows=1 width=4) (actual time=0.392..0.392 rows=1 loops=1)  
                 Output: b_9.aid  
                 Buffers: shared hit=5  
                 ->  Seq Scan on public.b b_9  (cost=0.00..258291.00 rows=5694 width=4) (actual time=0.392..0.392 rows=1 loops=1)  
                       Output: b_9.aid  
                       Filter: ((b_9.info ~~ '%abc%'::text) AND (b_9.aid <> ALL ('{1,2,3,4,5,6,7,8,9}'::integer[])))  
                       Rows Removed by Filter: 1931  
                       Buffers: shared hit=5  
 Planning Time: 1.411 ms  
 Execution Time: 2.963 ms  
(89 rows)  

可见,分区索引,效果是比较好的。期待PG尽快支持。

另外,对于本例,用户需要IN很多值时,建议使用较大的work_mem,同时使用any(array())的写法。

work_mem='4MB'  

参考

《HTAP数据库 PostgreSQL 场景与性能测试之 25 - (OLTP) IN , EXISTS 查询》

《HTAP数据库 PostgreSQL 场景与性能测试之 26 - (OLTP) NOT IN、NOT EXISTS 查询》

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
4 PostgreSQL 索引,全文检索,模糊匹配,近似度匹配(三)
4 PostgreSQL 索引,全文检索,模糊匹配,近似度匹配(三)
344 0
认识 PostgreSQL 中与众不同的索引(一)|学习笔记
快速学习认识 PostgreSQL 中与众不同的索引(一)
113 0
postgresql模糊查询过滤首尾空格
此处不讨论模糊查询的方法(like、bind等),只针对如何过滤前后字符串
92 0
postgresql模糊查询不区分大小写
本人当时找了无数文章都没解决,后面才发现pg有ilike这个关键字,书读少了好吃亏的(小声逼逼) 除此之外还可以通过upper()函数来搞
306 0
【重新发现PostgreSQL之美】- 16 like '%西出函谷关%' 模糊查询
大家好,这里是重新发现PostgreSQL之美 - 16 like '%西出函谷关%' 模糊查询
208 0
PostgreSQL 模糊查询最佳实践 - (含单字、双字、多字模糊查询方法)
PostgreSQL 模糊查询最佳实践 - (含单字、双字、多字模糊查询方法)https://github.com/digoal/blog/blob/master/201704/20170426_01.md
12532 0
用PostgreSQL 做实时高效 搜索引擎 - 全文检索、模糊查询、正则查询、相似查询、ADHOC查询
用PostgreSQL 做实时高效 搜索引擎 - 全文检索、模糊查询、正则查询、相似查询、ADHOC查询作者digoal 日期2017-12-05 标签PostgreSQL , 搜索引擎 , GIN , ranking , high light , 全文检索 , 模糊查询 , 正则查询 , 相似查询 , ADHOC查询 背景字符串搜索是非常常见的业务需求,它包括: 1、前缀+模糊查询。
8120 0
数据库领域前沿技术分享与交流
+关注
德哥
公益是一辈子的事, I am digoal, just do it.
文章
问答
视频
相关电子书
更多
PostgreSQL 物联网六脉神剑
立即下载
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
相关镜像