开发者学堂课程【PostgreSQL快速入门：4 PostgreSQL 索引，全文检索，模糊匹配，近似度匹配（二）】学习笔记与课程紧密联系，让用户快速学习知识
课程地址：https://developer.aliyun.com/learning/course/16/detail/90
4 PostgreSQL 索引，全文检索，模糊匹配，近似度匹配（二）
内容介绍：
六、索引合并查询
七、索引和collate的匹配
八、部分值索引
九、函数索引和表达式索引
十、函数和表达式索引
六、索引合并查询
1、Combining Multiple Indexes
src/backend/executor
例如
比如有两个查询条件是and，or的查询条件，并且两个列都创建了索引，就可以走bitmap的合并，相当于使用索引取出的数据tupleaaddr进行bitmap，最后给上面的节点，扫描bitmapand或者bitmapor的结果集。
2、Combining Multiple Indexes
单列索引的多条件匹配合并
postgres= # create table test (id int primary key，info text unique);
postgres= # insert into test select
generate_ series(1，10000o)，' 'digoal'llgenerate_
_series(1，10оо00);
postgres= # explain analyze select * from test where id=1 or id= 1000;
Bitmap Heap Scan on test (cost=8.54..16.20 rows=2 width=36) (actual
time=0.034..0.036 rows= =2 loops=1)
Recheck Cond: ((id= 1) OR (id = 1000))
>BitmapOr (cost=8.54..8.54 rows=2 width=o) (actual time=0.023..0.023
rows=o loops=1)
> Bitmap Index Scan on test_ pkey (cost= O.00..4.27 rows=1 width=o)
(actual time=0.012..O.012 rows=1 loops=1)
Index Cond: (id= 1)
>
Bitmap Index Scan on test_ pkey (cost=0.00..4.27 rows=1 width=o)分成两个节点，走索引扫描，同时再对扫描出来的结果cpid做排序，去上面的节点BitmapOr，给Bitmap Heap Scan节点做最后的数据
(actual time=0.oo9..o.0o9 rows=1 loops=1)
Index Cond: (id = 1000)
七、索引和collate的匹配
collection
例子
CREATE TABLE test1c (
id integer，
content varchar COLLATE "x"创建表，collection是x
);
CREATE INDEX test1c_ content_ index ON test1c (content);强制指定
SELECT * FROM test1c WHERE content > constant;可能不走索引
以下SQL不能使用索引test1c_ content_ index
SELECT * FROM test1c WHERE content > constant COLLATE "y";
需建立与y COLLATE对应的索引，以上这条SQL才会走索引。
CREATE INDEX test1c_ content_ y_ index ON test1c (content COLLATE "y");在查询时
如果创建的索引的collection和字段上面存储的collection，查询所要的collection不一样，不会走索引。创建的索引不是collection，在查询时指定y，也不会走索引，创建的collection y跟创建的colatr x也是不会走索引，创建索引时指定collection y即可。
八、部分值索引
1、partial index
在postgres里面允许对某些值创建索引。表里面，字段里面只关心
例子where id不等于1的值，只对不等1的创建索引，好处是索引很小，检索的值可能是一小部分，创建的是很小的数据。
部分约束
去除common值id=1，这个值有10W条，走索引根本不合适，partial 索引很好的避免了此类情况。
postgres= # create table test(id int，info text);
postgres= # insert into test select 1，' digoal'｜｜generate_ series(1，100000);
postgres= # insert into test select
generate_ series(1，1000)，digoal'｜｜generate_series(1，100o);
postgres= # create index idx_ test_ 1 on test(id) where id<>1;
postgres= # explain select * from test where id=1;
5"7
Seq Scan on test (cost=0.00..1791.00 rows=100000 width=15)
Filter: (id= 1)
postgres= # explain select * from test where id=10O;
Index Scan using idx_ test_ 1 on test (cost=o.oo..8.27 rows=1 width=15)
Index Cond: (id = 100)
2、部分索引在非索引列的使用
postgres= # explain select * from test where info='digoal' and id=1;
QUERY PLAN
Seq Scan on test (cost=0.00..2041.00 rows=1 width=15)
Filter: ((info = 'digoal'::text) AND (id = 1))
postgres= # create index idx_ test_2 on test(id) where info='digoalioo';索引列创建在虚拟列上面，检索时也可以走索引
postgres= # explain select * from test where info='digoalioo';在某一个查询中带一个条件，再创建索引
QUERY PLAN
Index Scan using idx_ test_ 2 on test (cost=0.00..8.27 rows=1 width=15)
(1 row)
3、部分索引的好处，为什么要去除common值
postgres= # drop index idx_test_ 1;
postgres= # drop index idx_test _2;
postgres= # explain select * from test where id=1;
QUERY PLAN
Seq Scan on test (cost=0.00..1791.00 rows= 100000 width=15)
Filter: (id = 1)创建的索引没有用，可以把id=1排除
为什么会走全表扫描
postgres= # select id,count(*) from test group by id order by count(*) desc limit 10;
id I count
1｜100001 对频繁出现的值，id等于1的值出现了100001
120｜1其它值出现很少，id走索引不合理，可以把id排除掉
887｜1
681｜1
id不等于1，创建的索引只会出现清晰的。
九、函数索引和表达式索引
1、表达式索引
postgres= # explain select * from test where id+1=100;
QUERY PLAN
Seq Scan on test (cost=0.00..2059.86 rows=505 width=15)
Filter: ((id + 1)= 100)
postgres= # create index idx_ test_ 1 on test((id+1));
CREATE INDEX
postgres= # explain select * from test where id+1= 100;
QUERY PLAN
Bitmap Heap Scan on test (cost= 12.18..577.45 rows=505 width=15)
Recheck Cond: ((id + 1)= 100)
> Bitmap Index Scan on idx_ test_ 1 (cost=0.00..12.05 rows=505 width=0)
Index Cond: ((id + 1)= 100)
函数创建索引必须是seq，在进行where条件查询时where里面的函数传入相同的值只会计算一遍，如果传入同样的参数得到的值跟调用值不一样，不允许走索引扫描，如果传入同样的参数得到的值不一样，创建索引没有任何意义，做函数匹配时发现变量的值跟创建索引时的函数所产生的值不一样，如果创建函数索引，函数输入同样的参数得到的值不管调用多少次都一样，返回的结果也是一样的，只有这种函数才能拿来做函数索引，表达式索引也是类似的，+对应函数也是seq函数。
2、函数索引
以下区分大小写的场景无法使查询走普通的索引。
postgres= # create table test (id int,info text,crt_time timestamp(o));
postgres= # insert into test select
generate_ series(1,100000),' digoal'｜｜generate_ series(1,100000),clock_ timestamp(
);
postgres= # create index idx_ test_ info on test(info);
postgres= # explain select * from test where info~*'^a';
Seq Scan on test (cost= 0.00..1887.00 rows=10 width=23)
Filter: (info ~*'^:':text)
忽略大小写的ilike和~*要走索引的话,开头的字符只能是大小写一致的,字母不行，数字可以，例如字母a区分大小写,数字0不区分大小写索引中的条目也就有差别。
postgres= # explain select * from test where info{~* '^0';
Index Scan using idx_ .test_ info on test (cost=0.00..8.28 rows=10 width=23)
Index Cond: ((info >= 'o':text) AND (info < '1'::text))
Filter: (info ~* '^o'::text)
十、函数和表达式索引
1、函数索引
要让字母也可以走忽略大小写的索引如何做呢?
函数索引,但是函数必顷是immutable状态的，才能创建函数索引。其他状态不能创建函数索引。
过滤条件中也必须使用和创建的索引相同声明。
postgres= # select proname,provolatile from pg_proc where proname= 'lower';
proname| provolatile
Lower｜i
postgres= # create index idx_ test_info_ 1 on test(lower(info));
CREATE INDEX
postgres= # explain select * from testwhere lower(info) ~ '^a';区分大小写可以用函数，先转换成lower的字母再匹配
Bitmap Heap Scan on test (cost= 13.40..648.99 rows=500 width=23)
Filter: (lower(info) ~ '^ a'::text)
> Bitmap Index Scan on idx_ test_ jinfo_ 1 (cost=0.00..13.27 rows=500 width=0)
Index Cond: (lower(info) >= a:text) AND (lower(info) < 'b'::text))
(4 rows)
2、作为查询条件的函数或常量或变量或子查询
优化器需要知道给operator的参数值才能通过pg_statistic中 统计到的表柱状图来计算走索引还是走全表扫描戈者其他planner的开销最小，如果传入的是个变量则通常不能使用索引扫描。
几个时间函数的稳定性:
postgres= # create index idx_test_ 1 on test (crt_time);
postgres= # select proname,proargtypes,provolatile from Pg_proc where通过proc字段查询
prorettype in (1114,1184) order by proargtypes;
proname| proargtypes | provolatile
transaction_ timestamp||s
statement_ timestamp||s
pg_ stat_ get_ bgwriter_stat_ .reset_time||s
pg. . conf_ load_time||s
pg_ postmaster_ start_ time||s
pg _last_xact_replay_ timestamp||v
3、作为查询条件的函数或常量或变量或子查询
clock_ timestamp｜｜v
Now｜｜s
postgres= # explain select * from test where crt_ time = clock_timestamp();作为查询条件的函数，至少不会是stamp，在crt_ time上面有索引，输入的查询条件是函数
不知道每次调用的结果，所以每条记录都会调用函数，根据函数得到的结果集进行匹配
Seq Scan on test (cost=0.0...2137.00 rows=100000 width=23)
Filter: (crt_time = clock_timestamp())输入的查询条件
postgres= # explain select * from test where crt_ time = now();
Index Scan using idx_ test_ 1 on test (cost=0.00..8.28 rows=1 width=23)
Index Cond: (crt_ time = now())
postgres= # alter function now() strict volatile;
postgres= # explain select * from test where crt_ time = now();不走索引，也会变成连续扫描
Seq Scan on test (cost=0.00..2137.00 rowS=100000 width=23)
Filter: (crt_time = now())
作为查询条件的函数或常量或变量或子查询
postgres= # alter function clock_timestamp() strict immutable;
ALTER FUNCTION
postgres= # explain select * from test where crt_time = clock_timestamp();
QUERY PLAN
Index Scan using idx_test_ 1 on test (cost=0.00..8.28 rows=1 width=23)
Index Cond: (crt_time = '2012-04-30 15:32:02.559888+ o8' :timestamp with time zone)
作为过滤条件的函数, immutable和stable的函数在优化器开始计算COST前会把函数值算出来,而volatile的函数,是在执行SQL的时候运行的,所以无法在优化器计算执行计划的阶段得到函数值,也就无法和pg_ statistic中的信息比对到底是走索引呢还是全表扫描或其
他执行计划。
4、表达式作为过滤条件时,同样的道理,表达式不会在优化器计算执行计划的过程中运算，所以也不能走最优的执行计划。
postgres= # explain select * from test where crt_ time = (select now（）);
QUERY PLAN
Seq Scan on test (cost=0.01..1887.01 rows=100000 width=23)
Filter: (crt_ timne = $0)
InitPlan 1 (returns $0)
> Result (cost=0.0..0.01 rows=1 width=0)
(4 rows)
绑定变量的使用场景,通常需要5次后得到generic plan。执行计划前五次都要进行评估，下次再进行同样的sql语句，使用的是generic plan，不是class plan，通过generic plan的到的成本和generic plan当时存储的成本相差很大的情况才会重新计算出class plan。
htp:/blog.163.com/digoal@126/blgstatic/1638770402012112452432251/
