postgresql 数据库数组索引的初步使用

今天药忘吃喽~ 2022-11-15 11:22 298阅读 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(100)[]
    );
    
    
    insert into tmp_array
    (id,name)
    select mod(id,9999) as m_id,
    	   array_agg(id::varchar) as m_id_agg
      from generate_series(1,1000000) as id
     group by mod(id,9999)
    ;

create index idx_tmp_array_x1 on tmp_array using gin(name);

explain ANALYZE
    select *
    from tmp_array ta
    where 1=1
    and name @> array['113'::varchar]
    ;

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

[20210331153517397.png]: /images/20221022/34565d3bc4cd43849ab1ebcde770bd30.png