postgresql 索引状态_postgresql----索引失效

最新推荐文章于 2025-04-03 10:29:43 发布
最新推荐文章于 2025-04-03 10:29:43 发布
阅读量3.1k 收藏 4
点赞数
文章标签： postgresql 索引状态
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/weixin_39607423/article/details/111482792
版权
本文探讨了PostgreSQL中导致索引失效的各种情况，包括：1) 使用计算、函数或类型转换；2) 不等于操作符（!=）；3) 使用NOT；4) 模糊查询通配符在前；5) 索引字段在表中占比高；6) 多字段索引查询条件不包含第一列；7) 多字段索引查询条件使用OR。通过示例展示了解决这些问题的方法，如创建表达式索引。对于!=操作符、NOT以及NOT IN和NOT EXISTS的比较，也揭示了索引使用上的差异。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

什么是索引失效？如果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) = '1'::double precision)

Rows Removedby Filter: 3333333Planning time:13.564ms

Execution time:6904.232ms

(8rows)

Time:7051.482ms

test=#

test=# explain analyze select * from tbl_index where a::varchar = '1';

QUERYPLAN

---------------------------------------------------------------------------------------------------------------------------------

Gather (cost=1000.00..94815.67 rows=50000 width=23) (actual time=1.239..6689.272 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=4449.890..6679.233 rows=0 loops=3)

Filter: (((a)::character varying)::text = '1'::text)

Rows Removedby Filter: 3333333Planning time:1.029ms

Execution time:6692.329ms

(8rows)

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;

QUERYPLAN

-------------------------------------------------------------------------------------------------------------------------

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 Removedby Filter: 1Planning time:0.206ms

Execution time:11585.859ms

(5rows)

Time:11587.146 ms

3.NOT，相当于使用函数

test=# explain analyze select * from tbl_index where a is null;

QUERYPLAN

------------------------------------------------------------------------------------------------------------------------------

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.783ms

Execution time:41.838ms

(4rows)

Time:102.544ms

test=# explain analyze select * from tbl_index where a is not null;

QUERYPLAN

--------------------------------------------------------------------------------------------------------------------------

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: (aIS NOT NULL)

Planning time:0.099ms

Execution time:6877.566ms

(4rows)

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 fromtbl_test );

QUERYPLAN

-------------------------------------------------------------------------------------------------------------------------

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 Removedby Filter: 1000SubPlan1

-> 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.134ms

Execution time:4147.857ms

(7rows)

Time:4148.615ms

test=# explain analyze select * from tbl_index where not exists (select null from tbl_test where tbl_test.a =tbl_index.a);

QUERYPLAN

---------------------------------------------------------------------------------------------------------------------------------------------------------

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.400row

s=10000000 loops=1)-> Sort (cost=61.83..64.33 rows=1000 width=8) (actual time=0.390..0.659 rows=1000 loops=1)

SortKey: 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.339ms

Execution time:17530.472ms

(9rows)

Time:17594.258 ms

4.模糊查询通配符在开头

test=# explain analyze select * from tbl_index where c like 'bit%';

QUERYPLAN

--------------------------------------------------------------------------------------------------------------------------

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.373ms

Execution time:2104.863ms

(4rows)

Time:2164.464ms

test=# explain analyze select * from tbl_index where c like '%me';

QUERYPLAN

---------------------------------------------------------------------------------------------------------------------------

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.603ms

Execution time:6007.367ms

(4 rows)

5.索引字段在表中占比较高

test=# insert into tbl_index values (10000001,'2015-05-23 00:00:00','haha');INSERT 0 1Time:88.226ms

test=# explain analyze select * from tbl_index where c = 'bit me';

QUERYPLAN

--------------------------------------------------------------------------------------------------------------------------

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 Removedby Filter: 1Planning time:0.128ms

Execution time:7237.900ms

(5rows)

Time:7238.685ms

test=# explain analyze select * from tbl_index where c = 'haha';

QUERYPLAN

----------------------------------------------------------------------------------------------------------------------------

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.219ms

Execution time:2.869ms

(4rows)

Time:4.942 ms

test=# drop indexidx_tbl_index_a;DROP INDEXTime:134.873ms

test=# drop indexidx_tbl_index_c;DROP INDEXTime:173.572 ms

6.多字段btree索引查询条件不包含第一列

test=# explain analyze select * from tbl_index where a = 10000001 and c = 'haha';

QUERYPLAN

--------------------------------------------------------------------------------------------------------------------------------

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.050ms

Execution time:35.710ms

(4rows)

Time:78.816ms

test=# explain analyze select * from tbl_index where c = 'haha';

QUERYPLAN

-----------------------------------------------------------------------------------------------------------------------------

Gather (cost=1000.00..68982.44 rows=1 width=23) (actual time=7869.579..7890.974 rows=1 loops=1)

Workers Planned:2Workers 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 Removedby Filter: 3333333Planning time:0.130ms

Execution time:7891.137ms

(8rows)

Time:7891.937ms

test=# explain analyze select * from tbl_index where a = 10000001;

QUERYPLAN

------------------------------------------------------------------------------------------------------------------------------

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.257ms

Execution time:0.206ms

(4rows)

Time:1.119 ms

7.多字段索引查询条件使用OR

test=# explain analyze select * from tbl_index where a = 10000001 or c = 'haha';

QUERYPLAN

-----------------------------------------------------------------------------------------------------------------------------

Gather (cost=1000.00..79399.11 rows=1 width=23) (actual time=7321.821..7323.593 rows=1 loops=1)

Workers Planned:2Workers 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 Removedby Filter: 3333333Planning time:0.163ms

Execution time:7324.821ms

(8rows)

Time:7325.532ms

test=# explain analyze select * from tbl_index where a = 10000001 and c = 'haha';

QUERYPLAN

------------------------------------------------------------------------------------------------------------------------------

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.165ms

Execution time:0.093ms

(4rows)

Time:32.904 ms

PostgreSQL查看索引是否失效
tttzzzqqq2018的博客
08-08 1602
PostgreSQL查看索引是否失效
PostgreSQL 如何应对因数据类型不一致导致的索引无法使用？
发现生活，分享智慧，一起成长！
07-14 1160
在使用 PostgreSQL 时，因数据类型不一致导致的索引无法使用是一个常见的问题。这个问题就像一个隐藏在数据库中的陷阱，如果我们不小心，就可能会陷入其中，导致查询效率低下。为了避免这个问题，我们应该在设计数据库表结构时，仔细考虑每个列的数据类型，并确保在查询中使用的条件与索引列的数据类型一致。如果由于某些原因无法确保数据类型的一致性，我们可以使用 PostgreSQL 提供的类型转换函数来将查询条件中的数据类型转换为与索引列的数据类型一致。
PostgreSQL 一则索引失效案例引发的思考
Focus on PostgreSQL
12-03 4315
前段时间碰到个奇怪的索引失效的问题，实际情况类似下面这样： bill=# begin; BEGIN bill=*# create index idx_t1 on t1(id); CREATE INDEX bill=*# explain select * from t1 where id = 1; QUERY PLAN ---------------------------------------------------- Seq Scan on t1 (cost
postgresql----索引失效
weixin_34038293的博客
07-06 690
什么是索引失效？如果where过滤条件设置不合理，即使索引存在，且where过滤条件中包含索引列，也会导致全表扫描，索引不起作用。什么条件下会导致索引失效呢？ 1.任何计算、函数、类型转换 2.!= 3.NOT，相当于使用函数 4.模糊查询通配符在开头 5.索引字段在表中占比较高 6.多字段btree索引查询条件不包含第一列 7.多字段索引查询条件使用OR（有时也会走索引扫描，但查询...
MySQL索引失效的10种场景
最新发布
hong4760的专栏
04-03 1469
MySQL索引，B+树，索引失效
PostgreSQL 不能索引的10个原因
snowyar的专栏
07-26 5682
但是需要注意的是，为了个别的SQL，去盲目改变这些参数的值，往往是得不偿失的，操作的时候需要严谨并且仔细考虑，否则，这些类型的参数的改变，对于数据库的性能影响可能是巨大的。如果索引列中有大量重复的数据，或者一个字段全是一个值，这个时候，索引可能并不能发挥它的作用，起到加快检索的作用，因为这个索引并不能显著地减少需要扫描的行数，所以计算的代价可能远远大于走别的执行计划的代价。关闭对应的优化器选项，可以看到CBO受到设置的参数的影响，选择了seq scan的执行计划，而没有用到字段上的索引。
PostgreSql 索引失效
azhegps的博客
07-27 9113
什么是索引失效？如果where过滤条件设置不合理，即使索引存在，且where过滤条件中包含索引列，也会导致全表扫描，索引不起作用。什么条件下会导致索引失效呢？ 1、任何计算、函数、类型转换 2、!= 3、NOT，相当于使用函数 4、模糊查询通配符在开头 5、索引字段在表中占比较高 6、多字段btree索引查询条件不包含第一列 7、多字段索引查询条件使用OR（有时也会走索引扫描，但查询效率不高） ...
PostgreSQL 如何应对因查询条件变化频繁导致的索引失效？
2401_86074221的博客
07-17 1265
在数据库管理中，因查询条件变化频繁导致的索引失效是一个常见的问题。通过合理设计索引、使用覆盖索引、定期重建索引、使用分区表和优化查询语句等措施，我们可以有效地解决这个问题，提高数据库的性能。就像一场战斗，我们需要根据敌人的情况，灵活运用各种战术，才能取得胜利。在数据库管理中，我们也需要根据实际情况，选择合适的解决方案，才能保证数据库的高效运行。🎉相关推荐🍅关注博主🎗️带你畅游技术世界，不错过每一次成长机会！📚领书：PostgreSQL 入门到精通.pdf📙PostgreSQL 中文手册。
postgresql 索引状态_postgreSql索引没有被用到?
weixin_39620099的博客
12-18 635
我现在有一个数据库, 表 notice 大概 100W row 左右的数据表结构如下:CREATE TABLE public.notice(id integer NOT NULL DEFAULT nextval('notice_id_seq'::regclass),title character varying(200) COLLATE pg_catalog."default" NOT NULL,...
PostgreSql中使用to_char函数、date()函数可能会导致索引无法充分利用，导致查询速度无法提升
番茄Salad
06-13 1330
这时候去搜了一下才知道pgsql的to_char函数可能会导致索引无法充分利用，也就是说必须把to_char改成TIMESTAMP去定义日期。在我的SQL语句中，to_char(c1.binder_gen_time, ‘YYYY-MM-DD’) 会将 binder_gen_time 转换为字符串，这会导致无法使用日期列上的索引。使用 to_char 函数可能会导致索引无法充分利用的情况，因为函数会改变列的数据类型，这样就无法使用索引来加速查询。查询时间花费了4s左右。
为什么pgsql(内关联查询或者with字句时)会导致索引失效
weixin_48811255的博客
01-30 1385
pgsql索引失效
PG索引失败排查记录
sunquan291的专栏
08-25 2937
首先系统使用的是postgre数据库，突然业务逻辑中存在性能问题，最终慢查询定位后，跟踪到有一个过滤查询，耗时严重。如果只有前模糊查询需求(字符串 like ‘xx%’)，使用collate "C"的b-tree索引；当collate不为"C"时，可以使用类型对应的pattern ops(例如text_pattern_ops)建立b-tree索引。
Oracle分区表删除分区数据时导致索引失效解决
崔向阳@李晓的博客
08-05 2260
问题描述： 某表数据量在1亿左右，通过时间在建表时创建了分区，现在因为业务需要，需要删除某个时间段内的数据，如果要是用delete去删除这么多的数据，我还要写存储过程，分批提交的这样做，因此使用删除分区数据的方式。就是这样的一简单的truncate partition 引发了后继的业务故障。最终查询到该表的索引失效，重建立后恢复。 原因分析： 在使用truncate partit...
pgsql索引无法命中问题
newlilu的博客
06-08 1054
pgsql模糊查询无法命中索引
PostgreSql查看索引
snn1410的专栏
07-17 8828
PostgreSQL .   select * from pg_indexes where tablename='log';   或select * from pg_statio_all_indexes where relname='log';   详情请参考http://haohetao.iteye.com/blog/800749 里面有PDO数据库笔记和php相关知识
mysql索引失效的情况以及left join关联字段字符集排序规则造成索引失效
健康平安的活着的专栏
07-09 3378
CREATE TABLE `tb_user` ( `id` int(10) NOT NULL AUTO_INCREMENT, `u_name` varchar(255) DEFAULT NULL, `address` varchar(255) DEFAULT NULL, `create_time` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `n_index` (`u_name`) USING BTREE COMMENT '加快检索'
sql 关联查询索引不生效的原因
yuan_sun的博客
10-31 1432
sql语句如下： SELECT a.id, a.CODE, a.id, b.CODE FROM table_1 a LEFT JOIN table_2 b ON a.CODE = b.CODE 问题：关联sql查询时发现索引无效 原因：表a字段的code和表b字段的code字符集编码不一样导致索引失效 ...
【PG里常见的字段有索引但没有使用索引的原因】
小怪兽的博客
05-05 2796
对于一个系统而言，稳定性、性能、安全是很重要的几点。运维的一些工作也是围绕着这些去做。对于某些时候，业务层可能会向数据库层提出种种质疑：为什么数据库这么慢？为什么数据库挂了？为什么我这么用，SQL走不了索引？诸如此类。其实对于了解数据库和运维的大家都知道，这些使用关系型数据库的应用系统，SQL语句的好坏会直接影响系统的性能，很多系统性能很差最后发现都是因为SQL写得很烂的缘故。
postgresql学习笔记之查询不走索引的情况
weixin_44847119的博客
09-08 6942
查询不走索引的情况： 1、条件字段选择性弱，查出的结果集较大，不走索引； 2、where条件等号两边字段类型不同，不走索引； 3、索引字段 is null 不走索引； 4、对于count(*)当索引字段有not null约束时走索引，否则不走索引； 5、like 后面的字符当首位为通配符时不走索引； 6、使用不等于操作符如：<>、!= 等不走索引； 7、索引字段前加了函数或参加了运算不走索引； 8，部分索引但查询条件包括不属于部分索引的数据 如果where条件都没有以上所述，那么考虑优化器分析的
postgresql 索引失效
01-23
在PostgreSQL中，索引失效可能有多种原因。以下是一些常见的原因： 1. 索引重复：在同一列上创建多个相同的索引可能会导致索引失效。这可能会导致性能问题，因为查询优化器可能会选择错误的索引。 2. 数据分布不...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值