PostgreSql 索引失效_pg联合索引使用范围查询失效

作者：2023面试高手 | 2024-05-22 04:07:05

踩

pg联合索引使用范围查询失效

什么是索引失效？如果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

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/2023面试高手/article/detail/606152