pg库coalesce怎么走索引 pg数据库索引类型

pg库coalesce怎么走索引 pg数据库索引类型

目录

PG索引类型

索引类型

CREATE INDEX 在一个指定表或者物化视图的指定列上创建一个索引,索引主要用来提高数据库的效率(尽管不合理的使用将导致较慢的效率)

索引特性

  • 只有B-tree，GiST，GIN和BRIN索引类型支持多列索引。最多可以指定32列。使用最左匹配原则。
  • 在PostgreSQL当前支持的索引类型中，只有B-tree可以产生排序的输出，当ORDER BY与LIMIT n组合：显式排序将必须处理所有数据以识别前n行，但如果存在与ORDER BY匹配的索引，则可以直接检索前n行，而不扫描其余部分。升序默认null值放在最后，可以使用NULLS FIRST和/或NULLS LAST选项来进行调整。
  • PostgreSQL可以为表达式的结果创建索引，但是该索引维护代价太大，因为每当插入或者更新时，表达式都需要重新计算。
  • PostgreSQL支持对表中部分数据建立索引，使用部分索引的一个主要原因是避免索引常见值。由于搜索常见值的查询将不会使用索引，所以根本没有必要在索引中保留这些行，这样可以直接排除掉一部分数据，减少了索引的大小，性能更快。
  • PostgreSQL支持仅索引扫描，当要查询的目标列都在索引中时，直接使用索引中的键值进行返回，不需要回表操作。

btree

• =, >, >=, <, <=、排序

  • 选择性越好(唯一值个数接近记录数)的列，越适合b-tree。
  • 当被索引列存储相关性越接近1或-1时，数据存储越有序，范围查询扫描的HEAP PAGE越少。
  • 支持多列索引，默认最多32列，编译可改。（通过调整pg_config_manual.h可以做到更大，但是还有另一个限制，indextuple不能超过约1/4的数据块（索引页）大小，也就是说复合索引列很多的情况下，可能会触发这个限制）
  • 支持唯一索引。
  • 索引策略 ： <,<=,=,>=,>

PostgreSQL B-Tree是一种变种(high-concurrency B-tree management algorithm)，算法详情请参考src/backend/access/nbtree/README

可以使用pageinspect插件，内窥B-Tree的结构。

组合索引

虽然b-tree多列索引支持任意列的组合查询，但是最有效的查询还是包含驱动列条件的查询。

对于b-tree的多列索引来说，一个查询要扫描索引的哪些部分呢？

  • 从驱动列开始算，按索引列的顺序算到非驱动列的第一个不相等条件为止（没有任何条件也算）。
（WHERE a = 5 AND b >= 42 AND c < 77），从a=5, b=42开始的所有索引条目，都会被扫描。
  • 其他例子
（WHERE b >= 42 AND c < 77），所有索引条目，都会被扫描。只要不包含驱动列，则扫描所有索引条目。
（WHERE a = 5 AND c < 77），a=5的所有索引条目，都会被扫描。
（WHERE a >= 5 AND b=1 and c < 77），从a=5开始的所有索引条目，都会被扫描。

hash

• =
只有等值查询，并且被索引的列长度很长，可能超过数据库block的1/3时，建议使用hash索引。 PG 10 hash索引会产生WAL，确保了可靠性，同时支持流复制。PG 10 以前的版本，不建议使用hash index，crash后需要rebuild，不支持流复制。

  • 不支持多列索引。
  • 不支持唯一索引。
  • 索引策略 ：=

原理src/backend/access/hash/README

应用场景
  • hash索引存储的是被索引字段VALUE的哈希值，只支持等值查询。
  • hash索引特别适用于字段VALUE非常长（不适合b-tree索引，因为b-tree一个PAGE至少要存储3个ENTRY，所以不支持特别长的VALUE）的场景，例如很长的字符串，并且用户只需要等值搜索，建议使用hash index。
  • PG10之后的版本hash index会写wal日志了，同时支持流复制。

gist

• 多值类型(数组、全文检索、枚举、网络地址类型)：包含、相交
• JSON类型
• 普通类型(通过btree_gin 插件支持)：与B-Tree类似
• 字符串(通过pg_trgm 插件支持)：模糊查询、相似查询
• 多列：任意列组合查询

GIST是PG的一种通用索引接口，适合各种数据类型，特别适合异构的类型，例如几何类型，空间类型，范围类型等。

  • 支持多列索引，默认最多32列，编译可改。
  • 不支持唯一索引。
  • 索引策略 ：
两维R-tree策略
严格地在...左边,
            不扩展到...右边,
            重叠,
            不延伸到...左边,
            严格地在...右边,
            相同,
            包含,
            包含于,
            不扩展到...上面,
            严格地在...下面,
            严格地在...上面,
            不扩展到...下面
  • 独有参数 BUFFERING

建立索引时决定是否缓存建立的方法.使用OFF 关闭这个功能,ON 打开这个功能. 使用AUTO 它初始化时是关闭的,但是当索引的达到effective_cache_size将 会打开.默认使用AUTO.

GiST是一个通用的索引接口，可以使用GiST实现b-tree, r-tree等索引结构。

不同的类型，支持的索引检索也各不一样。例如：

  • 1、几何类型，支持位置搜索（包含、相交、在上下左右等），按距离排序。
  • 2、范围类型，支持位置搜索（包含、相交、在左右等）。
  • 3、IP类型，支持位置搜索（包含、相交、在左右等）。
  • 4、空间类型（PostGIS），支持位置搜索（包含、相交、在上下左右等），按距离排序。
  • 5、标量类型，支持按距离排序。

SPGiST

• 平面几何类型：与GiST类似
• 范围类型：与GiST类似

RUM

• 多值类型(数组、全文检索类型)：包含、相交、相似排序
• 普通类型：与B-Tree类似

BRIN

• 适合线性数据、时序数据，block ranged index是oracle一体机中才有的功能。
• 普通类型：与B-Tree类似
• 空间类型：包含

Bloom

• 多列：任意列组合，等值查询
• 表达式索引
• 搜索条件为表达式
• where st_makepoint(x,y) op ?
• create index idx on tbl ( (st_makepoint(x,y)) );
• 条件索引(定向索引)
• 搜索时，强制过滤某些条件
• where status='active' and col=?
• create index idx on tbl (col) where status='active';
• 监控系统例子select x from tbl where temp>60; -- 99, 1% 异常数据

posted @ 2026-05-12 17:15  数据库小白（专注）  阅读(16)  评论(0)    收藏  举报