索引相关分享

索引是提高数据库性能的常用途径。比起没有索引，使用索引可以让数据库服务器更快找到并获取特定行。但是索引同时也会增加数据库系统的日常管理负担。

• 系统会在表更新时更新索引，而且会在它觉得使用索引比顺序扫描表效率更高时使用索引

• 我们可能需要定期地运行ANALYZE命令来更新统计信息以便查询规划器能做出正确的决定

• 索引也会使带有搜索条件的UPDATE和DELETE命令受益；此外索引还可以在连接搜索中使用

• 并发构建索引允许并行的写

• 一个索引被创建后，系统必须保持它与表同步。这增加了数据操作的负担

• 那些很少或从不在查询中使用的索引应该被移除

索引导读

Ctid

​ ctid是行版本在其表中的物理位置。注意尽管ctid可以被用来非常快速地定位行版本，但是一个行的ctid会在被更新或者被VACUUM FULL移动时改变。因此，ctid不能作为一个长期行标识符。 应使用主键来标识逻辑行。

ctid(block number + 块内的偏移量offset)，

testuse2=# create table t1(id serial primary key,name varchar);
CREATE TABLE
testuse2=# \d t1
                                 Table "public.t1"
 Column |       Type        | Collation | Nullable |            Default             
--------+-------------------+-----------+----------+--------------------------------
 id     | integer           |           | not null | nextval('t1_id_seq'::regclass)
 name   | character varying |           |          | 
Indexes:
    "t1_pkey" PRIMARY KEY, btree (id)
testuse2=# insert into t1(name) select a::varchar||'test' from generate_series(1,1000)a;
INSERT 0 1000
testuse2=# select ctid,* from t1 limit 10;
  ctid  | id |  name  
--------+----+--------
 (0,1)  |  1 | 1test
 (0,2)  |  2 | 2test
 (0,3)  |  3 | 3test
 (0,4)  |  4 | 4test
 (0,5)  |  5 | 5test
 (0,6)  |  6 | 6test
 (0,7)  |  7 | 7test
 (0,8)  |  8 | 8test
 (0,9)  |  9 | 9test
 (0,10) | 10 | 10test        -- 我这一行数据位于第0个block，偏移量为10的位置
(10 rows)

testuse2=# select ctid,* from t1 where ctid='(0,10)';
  ctid  | id |  name  
--------+----+--------
 (0,10) | 10 | 10test
(1 row)

pageinspect模块

pageinspect模块提供函数让你从低层次观察数据库页面的内容，这对于调试目的很有用。所有这些函数只能被超级用户使用。

该模块是自带的模块，需要我们在源码安装的时候使用 make world 和make install-world (源码安装)。

部分函数介绍

create extension pageinspect;
get_raw_page(relname text, blkno int) 返回 bytea

一个简写版的get_raw_page，用于读取主分叉。等效于get_raw_page(relname, 'main', blkno)




将这条数据更新之后：




autovacuum—》或者说手动vacuum操作，他是不会释放空间给系统，

vacuum full–〉可以释放空间给系统，但是会造成整个表最高级的锁

MVCC 多版本并发控制。

pg。—》 旧的数据，先不删除。

mysql–》 undo

bt_page_items(relname text, blkno int) 返回 setof record

bt_page_items返回一个 B-树索引页面上项的所有细节信息。例如：

testuse=# select * from bt_page_items('tt1_pkey', 1);
 itemoffset | ctid  | itemlen | nulls | vars |          data           
------------+-------+---------+-------+------+-------------------------
          1 | (0,1) |      16 | f     | f    | 01 00 00 00 00 00 00 00
          2 | (0,3) |      16 | f     | f    | 02 00 00 00 00 00 00 00
          3 | (0,4) |      16 | f     | f    | 03 00 00 00 00 00 00 00
(3 rows)

testuse=# \d tt1
                                 Table "public.tt1"
 Column |       Type        | Collation | Nullable |             Default             
--------+-------------------+-----------+----------+---------------------------------
 id     | integer           |           | not null | nextval('tt1_id_seq'::regclass)
 name   | character varying |           |          | 
Indexes:
    "tt1_pkey" PRIMARY KEY, btree (id)
    "tt1_name_idx" btree (name)

testuse=# select ctid,* from tt1;
 ctid  | id |  name   
-------+----+---------
 (0,1) |  1 | lucy666
 (0,2) |  2 | Anna666
 (0,3) |  3 | Alex666
(3 rows)

testuse=# select * from bt_page_items('tt1_pkey', 1);
 itemoffset | ctid  | itemlen | nulls | vars |          data           
------------+-------+---------+-------+------+-------------------------
          1 | (0,1) |      16 | f     | f    | 01 00 00 00 00 00 00 00
          2 | (0,2) |      16 | f     | f    | 02 00 00 00 00 00 00 00
          3 | (0,3) |      16 | f     | f    | 03 00 00 00 00 00 00 00
(3 rows)

testuse=# update tt1 set name='Alex' where id =3;
UPDATE 1
testuse=# select ctid,* from tt1;
 ctid  | id |  name   
-------+----+---------
 (0,1) |  1 | lucy666
 (0,2) |  2 | Anna666
 (0,4) |  3 | Alex
(3 rows)

testuse=# select * from bt_page_items('tt1_pkey', 1);
 itemoffset | ctid  | itemlen | nulls | vars |          data           
------------+-------+---------+-------+------+-------------------------
          1 | (0,1) |      16 | f     | f    | 01 00 00 00 00 00 00 00
          2 | (0,2) |      16 | f     | f    | 02 00 00 00 00 00 00 00
          3 | (0,3) |      16 | f     | f    | 03 00 00 00 00 00 00 00
          4 | (0,4) |      16 | f     | f    | 03 00 00 00 00 00 00 00
(4 rows)

testuse=# select * from heap_page_items(get_raw_page('tt1', 0));
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |           t_data           
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+----------------------------
  1 |   8152 |        1 |     36 |    896 |      0 |        0 | (0,1)  |           2 |      11010 |     24 |        |       | \x01000000116c756379363636
  2 |   8112 |        1 |     36 |    901 |      0 |        0 | (0,2)  |           2 |      11010 |     24 |        |       | \x0200000011416e6e61363636
  3 |   8072 |        1 |     36 |    948 |    951 |        0 | (0,4)  |           2 |       9986 |     24 |        |       | \x0300000011416c6578363636
  4 |   8032 |        1 |     33 |    951 |      0 |        0 | (0,4)  |           2 |      10498 |     24 |        |       | \x030000000b416c6578
(4 rows)


testuse=# vacuum tt1 ;
VACUUM
testuse=# select * from bt_page_items('tt1_pkey', 1);
 itemoffset | ctid  | itemlen | nulls | vars |          data           
------------+-------+---------+-------+------+-------------------------
          1 | (0,1) |      16 | f     | f    | 01 00 00 00 00 00 00 00
          2 | (0,2) |      16 | f     | f    | 02 00 00 00 00 00 00 00
          3 | (0,4) |      16 | f     | f    | 03 00 00 00 00 00 00 00
(3 rows)

testuse=# select * from heap_page_items(get_raw_page('tt1', 0));
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |           t_data           
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+----------------------------
  1 |   8152 |        1 |     36 |    896 |      0 |        0 | (0,1)  |           2 |      11010 |     24 |        |       | \x01000000116c756379363636
  2 |   8112 |        1 |     36 |    901 |      0 |        0 | (0,2)  |           2 |      11010 |     24 |        |       | \x0200000011416e6e61363636
  3 |      0 |        0 |      0 |        |        |          |        |             |            |        |        |       | 
  4 |   8072 |        1 |     33 |    951 |      0 |        0 | (0,4)  |           2 |      10498 |     24 |        |       | \x030000000b416c6578
(4 rows)


testuse=# select ctid,* from tt1;
 ctid  | id |  name   
-------+----+---------
 (0,1) |  1 | lucy666
 (0,2) |  2 | Anna666
 (0,4) |  3 | Alex
(3 rows)

testuse=# insert into tt1  (name)values('Alex666');
INSERT 0 1
testuse=# select ctid,* from tt1;
 ctid  | id |  name   
-------+----+---------
 (0,1) |  1 | lucy666
 (0,2) |  2 | Anna666
 (0,3) |  4 | Alex666
 (0,4) |  3 | Alex
(4 rows)

testuse=# select * from bt_page_items('tt1_pkey', 1);
 itemoffset | ctid  | itemlen | nulls | vars |          data           
------------+-------+---------+-------+------+-------------------------
          1 | (0,1) |      16 | f     | f    | 01 00 00 00 00 00 00 00
          2 | (0,2) |      16 | f     | f    | 02 00 00 00 00 00 00 00
          3 | (0,4) |      16 | f     | f    | 03 00 00 00 00 00 00 00
          4 | (0,3) |      16 | f     | f    | 04 00 00 00 00 00 00 00
(4 rows)

testuse=# select * from heap_page_items(get_raw_page('tt1', 0));
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |           t_data           
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+----------------------------
  1 |   8152 |        1 |     36 |    896 |      0 |        0 | (0,1)  |           2 |      11010 |     24 |        |       | \x01000000116c756379363636
  2 |   8112 |        1 |     36 |    901 |      0 |        0 | (0,2)  |           2 |      11010 |     24 |        |       | \x0200000011416e6e61363636
  3 |   8032 |        1 |     36 |    952 |      0 |        0 | (0,3)  |           2 |       2306 |     24 |        |       | \x0400000011416c6578363636
  4 |   8072 |        1 |     33 |    951 |      0 |        0 | (0,4)  |           2 |      10498 |     24 |        |       | \x030000000b416c6578
(4 rows)


索引结构图

该结构在版本8.3之后便有了相关优化

优化方式：Heap Only Tuple (HOT)：

1，当一行使用HOT更新时，如果更新的行存储在存储旧行的同一个表页中，PostgreSQL不会插入相应的索引元组，而是将HEAP_HOT_UPDATED位和HEAP_ONLY_TUPLE位分别设置为旧元组和新元组的t_informask2字段。

在本例中，’ Tuple_1 ‘和’ Tuple_2 '分别被设置为HEAP_HOT_UPDATED位和HEAP_ONLY_TUPLE位

我们将描述PostgreSQL如何在使用HOT更新元组之后，使用索引扫描来访问更新后的元组。

2，修剪

上图a展现（修剪前）:

(1)找到指向目标元组的索引元组。

(2)从get索引元组中访问行指针’[1]’。

(3)读“Tuple_1”。

(4)通过’ Tuple_1 ‘的t_ctid读取’ Tuple_2 '。

在这种情况下，PostgreSQL读取两个元组，’ Tuple_1 ‘和’ Tuple_2 '，并使用并发控制机制来决定哪个是可见的。

但是，如果删除表页中的死元组，就会出现问题。例如，在上图(a)中，如果“Tuple_1”被删除，因为它是一个死元组，那么“Tuple_2”就不能从索引中访问。

为了解决这个问题，在适当的时候，PostgreSQL将指向旧元组的行指针重定向为指向新元组的行指针。在PostgreSQL中，这种处理称为修剪。像上面的b图的PostgreSQL如何在修剪后访问更新的元组。

上图b展现PostgreSQL如何在修剪后访问更新的元组。

(1)查找索引元组。

(2)从get索引元组中访问行指针’[1]’。

(3)通过重定向行指针访问指向Tuple_2的行指针’[2]’。

(4)从行指针’[2]‘读取’ Tuple_2 ’

3，碎片整理

4，hot不可用：

​ 4.1 当更新的元组存储在另一个页面中时，该页面没有存储旧的元组，指向该元组的索引元组也被插入到索引页面中。

​ 4.2 当索引元组的键值更新时，新的索引元组被插入到索引页中。

Bitmap

本质上是通过构建位图，避免重复扫描一个块，同时会被要被扫描的数据块地址进行排序，减少随机IO


testuse=# \d tt1
                                 Table "public.tt1"
 Column |       Type        | Collation | Nullable |             Default             
--------+-------------------+-----------+----------+---------------------------------
 id     | integer           |           | not null | nextval('tt1_id_seq'::regclass)
 name   | character varying |           |          | 
Indexes:
    "tt1_pkey" PRIMARY KEY, btree (id)
    
testuse=# explain analyze select * from t1 where id = 300 or id =500;
                                                      QUERY PLAN                                                      
----------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t1  (cost=8.32..13.67 rows=2 width=36) (actual time=0.010..0.011 rows=0 loops=1)
   Recheck Cond: ((id = 300) OR (id = 500))
   ->  BitmapOr  (cost=8.32..8.32 rows=2 width=0) (actual time=0.009..0.009 rows=0 loops=1)
         ->  Bitmap Index Scan on t1_pkey  (cost=0.00..4.16 rows=1 width=0) (actual time=0.008..0.008 rows=0 loops=1)
               Index Cond: (id = 300)
         ->  Bitmap Index Scan on t1_pkey  (cost=0.00..4.16 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1)
               Index Cond: (id = 500)
 Planning Time: 0.056 ms
 Execution Time: 0.030 ms
(9 rows)

顺序扫描

就是挨个获取所有数据的CTID，去检索所有数据是否满足

testuse2=# create table t1 (id serial,name varchar);
CREATE TABLE
testuse2=# insert into t1(name)select a from generate_series(1,1000)a;
INSERT 0 1000
testuse2=# explain analyze select * from t1 where id = 900;
                                         QUERY PLAN                                          
---------------------------------------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..17.50 rows=1 width=7) (actual time=0.071..0.079 rows=1 loops=1)
   Filter: (id = 900)
   Rows Removed by Filter: 999  -----总共1000条数据，过滤掉999条数据，进行了全表扫描
 Planning Time: 0.039 ms
 Execution Time: 0.092 ms
(5 rows)

顺序读取预估总成本计算

testuse2=# select relname,relpages,reltuples from pg_class where relname ='t1';
 relname | relpages | reltuples 
---------+----------+-----------
 t1      |        5 |      1000
(1 row)

testuse2=# show seq_page_cost ;
 seq_page_cost 
---------------
 1
(1 row)

testuse2=# show cpu_tuple_cost;
 cpu_tuple_cost 
----------------
 0.01
(1 row)

testuse2=# show cpu_operator_cost;
 cpu_operator_cost 
-------------------
 0.0025
(1 row)

testuse2=# select 5*1 + 1000*0.01 + 1000*0.0025;
 ?column? 
----------
  17.5000
(1 row)


相关参数

0.seq_page_cost  设置规划器计算一次顺序磁盘页面抓取的开销。默认值是1.0。

1.random_page_cost=4(默认值，固态磁盘可以将此值设置为接近1，如1.1)， 机械硬盘需要考虑随机读和顺序读的性能差异。固态硬盘随机读和顺序读代价相似,可以配置成接近1的值。

2.cpu_index_tupe_cost=0.005 如果使用了索引,还需要考虑索引CPU的代价。

3.parallel_tuple_cost=0.1 一个并行工作者work,向另外一个进程传输元组的代价。

4.parallel_setup_cost=1000.0 启动一个工作者work进程的代价。

5.min_parallel_table_scan_size=8MB 为必须扫描的表数据量设置一个最小值，扫描的表数据量超过这一个值才会考虑使用并行扫描。。

6.min_parallel_index_scan_size=512KB 为必须扫描的索引数据量设置一个最小值，扫描的索引数据量超过这一个值时才会考虑使用并行扫描。

索引创建与维护

创建索引

CREATE INDEX — 定义一个新索引

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON [ ONLY ] table_name [ USING method ]
    ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ INCLUDE ( column_name [, ...] ) ]
    [ WITH ( storage_parameter = value [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    [ WHERE predicate ]

删除索引

一个索引可以使用 PostgreSQL 的 DROP 命令删除。

DROP INDEX index_name;

索引失效

在表t1.age字段创建btree索引，使用age=1索引生效，但是下面的例子运算、函数、类型转换却导致索引失效了。

where age + 1 = 1   

where power(age,2) = 1

where age::varchar = '1'  -- 无法使用索引 ，但是where age ='1'可以使用索引。

如何解决呢,可参考前面的表达式索引解决：

create index idx_tl_age on tl ((age+1));
create index idx_t1_age on tl((power(age,2)));
create index idx_tl_age on tl((age::varchar));

索引类型

PostgreSQL提供了多种索引类型： B-tree、Hash、GiST、SP-GiST 、GIN 和 BRIN。每一种索引类型使用了 一种不同的算法来适应不同类型的查询。默认情况下， CREATE INDEX命令创建适合于大部分情况的B-tree 索引。

B-tree

​ 默认情况下， CREATE INDEX命令创建适合于大部分情况的B-tree 索引。

​ B-tree可以在可排序数据上的处理等值和范围查询。特别地，PostgreSQL的查询规划器会在任何一种涉及到以下操作符的已索引列上考虑使用B-tree索引：<、<=、=、>=、> 将这些操作符组合起来，例如BETWEEN和IN，也可以用B-tree索引搜索实现。同样，在索引列上的IS NULL或IS NOT NULL条件也可以在B-tree索引中使用。

优化器也会将B-tree索引用于涉及到模式匹配操作符LIKE和~ 的查询，前提是如果模式是一个常量且被固定在字符串的开头—例如：col LIKE 'foo%'或者col ~ '^foo', 但在col LIKE '%bar'上则不会。

B-tree索引也可以用于检索排序数据。这并不会总是比简单扫描和排序更快，但是总是有用的。

Hash

• 散列(Hash)索引只能处理简单的等于比较

• 当索引列使用等于操作符进行比较时，查询规划器会考虑使用散列索引

• PostgreSQL散列索引的性能不比B-Tree索引强，但是散列索引的尺寸和构造时间则更差

• 散列索引操作V10前没有记录WAL日志，发生了DB崩溃，需用REINDEX重建散列索引

GiST

• GiST索引不是一种单独的索引类型，而是一种架构
• GiST（通用搜索树）提供了一种用于存储数据的方式来构建平衡的树结构
• 可以在该架构上实现很多不同的索引策略
• 可以使GiST索引根据不同的索引策略，使用特定的操作符类型

二维几何类型的以下操作符支持通过Gist索引访问

<< --严格在左侧，例如circle’((0,0),1)’

<< circle’((5,0),1)’

&< --表示左边的平面体不会扩展到超过右边的平面体的右边. 例如box ‘((0,0),(1,1))’

&< box '((0,0),(2,2))’&> --表示左边的平面体不会扩展到超过右边的平面体的左边. 例如box ‘((0,0),(3,3))’ &> box '((0,0),(2,2))’

– 严格在右

<<| --严格在下

&<| --不会扩展到超出上面

|&> --不会扩展到超出下面

|>> – 严格在上

@> --包含

<@ --被包含

~= --相同

&& --相交


GiST是由节点页组成的高度平衡树。 节点由索引行组成。

每个矩形都包含足够多的点以容纳一个索引页：

SP-GiST

SP-GisST与Gist类似，也是一种索引框架

支持基于磁盘存储的非平衡数据结构如四叉树，k-d树，radix树

例如二维的point类型，Sp-Gist索引支持的操作符如下：

<<

~=

<@

<^ – 在下面, circle ‘((0,0),1)’ <^ circle '((0,5),1)’

^ – 在上面, circle ‘((0,5),1)’ >^ circle ‘((0,0),1)’

SP-GiST访问方法的思想是将值域(value domain)分割为不重叠的子域，每个子域依次也可以分割。这样的划分导致了树的不平衡(不像b树和常规的GiST)。

四叉树示例

四叉树用于索引平面上的点。一个想法是递归地将区域分割成相对于中心点的四个部分(象限)。这种树中分支的深度可以变化，并取决于适当象限中点的密度。

以此类推，直到我们得到最终的划分。

让我们来看在与gist相关的文章中已经考虑过的一个简单示例的更多细节。看看这种情况下的分区是什么样子的:

在第一象限对应的节点中，我们再次使用一致性函数确定子节点。中心点是(6,6)，我们需要再次查看第一和第四象限。

GIN

• GIN索引是“倒排索引”，它可以处理包含多个键的值(比如数组)

  “I am a man” (0,1)

  I–>(0,1)

  am–>(0,1) (5,10)

  a -->(0,1)

  Man–>(0,1)

  GIN是广义倒排索引(Generalized Inverted Index)的缩写。这就是所谓的倒排索引。它操作的数据类型的值不是原子的，而是由元素组成的。我们将这些类型称为复合类型。索引的不是复合类型的值，而是单独的元素;每个元素都引用它出现的值。

• 与GiST类似，GIN可以支持多种不同的用户定义的索引策略和特定操作符

• 可以使GIN索引根据不同的索引策略，使用特定的操作符类型

• 作为示例，PostgreSQL的标准发布中包含了用于一维数组的GIN操作符类型

  如：<@、@>、=、&&等

• gin索引和gist索引 如何选择？

• 1, 数据偏于静态 gin。耗时 >2-3(btree gist) ;

  ​ 用户：users加了一个gin；用户数据，

  ​ login。

  用户第三方的信息。。。-

• 2，好处就是检索非常快，gin/gist 也支持like ‘%man’

• 3, 如果数据需要经常更新 使用 gist

BRIN

• BRIN 索引是块级索引，有别于B-TREE等索引，BRIN记录并不是以行号为单位记录索引明细，而是记录每个数据块或者每段连续的数据块的统计信息。因此BRIN索引空间占用特别的小，对数据写入、更新、删除的影响也很小。

  BRIN的想法是避免查找绝对不合适的行，而不是快速找到匹配的行

• BRIN属于LOSSLY索引，当被索引列的值与物理存储相关性很强时，BRIN索引的效果非常的好。

  例如时序数据，在时间或序列字段创建BRIN索引，进行等值、范围查询时效果很棒。

如何选择BRIN索引？

工作原理如下。表被分割成ranges(好多个pages的大小)：因此被称作block range index(BRIN)。在每个range中存储数据的摘要信息。作为规则，这里是最小值和最大值，但有时也并非如此。假设执行了一个查询，该查询包含某列的条件;如果所查找的值没有进入区间，则可以跳过整个range;但如果它们确实在，所有块中的所有行都必须被查看以从中选择匹配的行。

在BRIN索引中，PostgreSQL会为每个8k大小的存储数据页面读取所选列的最大值和最小值，然后将该信息（页码以及列的最小值和最大值）存储到BRIN索引中。

关注的是几个tb或几十tb大的表。BRIN的一个重要特性使我们能够在这样的表上创建索引，索引比较小而且维护成本很低。

索引的一些用法

多列索引

• 只有 B-tree、GiST、GIN 和 BRIN 索引类型支持多列索引，最多可以指定32个列

该限制可以在源代码文件pg_config_manual.h中修改，但是修改后需要重新编译PostgreSQL

• 一个B-tree索引可以用于条件中涉及到任意索引列子集的查询，但是当先导列（即最左边的那些列）上有约束条件时索引最为有效。

确切的规则是：在先导列上的等值约束，加上第一个无等值约束的列上的不等值约束，将被用于限制索引被扫描的部分。在这些列右边的列上的约束将在索引中被检查，这样它们适当节约了对表的访问，但它们并未减小索引被扫描的部分。

• 使用多列索引需要谨慎在大多数情况下，单一字段上的索引就已经足够了，并且还节约时间和空间除非表的

• 使用模式非常固定，否则超过三个字段的索引几乎没什么用处

  （a,b,c）

表达式索引

主要用于在查询条件中存在基于某个字段的函数或表达式的结果与其他值进行比较时

例如，一种进行大小写不敏感比较的常用方法是使用lower函数：

SELECT * FROM test1 WHERE lower(col1) = 'value';

这种查询可以利用一个建立在lower(col1)函数结果之上的索引：

CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));

表达式索引还允许控制唯一索引的范围。例如，此唯一索引可防止在double precision类型列中存储重复的整数值:

CREATE UNIQUE INDEX test1_uniq_int ON tests ((floor(double_col)))

– immutable -->在任何时候，输入的参数一定，结果一定。

–stable–>在一个事务里面它的参数一定，结果一定

– Volatile now()—>

另外一个例子，如果我们经常进行如下的查询：

SELECT * FROM people WHERE (first_name || ' ' || last_name) = 'John Smith';

那么值得创建一个这样的索引：

CREATE INDEX people_names ON people ((first_name || ' ' || last_name));

部分索引

一个部分索引是建立在表的一个子集上，而该子集则由一个条件表达式（被称为部分索引的谓词）定义。而索引中只包含那些符合该谓词的表行的项。部分索引是一种专门的特性，但在很多种情况下它们也很有用。

使用部分索引的一个主要原因是避免索引公值。由于搜索一个公值的查询（一个在所有表行中占比超过一定百分比的值）不会使用索引，所以完全没有理由将这些行保留在索引中。这可以减小索引的尺寸，同时也将加速使用索引的查询。

如果我们有一个表包含已上账和未上账的订单，其中未上账的订单在整个表中占据一小部分且它们是最经常被访问的行。我们可以通过只在未上账的行上创建一个索引来提高性能。创建索引的命令如下：

CREATE INDEX orders_unbilled_index ON orders (order_nr)
    WHERE billed is not true;

使用该索引的一个可能查询是：

SELECT * FROM orders WHERE billed is not true AND order_nr < 10000;

然而，索引也可以用于完全不涉及order_nr的查询，例如：

SELECT * FROM orders WHERE billed is not true AND amount > 5000.00;

这并不如在amount列上部分索引有效，因为系统必须扫描整个索引。然而，如果有相对较少的未上账订单，使用这个部分索引来查找未上账订单将会更好。

唯一索引

索引也可以被用来强制列值的唯一性，或者是多个列组合值的唯一性。当前，只有B-tree能够被声明为唯一。

当一个索引被声明为唯一时，索引中不允许多个表行具有相同的索引值。

CREATE UNIQUE INDEX name ON table (column [, ...]);

覆盖索引

• PostgreSQL中的所有索引是二级索引,这意味着每个索引都是与表的主数据区（在PostgreSQL术语称为表的堆中）分开存储。这意味着在普通索引扫描中，每行检索都需要从索引和堆中取数据。

  T1(a int ,b int ,c int )–>index (a) include(b).—> Index(a,b)

  Select a,b from t1 where a=…and b…

PostgreSQL支持只用索引的扫描，这类扫描可以仅用一个索引来回答查询而不产生任何堆访问。其基本思想是直接从每一个索引项中直接返回值，而不是去参考相关的堆项。

有两个限制：

• 1， 索引类型必须支持只用索引的扫描。B-树索引总是支持只用索引的扫描。GIN 索引是一个不支持只用索引的扫描的反例，因为它的每一个索引项通常只包含原始数据值的一部分。
• 2，查询必须只引用存储在该索引中的列。

覆盖索引，它是一个特别设计的索引，包含经常运行的特殊类型查询所需要的列。由于查询通常需要检索的列不仅仅是他们搜索的列，PostgreSQL允许您创建索引,这个索引中有些列只是“负荷”而不是搜索键的一部分。这可以通过添加INCLUDE来完成子句来列出了额外的列。

例如，如果您通常可以运行这样的查询：

SELECT y FROM tab WHERE x = 'key';

加快此类查询的传统方法是仅在x上的索引。但是，一个索引定义为

CREATE INDEX tab_x_y ON tab(x) INCLUDE (y);

可以将这些查询作为仅索引扫描处理，因为y可以从索引中获取而不需要访问堆。

除非一个表足够慢以至于仅索引扫描可能不必访问堆，否则没有什么理由在一个索引中包含负载列。

并发构建索引

CREATE INDEX CONCURRENTLY ....

• 堵塞update，delete。

• 创建时间大概是不加concurrently2倍左右。