什么是索引失效?如果where过滤条件设置不合理,即使索引存在,且where过滤条件中包含索引列,也会导致全表扫描,索引不起作用。什么条件下会导致索引失效呢?
1.任何计算、函数、类型转换
2.!=
3.NOT,相当于使用函数
4.模糊查询通配符在开头
5.索引字段在表中占比较高
6.多字段btree索引查询条件不包含第一列
7.多字段索引查询条件使用OR(有时也会走索引扫描,但查询效率不高)
测试表
test=# \timing
Timingis on.
test=# create table tbl_index(a bigint,b timestamp without time zone ,c varchar(12));CREATE TABLETime:147.366ms
test=# insert into tbl_index select generate_series(1,10000000),clock_timestamp()::timestamp without time zone,'bit me';INSERT 0 10000000Time:30982.723 ms
1.任何计算、函数、类型转换
crtest=# create index idx_tbl_index_a ontbl_index (a);CREATE INDEXTime:19634.874ms
test=#
test=# explain analyze select * from tbl_index where a = 1;
QUERYPLAN
------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_tbl_index_a on tbl_index (cost=0.43..8.45 rows=1 width=23) (actual time=59.844..59.850 rows=1 loops=1)Index Cond: (a = 1)
Planning time:22.788ms
Execution time:60.011ms
(4rows)
Time:84.865ms
test=# explain analyze select * from tbl_index where a + 1 = 1;
QUERYPLAN
---------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..84399.00 rows=50000 width=23) (actual time=7678.109..7678.109 rows=0 loops=1)
Workers Planned:2Workers Launched:2
-> Parallel Seq Scan on tbl_index (cost=0.00..78607.33 rows=20833 width=23) (actual time=7350.047..7350.047 rows=0 loops=3)
Filter: ((a+ 1) = 1)
Rows Removedby Filter: 3333333Planning time:0.112ms
Execution time:7688.615ms
(8rows)
Time:7780.024ms
test=# explain analyze select * from tbl_index where power(a,2) = 1;
QUERYPLAN
---------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..94815.67 rows=50000 width=23) (actual time=47.516..6902.399 rows=1 loops=1)
Workers Planned:2Workers Launched:2
-> Parallel Seq Scan on tbl_index (cost=0.00..89024.00 rows=20833 width=23) (actual time=4607.894..6892.174 rows=0 loops=3)
Filter: (power((a)::double precision, '2'::double precision) &