postgresql----索引失效
什么是索引失效？如果where过滤条件设置不合理，即使索引存在，且where过滤条件中包含索引列，也会导致全表扫描，索引不起作用。什么条件下会导致索引失效呢？
1.任何计算、函数、类型转换
2.!=
3.NOT，相当于使用函数
4.模糊查询通配符在开头
5.索引字段在表中占比较高
6.多字段btree索引查询条件不包含第一列
7.多字段索引查询条件使用OR（有时也会走索引扫描，但查询效率不高）
测试表
test=# \timing Timing is on. test=# create table tbl_index(a bigint,b timestamp without time zone ,c varchar(12)); CREATE TABLE Time: 147.366 ms test=# insert into tbl_index select generate_series(1,10000000),clock_timestamp()::timestamp without time zone,'bit me'; INSERT 0 10000000 Time: 30982.723 ms
1.任何计算、函数、类型转换
crtest=# create index idx_tbl_index_a on tbl_index (a); CREATE INDEX Time: 19634.874 ms test=# test=# explain analyze select * from tbl_index where a = 1; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------ 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.788 ms Execution time: 60.011 ms (4 rows) Time: 84.865 ms test=# explain analyze select * from tbl_index where a + 1 = 1; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------- Gather (cost=1000.00..84399.00 rows=50000 width=23) (actual time=7678.109..7678.109 rows=0 loops=1) Workers Planned: 2 Workers 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 Removed by Filter: 3333333 Planning time: 0.112 ms Execution time: 7688.615 ms (8 rows) Time: 7780.024 ms test=# explain analyze select * from tbl_index where power(a,2) = 1; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------- Gather (cost=1000.00..94815.67 rows=50000 width=23) (actual time=47.516..6902.399 rows=1 loops=1) Workers Planned: 2 Workers 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) = '1'::double precision) Rows Removed by Filter: 3333333 Planning time: 13.564 ms Execution time: 6904.232 ms (8 rows) Time: 7051.482 ms test=# test=# explain analyze select * from tbl_index where a::varchar = '1'; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------- Gather (cost=1000.00..94815.67 rows=50000 width=23) (actual time=1.239..6689.272 rows=1 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Seq Scan on tbl_index (cost=0.00..89024.00 rows=20833 width=23) (actual time=4449.890..6679.233 rows=0 loops=3) Filter: (((a)::character varying)::text = '1'::text) Rows Removed by Filter: 3333333 Planning time: 1.029 ms Execution time: 6692.329 ms (8 rows) Time: 6723.530 ms
在表tbl_index.a字段创建btree索引，使用a=1索引生效，但是下面的例子运算、函数、类型转换却导致索引失效了。
where a + 1 = 1
where power(a,2) = 1
where a::varchar = '1'
如何解决呢？可参考前面的表达式索引解决：
create index idx_tbl_index_a on tbl_index ((a+1));
create index idx_tbl_index_a on tbl_index ((power(a,2)));
create index idx_tbl_index_a on tbl_index ((a::varchar));
2.!=
test=# explain analyze select * from tbl_index where a != 1; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------- Seq Scan on tbl_index (cost=0.00..140899.00 rows=9999999 width=23) (actual time=0.049..11004.864 rows=9999999 loops=1) Filter: (a <> 1) Rows Removed by Filter: 1 Planning time: 0.206 ms Execution time: 11585.859 ms (5 rows) Time: 11587.146 ms
3.NOT，相当于使用函数
test=# explain analyze select * from tbl_index where a is null; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------ Index Scan using idx_tbl_index_a on tbl_index (cost=0.43..4.45 rows=1 width=23) (actual time=30.092..30.092 rows=0 loops=1) Index Cond: (a IS NULL) Planning time: 33.783 ms Execution time: 41.838 ms (4 rows) Time: 102.544 ms test=# explain analyze select * from tbl_index where a is not null; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------- Seq Scan on tbl_index (cost=0.00..115899.00 rows=10000000 width=23) (actual time=3.062..6309.908 rows=10000000 loops=1) Filter: (a IS NOT NULL) Planning time: 0.099 ms Execution time: 6877.566 ms (4 rows) Time: 6878.156 ms
以上比较可知where a is null索引生效，但是where a is not null导致索引生效。类似导致索引失效的还有NOT IN，NOT LIKE等，但是NOT EXISTS不会导致索引失效。下面的例子可以看到tbl_index表仍进行索引扫描，但是性能仍有限制，使用NOT IN虽然索引失效，但性能比NOT EXISTS要高。这个和我之前的认识有些出入，之前测试发现NOT EXISTS比NOT IN性能高，看来情况不同，性能也是不一定的。
test=# explain analyze select * from tbl_index where a not in (select a from tbl_test ); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------- Seq Scan on tbl_index (cost=14.50..140913.50 rows=5000000 width=23) (actual time=1.393..3717.312 rows=9999000 loops=1) Filter: (NOT (hashed SubPlan 1)) Rows Removed by Filter: 1000 SubPlan 1 -> Seq Scan on tbl_test (cost=0.00..12.00 rows=1000 width=8) (actual time=0.038..0.236 rows=1000 loops=1) Planning time: 0.134 ms Execution time: 4147.857 ms (7 rows) Time: 4148.615 ms test=# explain analyze select * from tbl_index where not exists (select null from tbl_test where tbl_test.a = tbl_index.a); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------- -------------------- Merge Anti Join (cost=62.45..218187.26 rows=9999000 width=23) (actual time=1.698..16909.581 rows=9999000 loops=1) Merge Cond: (tbl_index.a = tbl_test.a) -> Index Scan using idx_tbl_index_a on tbl_index (cost=0.43..193110.43 rows=10000000 width=23) (actual time=0.035..14781.400 row s=10000000 loops=1) -> Sort (cost=61.83..64.33 rows=1000 width=8) (actual time=0.390..0.659 rows=1000 loops=1) Sort Key: tbl_test.a Sort Method: quicksort Memory: 71kB -> Seq Scan on tbl_test (cost=0.00..12.00 rows=1000 width=8) (actual time=0.038..0.194 rows=1000 loops=1) Planning time: 0.339 ms Execution time: 17530.472 ms (9 rows) Time: 17594.258 ms
4.模糊查询通配符在开头
test=# explain analyze select * from tbl_index where c like 'bit%'; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------- Seq Scan on tbl_index (cost=0.00..140899.00 rows=10000000 width=23) (actual time=0.099..1685.317 rows=10000000 loops=1) Filter: ((c)::text ~~ 'bit%'::text) Planning time: 55.373 ms Execution time: 2104.863 ms (4 rows) Time: 2164.464 ms test=# explain analyze select * from tbl_index where c like '%me'; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------- Seq Scan on tbl_index (cost=0.00..140899.00 rows=10000000 width=23) (actual time=20.172..5507.741 rows=10000000 loops=1) Filter: ((c)::text ~~ '%me'::text) Planning time: 65.603 ms Execution time: 6007.367 ms (4 rows)
5.索引字段在表中占比较高
test=# insert into tbl_index values (10000001,'2015-05-23 00:00:00','haha'); INSERT 0 1 Time: 88.226 ms test=# explain analyze select * from tbl_index where c = 'bit me'; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------- Seq Scan on tbl_index (cost=0.00..140899.00 rows=10000000 width=23) (actual time=0.051..6758.236 rows=10000000 loops=1) Filter: ((c)::text = 'bit me'::text) Rows Removed by Filter: 1 Planning time: 0.128 ms Execution time: 7237.900 ms (5 rows) Time: 7238.685 ms test=# explain analyze select * from tbl_index where c = 'haha'; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------- Index Scan using idx_tbl_index_c on tbl_index (cost=0.43..4.45 rows=1 width=23) (actual time=0.063..0.065 rows=1 loops=1) Index Cond: ((c)::text = 'haha'::text) Planning time: 0.219 ms Execution time: 2.869 ms (4 rows) Time: 4.942 ms
test=# drop index idx_tbl_index_a; DROP INDEX Time: 134.873 ms test=# drop index idx_tbl_index_c; DROP INDEX Time: 173.572 ms
6.多字段btree索引查询条件不包含第一列
test=# explain analyze select * from tbl_index where a = 10000001 and c = 'haha'; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------- Index Scan using idx_tbl_index_a_c on tbl_index (cost=0.43..6.20 rows=1 width=23) (actual time=23.254..23.257 rows=1 loops=1) Index Cond: ((a = 10000001) AND ((c)::text = 'haha'::text)) Planning time: 36.050 ms Execution time: 35.710 ms (4 rows) Time: 78.816 ms test=# explain analyze select * from tbl_index where c = 'haha'; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- Gather (cost=1000.00..68982.44 rows=1 width=23) (actual time=7869.579..7890.974 rows=1 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Seq Scan on tbl_index (cost=0.00..67982.34 rows=0 width=23) (actual time=7468.480..7468.480 rows=0 loops=3) Filter: ((c)::text = 'haha'::text) Rows Removed by Filter: 3333333 Planning time: 0.130 ms Execution time: 7891.137 ms (8 rows) Time: 7891.937 ms test=# explain analyze select * from tbl_index where a = 10000001; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------ Index Scan using idx_tbl_index_a_c on tbl_index (cost=0.43..8.45 rows=1 width=23) (actual time=0.154..0.156 rows=1 loops=1) Index Cond: (a = 10000001) Planning time: 0.257 ms Execution time: 0.206 ms (4 rows) Time: 1.119 ms
7.多字段索引查询条件使用OR
test=# explain analyze select * from tbl_index where a = 10000001 or c = 'haha'; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- Gather (cost=1000.00..79399.11 rows=1 width=23) (actual time=7321.821..7323.593 rows=1 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Seq Scan on tbl_index (cost=0.00..78399.01 rows=0 width=23) (actual time=7307.413..7307.413 rows=0 loops=3) Filter: ((a = 10000001) OR ((c)::text = 'haha'::text)) Rows Removed by Filter: 3333333 Planning time: 0.163 ms Execution time: 7324.821 ms (8 rows) Time: 7325.532 ms test=# explain analyze select * from tbl_index where a = 10000001 and c = 'haha'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------ Index Scan using idx_tbl_index_a_c on tbl_index (cost=0.43..6.20 rows=1 width=23) (actual time=0.040..0.041 rows=1 loops=1) Index Cond: ((a = 10000001) AND ((c)::text = 'haha'::text)) Planning time: 0.165 ms Execution time: 0.093 ms (4 rows) Time: 32.904 ms
