背景

postgresql拥有众多开放特性，例如

1. 开放的数据类型接口，使得pg支持超级丰富的数据类型，除了传统数据库支持的类型，还支持gis，json，range，ip，isbn，图像特征值，化学，dna等等扩展的类型，用户还可以根据实际业务扩展更多的类型。
2. 开放的操作符接口，使得pg不仅仅支持常见的类型操作符，还支持扩展的操作符，例如 距离符，逻辑并、交、差符号，图像相似符号，几何计算符号等等扩展的符号，用户还可以根据实际业务扩展更多的操作符。
3. 开放的外部数据源接口，使得pg支持丰富的外部数据源，例如可以通过fdw读写mysql，redis，mongo，oracle，sqlserver，hive，www，hbase，ldap，等等只要你能想到的数据源都可以通过fdw接口读写。
4. 开放的语言接口，使得pg支持几乎地球上所有的编程语言作为数据库的函数、存储过程语言，例如plpython，plperl，pljava，plr，plcuda，plshell等等。用户可以通过language handler扩展pg的语言支持。
5. 开放的索引接口，使得pg支持非常丰富的索引方法，例如btree，hash，gin，gist，sp-gist，brin，bloom，rum，zombodb，bitmap（greenplum extend），用户可以根据不同的数据类型，以及查询的场景，选择不同的索引。
6. pg内部还支持bitmapand，bitmapor的优化方法，可以合并多个索引的扫描操作，从而提升多个索引数据访问的效率。

概述

btree

postgresql b-tree是一种变种（high-concurrency b-tree management algorithm）。

btree适合所有的数据类型，支持排序，支持大于、小于、等于、大于或等于、小于或等于的搜索。索引与递归查询结合还能实现快速的稀疏检索。

hash

hash索引存储的是被索引字段value的哈希值，只支持等值查询。hash索引特别适用于字段value非常长（不适合b-tree索引，因为b-tree一个page至少要存储3个entry，所以不支持特别长的value）的场景，例如很长的字符串，并且用户只需要等值搜索，建议使用hash index。

gin

gin是倒排索引，存储被索引字段的value或value的元素，以及行号的list或tree。

• 当需要搜索多值类型内的value时，适合多值类型，例如数组、全文检索、token。（根据不同的类型，支持相交、包含、大于、在左边、在右边等搜索）。
• 当用户的数据比较稀疏时，如果要搜索某个value的值，可以适应btree_gin支持普通btree支持的类型。（支持btree的操作符）。
• 当用户需要按任意列进行搜索时，gin支持多列展开单独建立索引域，同时支持内部多域索引的bitmapand，bitmapor合并，快速的返回按任意列搜索请求的数据。

gist

gist是一个通用的索引接口，可以使用gist实现b-tree，r-tree等索引结构。不同的类型，支持的索引检索也各不一样。

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

sp-gist

sp-gist类似gist，是一个通用的索引接口，但是sp-gist使用了空间分区的方法，使得sp-gist可以更好的支持非平衡数据结构，例如quad-trees，k-d tree，radis tree。

• 几何类型，支持位置搜索（包含、相交、在上下左右等），按距离排序。
• 范围类型，支持位置搜索（包含、相交、在左右等）。
• ip类型，支持位置搜索（包含、相交、在左右等）。

brin

brin索引是块级索引，有别于b-tree等索引，brin记录并不是以行号为单位记录索引明细，而是记录每个数据块或者每段连续的数据块的统计信息。因此brin索引空间占用特别的小，对数据写入、更新、删除的影响也很小。 brin属于lossly索引，当被索引列的值与物理存储相关性很强时，brin索引的效果非常的好。例如时序数据，在时间或序列字段创建brin索引，进行等值、范围查询时效果很棒。

rum

rum是一个索引插件，由postgrespro开源，适合全文检索，属于gin的增强版本。

• 在rum索引中，存储了lexem的位置信息，所以在计算ranking时，不需要回表查询（而gin需要回表查询）。
• rum支持phrase搜索，而gin无法支持。
• 在一个rum索引中，允许用户在posting tree中存储除ctid（行号）以外的字段value，例如时间戳。

这使得rum不仅支持gin支持的全文检索，还支持计算文本的相似度值，按相似度排序等。同时支持位置匹配，例如（速度与激情，可以采用”速度” <2> “激情” 进行匹配，而gin索引则无法做到）。

bloom

bloom索引接口是postgresql基于bloom filter构造的一个索引接口，属于lossy索引，可以收敛结果集（排除绝对不满足条件的结果，剩余的结果里再挑选满足条件的结果），因此需要二次check，bloom支持任意列组合的等值查询。 bloom存储的是签名，签名越大，耗费的空间越多，但是排除更加精准，有利有弊。

zombodb

zombodb是postgresql与elasticsearch结合的一个索引接口，可以直接读写es。

bitmap

bitmap索引是greenplum的索引接口，类似gin倒排，只是bitmap的key是列的值，value是bit（每个bit对应一行），而不是行号list或tree。

当某个字段的唯一值个数在100到10万之间（超出这个范围，不建议使用bitmap）时，如果表的记录数特别多，而且变更不频繁（或者是ao表），那么很适合bitmap索引，bitmap索引可以实现快速的多个或单个value的搜索。 因为只需要对行号的bitmap进行bit与或运算，得到最终的bitmap，从最终的bitmap映射到行进行提取。bitmap与btree一样，都支持等于，大于，小于，大于等于，小于等于的查询。

varbitx

varbitx是阿里云rds的扩展包，丰富bit类型的函数接口，实际上并不是索引接口，但是在postgresql中使用varbitx可以代替bitmap索引，达到同样的效果。

部分索引

postgresql允许用户创建部分索引，create index后面可以跟where子句，限制建索引的条件。

表达式索引

表达式索引也是postgresql特有的特性，例如用户的数据需要转换后查询，例如某些设备上传的地理坐标的坐标系不符合国标，需要转换为国内的空间坐标来查询。 那么可以针对这类字段，创建表达式索引，将转换过程放到表达式中，查询时也使用表达式进行查询。

记录对慢查询sql的优化

1. 表a只创建记录并读取，不会有任何更新操作。业务目的，基于自增id和created_at均会随着时间的流逝而变大并且不可逆（人为操作除外）。
   • 优化方案：order by created_at desc = order by id desc。主键索引比时间（块）索引快得多。
2. 表b原始查询语句，套太多层，并且能大量过滤数据的时间过滤条件放在了最外层。时间是块状索引，每次过滤可以排除大量数据，应该被先使用。
   • 优化方案：时间字段放入内部子查询
3. 在某些表的索引创建时，考虑了耗空间之类。所以在时间索引等方面，选择了brin索引，查询效率不如btree。
   • 优化方案：使用btree索引
4. 查询报错示例：user query might have needed to see row versions that must be removed，这是因为在查询热备库数据时，主库有更新。并且在一定时限内，有冲突的查询未能查询完毕。
   • 优化方案：从热备库统计数据看，从19.5 以来冲突不多，并且上次数据库出问题，导致了很大一部分这个错误。故优化里可以如此设置： max_standby_archive_delay = -1，max_standby_streaming_delay = -1。让有冲突的查询查完。（正常情况下没啥副作用，除非库挂了）
5. 数据端使用sql时，没使用任何框架，没有连接池。读数据是通过resultset.next游标。通常在数据量特别大、并且在循环里添加了业务的时候，游标读有额外开销。
   • 优化方案：修改读取程序，在读取数据库数据后，先循环mapping成对象，然后对对象进行操作。以此缩短事务，减轻数据库压力。