首页 / POSTGRCSQL / postgresql----索引失效

postgresql----索引失效

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了postgresql----索引失效，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含12371字，纯文字阅读大概需要18分钟。

内容图文

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

postgresql----索引失效

标签：

本文系统来源：http://www.cnblogs.com/alianbog/p/5648455.html

内容总结

以上是互联网集市为您收集整理的postgresql----索引失效全部内容，希望文章能够帮你解决postgresql----索引失效所遇到的程序开发问题。如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/466827.html

来源：【匿名】

【上一篇】postgresql----时间类型【下一篇】PDO扩展连接PostgreSQL对象关系数据库步骤详解

更多 ►

【postgresql----索引失效】教程文章相关的互联网学习教程文章

PostgreSQL代码分析，查询优化部分，pull_ands()和pull_ors()【图】

PostgreSQL代码分析，查询优化部分。这里把规范谓词表达式的部分就整理完了，阅读的顺序例如以下：一、PostgreSQL代码分析，查询优化部分，canonicalize_qual二、PostgreSQL代码分析，查询优化部分，pull_ands()和pull_ors()三、PostgreSQL代码分析，查询优化部分，process_duplicate_ors*************************************************************************************************************************************...

科学地增加postgresql最大连接数【代码】

PG配置文件路径/etc/postgresql/9.3/main/postgresql.conf首先如何查看最大连接数This SQL will help youselect max_conn,used,res_for_super,max_conn-used-res_for_super res_for_normal from (select count(*) used from pg_stat_activity) t1,(select setting::int res_for_super from pg_settings where name=$$superuser_reserved_connections$$) t2,(select setting::int max_conn from pg_settings where name=$$max_conn...

postgresql 10 安装【代码】【图】

windows 安装第一步：下载https://www.enterprisedb.com/download-postgresql-binaries将解压后的文件去掉一层放入 E:\Program Files，程序目录为 E:\Program Files\pgsql第二步：解压压缩包，配置环境变量新建数据目录 E:\postgresql\11\pgsql\data （这是数据库的数据存储文件夹）在 E:\Program Files\pgsql 新建一个名为env.vbs的文件on error resume next set sysenv=CreateObject("WScript.Shell").Environment("system")...

PostgreSQL统计每天的记录数量,没有记录就为0或返回null【代码】【图】

1. 需求统计下面事件类型的数量，没有此类型没有数据的返回为null，前端显示为0，请写出PostgreSQL的sql SQL语句是：select t.event_code as eventCode,t.event_name as eventName,s.eventNum from event_type t LEFTJOIN (select s.parent_codeas eventCode,count(id) as eventNumfrom article s where1=1groupby s.parent_code orderby eventNum desc) s on s.eventCode=t.event_code where t.parent_code=‘0‘显示结果：...

PostgreSQL学习手册(目录)

原文地址：http://www.cnblogs.com/stephen-liu74/archive/2012/06/08/2315679.html 事实上之前有很长一段时间都在纠结是否有必要好好学习它，但是始终都没有一个很好的理由说服自己。甚至是直到这个项目最终决定选用PostgreSQL时，我都没有真正意识到学习它的价值，当时只是想反正和其它数据库差不多，能用就行了。然而有一天晚上，自己也不知道为什么，躺在床上开始回想整个项目的实施过程，想着想着就想到了数据库选型这一问...

PostgreSQL备机checkpoint【代码】

数据库异常关闭时，数据库关闭时来不及或者没机会做checkpoint，则需要从上一个一致性检查的开始恢复。 ? ? PostgreSQL备机checkpoint是不能产生checkpoint WAL的，因为如果写这样类型的checkpoint的话，就会将接收的WAL打乱，那么日志将混乱，回放会出问题。? ? 那么问题来了，备机支持checkpoint吗？他的checkpoint怎么做的？? ? PostgreSQL为了缩短恢复时间，备机上也支持checkpoint，即CreateRestartPoint。但是其pg_control文...

构建基于CentOS 7.6 的PostgreSQL 11.6 镜像【代码】

案例说明： 1）CentOS 7.6的基础镜像上创建PostgreSQL 11.6镜像。2）创建PostgreSQL 11.6 镜像的Dockerfile。3）下载PostgreSQL 11.6的源码包和Dockerfile存放在相同的目录下 1、启动docker服务[root@node1 soft]# systemctl start docker[root@node1 soft]# systemctl status docker ● docker.service - Docker Application Container EngineLoaded: loaded (/usr/lib/systemd/system/docker.service; disabled; vendor pr...

PostgreSQL服务端监听设置及client连接方法

背景介绍： PostgreSQL服务端执行在RedHat Linux上,IP为：192.168.230.128 client安装在Windows XP上， IP为：192.168.230.1配置方法： 1. 改动服务端/opt/postgresql/data/postgresql.conf文件,将#listen_address=‘localhost‘改成listen_address=‘*‘ 2. 改动/opt/postgresql/data/gp_hba.conf文件在当中添加client的ip，例如以下,注意最后一行：# TYPE DATABASE USER CIDR-ADDRESS METHOD# "local" i...

java web中向postgreSQL插入当前时间【代码】

方式1 常用插入方式Timestamp currentTime= new Timestamp(System.currentTimeMillis()); String sql = "INSERTINTO message(date_create) VALUES (" + "‘" + currentTime + "‘" + ");";方式2 使用占位符Timestamp currentTime= new Timestamp(System.currentTimeMillis()); String sql = "INSERTINTO message( date_create) VALUES (?);"; PreparedStatement pst = conn.prepareStatement(sql,Statement.RETURN_GENERATE...

PostgreSQL 9.5.4数据库快速INSERT大量数据研究【代码】

PostgreSQL 9.5.4数据库快速INSERT大量数据研究背景在一些应用场景中，需要向Abase数据库中快速装入大量的数据，比如数据库迁移，SQL日志分析等。在PG上快速插入数据有几种方案，每种方案的效率怎么样？如何调优能加快的数据装载？场景设定SQL日志分析是一个采集JDBC日志、分析SQL、发送分析结果工具。在分析阶段，需要解析大量的JDBC日志，并把解析后的结构化结果装入数据库供后续处理。以分析阶段为实验场景，以解析JDBC日志(多个...

PostgreSQL Replication之第三章理解即时恢复（2）

3.2 归档事务日志看过图片之后，我们可以看看如何使这些东西进入工作状态。当谈到及时归档时，您需要做的第一件事是归档XLOG。PostgreSQL通过postgresql.conf提供了所有与归档相关的选项。让我们一步一步地看，要启动归档需要在postgresql.conf中做什么：1. 首先，您应该把archive_mode设置为 on。2. 第二步，您应该配置您的归档命令。归档命令是一个简单的带有两个参数的shell命令：1. %p: 这是一个表示应该被归档的的XLOG的占位符...

linux postgresql 离线安装记录

由于服务器无法连接外网，通过yum指令来安装PostgreSQL的可能性是零，所以需要离线安装操作。下载离线 rpm 包：https://yum.postgresql.org/rpmchart/由于几个rpm彼此之间的依赖关系，所以我安装的先后顺序是：rpm -ivh example.rpm1. postgresql11-libs-11.8-1PGDG.rhel7.x86_64.rpm2. postgresql11-11.8-1PGDG.rhel7.x86_64.rpm3. postgresql11-server-11.8-1PGDG.rhel7.x86_64.rpm4. postgresql11-contrib-11.8-1PGDG.rhel7.x86...

PostgreSQL编译安装【代码】

获取源码略编译安装对于性能型的软件，我们采用编译的方式进行安装。安装依赖yum install -y systemtap-sdt-devel perl-ExtUtils-Embed pam-devel libxml2-devel libxslt-devel python-devel编译./configure --prefix=/opt/pgsql-9.3.2 --with-perl --with-python --with-openssl --with-pam --without-ldap --with-libxml --with-libxslt --enable-thread-safety --with-wal-blocksize=16 --with-blocksize=16 --enable-dtrace -...

PostgreSQL之Buffer Cache【代码】【图】

缓冲区高速缓存（Buffer Cache）位于服务器的共享内存中，并且所有进程均可访问。在读取或更新数据时，进程将页面读入缓存。当页面位于缓存中时，我们在RAM中使用它并保存数据到磁盘。当一个进程需要读取一个页面时，它首先尝试通过哈希表在Buffer Cache中找到它，如果在Buffer Cache中找不到所需的页面。在这种情况下，需要将页面从磁盘读取到某个缓冲区中。 PostgreSQL有一个扩展，使我们能够查看缓冲区高速缓存的内部。安装扩...

postgresql数据迁移

postgresql从库故障准备新库1，创建用户[root@localhost home]# userdel postgres[root@localhost home]# groupdel postgresmkdir /home/mydbchown -R postgres:postgres /home/mydb2，解压安装包[root@localhost home]## tar -xvf postgres/postgresql-8.1.4.tarcd postgres/postgresql-8.1.4编译安装： ./configure --prefix=/usr/local/pgsql -localstatedir=/home/mydb $ ./configure --prefix=/usr/local/pgsql --with-pgco...

首页 / POSTGRCSQL / postgresql----索引失效

postgresql----索引失效

内容导读

内容图文

内容总结

内容备注

内容手机端

【postgresql----索引失效】教程文章相关的互联网学习教程文章

POSTGRESQL - 相关标签

POSTGRCSQL - 技术教程分类

POSTGRCSQL - 最新教程

POSTGRCSQL - 最热教程