圈子推荐
查看所有吧>>
活跃用户
    vastbase-GIN索引在数组中的使用

    1、概述

         GIN(Generalized Inverted Index, 通用倒排索引),被设计为处理被索引项为组合值的情况,并且这种索引所处理的查询需要搜索出现在组合项中的元素值。例如,项可以是文档,并且查询可以是搜索包含指定词的文档。

         我们使用词项来表示要被索引的一个组合值,并且用词键来表示一个元素值。GIN总是存储和搜索键,而不是项值本身。

         一个GIN存储一个(key, posting list)对的***,其中key是一个键值,而posting list是键在其中出现的一个行 ID 的***。在表中的每一个属性,在建立索引时,都可能会被解析为多个键值,所以相同的行id可能会出现在多个key的posting list中。

         通过这种索引结构可以快速的查找到包含指定关键字的元组,因此GIN索引特别适用于多值类型的元素搜索,比如支持全文搜索,数组中元素的搜索。

         GIN访问方法代码不需要知道它所加速的是什么操作,从这个意义上来说,GIN是通用的。相反,它使用为特定数据类型定义的自定义策略。策略定义如何从被索引项和查询条件中抽取键,并且如何决定一个包含查询中某些键值的行是否真正满足查询。

         GIN的一个优点是它允许由数据类型的领域专家开发有合适访问方法的自定义数据类型,而不是让一个数据库专家来做这件事。在这一点上很像GiST。

    2、内建GIN操作符类

    PostgreSQL的核心发布包括表 中所示的GIN操作符类,除此之外,GIN还支持一些可选模块,比如pg_trgm等都提供了额外的GIN操作符类。


    名称-数据类型-可索引操作符

    array_ops--anyarray--&&、       

    jsonb_ops--jsonb--?、?&、?|、@>      

    jsonb_path_ops--jsonb--@>      

    tsvector_ops--tsvector--@@、@@@


    在两种用于类型jsonb的操作符类中,jsonb_ops是默认项。 jsonb_path_ops支持较少的操作符但是为那些操作符提供了更好的性能。

    3、数组中元素的搜索性能提升-GIN索引使用

    3.1.创建测试表

    CREATE TABLE array_test(
    id int primary key,
    name varchar(32),
    phone varchar(32)[],
    regdate timestamp without time zone default clock_timestamp());

    3.2.造100万测试数据

    insert into array_test(id,name,phone) select n,n||'_test',array[n+18200000000,n+18200000001] from generate_series(1, 2000000, 2) n;

    3.3.查询手机号包含18200008888的信息

    explain ***yze SELECT * FROM array_test WHERE phone @> array['18200008888'::varchar(32)];
    vastbase=# explain ***yze SELECT * FROM array_test WHERE phone @> array['18200008888'::varchar(32)];
                                                              QUERY PLAN                                                          
    --------------------------------------------------------------------------------------------------------------------------------
    Gather  (cost=1000.00..12926.02 rows=4363 width=126) (actual time=2.152..203.184 rows=1 loops=1)
      Number of Workers: 2
      ->  Parallel Seq Scan on array_test  (cost=0.00..11489.72 rows=1818 width=126) (actual time=364.670..565.696 rows=1 loops=3)
            Filter: (phone @> '{18200008888}'::varchar(32)[])
            Rows Removed by Filter: 999999
    Total runtime: 210.341 ms
    (6 rows)
    经过多次查询,查询时间基本保持在100ms-300ms之间,且走的是并行全表扫。

    3.4.在数组phone上创建GIN索引,再次查看执行计划

    ##通过concurrently选项实现非阻塞方式添加索引
    vastbase=# CREATE INDEX concurrently idx_array_test_phone on array_test using gin(phone);
    CREATE INDEX
    vastbase=# d+ array_test
                                            Table "public.array_test"
    Column  |            Type             |         Modifiers         | Storage  | Stats target | Description
    ---------+-----------------------------+---------------------------+----------+--------------+-------------
    id      | integer                     | not null                  | plain    |              |
    name    | varchar(32)                 |                           | extended |              |
    phone   | varchar(32)[]               |                           | extended |              |
    regdate | timestamp without time zone | default clock_timestamp() | plain    |              |
    Indexes:
       "array_test_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default
       "idx_array_test_phone" gin (phone) TABLESPACE pg_default
    Has OIDs: no
    Options: orientation=row, compression=no

    vastbase=# explain ***yze SELECT * FROM array_test WHERE phone @> array['18200008888'::varchar(32)];
                                                              QUERY PLAN                                                            
    ---------------------------------------------------------------------------------------------------------------------------------
    Bitmap Heap Scan on array_test  (cost=70.75..10862.91 rows=5000 width=77) (actual time=0.021..0.021 rows=1 loops=1)
      Recheck Cond: (phone @> '{18200008888}'::varchar(32)[])
      ->  Bitmap Index Scan on idx_array_test_phone  (cost=0.00..69.50 rows=5000 width=0) (actual time=0.016..0.016 rows=1 loops=1)
            Index Cond: (phone @> '{18200008888}'::varchar(32)[])
    Total runtime: 0.118 ms
    (5 rows)
    可以看到走的是位图索引扫描,经过多次查询,查询时间也稳定在0.1ms-0.4ms之间,相比未加gin索引之前,性能提升了1000倍左右。


    参考文档:http://www.postgres.cn/docs/11/gin-builtin-opclasses.html

    • 分享到:
    排序方式:回复时间 共有0条评论