postgresql 数据库数组索引的一次特殊使用

太过爱你忘了你带给我的痛 2022-11-15 11:18 115阅读 0赞

os: centos 7.6.1810  
db: postgresql 10

# 版本 #

# cat /etc/centos-release
    CentOS Linux release 7.6.1810 (Core) 
    
    # yum list installed |grep -i postgresql
    postgresql10.x86_64                10.16-1PGDG.rhel7                   @pgdg10  
    postgresql10-contrib.x86_64        10.16-1PGDG.rhel7                   @pgdg10  
    postgresql10-devel.x86_64          10.16-1PGDG.rhel7                   @pgdg10  
    postgresql10-docs.x86_64           10.16-1PGDG.rhel7                   @pgdg10  
    postgresql10-libs.x86_64           10.16-1PGDG.rhel7                   @pgdg10  
    postgresql10-odbc.x86_64           13.00.0000-1PGDG.rhel7              @pgdg10  
    postgresql10-plperl.x86_64         10.16-1PGDG.rhel7                   @pgdg10  
    postgresql10-plpython.x86_64       10.16-1PGDG.rhel7                   @pgdg10  
    postgresql10-plpython3.x86_64      10.16-1PGDG.rhel7                   @pgdg10  
    postgresql10-pltcl.x86_64          10.16-1PGDG.rhel7                   @pgdg10  
    postgresql10-server.x86_64         10.16-1PGDG.rhel7                   @pgdg10  
    postgresql10-tcl.x86_64            2.7.5-1.rhel7                       @pgdg10  
    postgresql10-test.x86_64           10.16-1PGDG.rhel7                   @pgdg10

# 测试 #

drop table if exists tmp_array;
    
    create table tmp_array (
      id   int8,
      name varchar(2000)
    );
    
    
    insert into tmp_array
    (id,name)
    select mod(id,10000) as m_id,
    	   string_agg(id::varchar,',') as m_id_agg
      from generate_series(1,1000000) as id
     group by mod(id,10000)
    ;

create index idx_tmp_array_x1 on tmp_array using gin(regexp_split_to_array(name,E','));

explain ANALYZE
    select *
    from tmp_array ta
    where 1=1
    and regexp_split_to_array(name,E',') @> array['123']
    ;

![在这里插入图片描述][2021033115040862.png]

[2021033115040862.png]: /images/20221022/7863640763524d1c88b7c8a705d93032.png