索引——btree/brin

btree

• 默认的索引，全称 B树

• 存储结构: 二叉树

  • 按照索引列从大到小排序，每一个分别挂在树的节点上
  • log(n)的查找速度

• 【推荐】构建方式1——不影响生产环境，但偏慢

  CREATE INDEX CONCURRENTLY practice_stime_idx 
  ON public.practice USING btree(stime);
  

  • 关键是加入CONCURRENTLY参数

• 构建方式2——会锁表，但较快

  CREATE INDEX practice_stime_idx 
  ON public.practice USING btree(stime);
  

  • 加快索引的方式，暂时增大maintenance_work_mem

    BEGIN;
    SET LOCAL maintenance_work_mem='1GB';
    CREATE INDEX xxx
    COMMIT;
    

    • 对于千万级的大表，起码要准备4到8GB的内存
      • 一组数据:7000万行的表，仅使用2GB内存，需要2分多钟，而且途中会锁表，导致大量事务处于锁等待状态!!!

• 支持组合索引

  • 构建方式

    CREATE INDEX CONCURRENTLY practice_user_uuid_stime_idx 
    ON public.practice USING btree(user_uuid, stime);
    

brin

• 块级索引

• 存储结构:

  • 并不是以行号为单位记录
  • 而是记录每段连续的数据块

• 好处:

  • 占用空间特别小
  • 数据写入和更新的影响也很小

• 适用于对ctime这种时序数据列，进行范围查询的场景，陆续按时间插入，与物理存储的相关性很强，强烈推荐