AI 助理
备案控制台
开发者社区 数据库 关系型数据库 文章 正文

PostgreSQL开发小讲堂 - 不等于、数组不包含 要不要用索引？

2017-01-06 5374
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云原生数据库 PolarDB 分布式版，标准版 2核8GB
推荐场景：
使用PolarDB-X与Flink搭建实时数据大屏
RDS PostgreSQL Serverless，0.5-4RCU 50GB 3个月
推荐场景：
对影评进行热评分析
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
简介：

标签

PostgreSQL , 不等于 , 索引 , 外连接

背景

在数据库中不等于能不能走索引呢？理论上是不行的，但是有方法可以让不等于也走索引(虽然走索引也不一定好)。

比如使用外连接实现(需要表有PK才行，没有PK可以使用行号代替)，或者使用not exists，或者使用partial index(不支持变量)。

对于返回结果集很大的场景，建议使用游标分页返回，此时可能用全表扫描更适合。

例子1, 有PK

create table tbl_pk(id int primary key, c1 int);  
create index idx_tbl_pk on tbl_pk(c1);  
insert into tbl_pk select generate_series(1,1000000), random()*10000;

原始方法，不能走索引
select * from tbl_pk where c1 <> 1;

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tbl_pk where c1 <> 1;

QUERY PLAN

Seq Scan on public.tbl_pk (cost=0.00..16925.00 rows=999902 width=8) (actual time=0.020..182.603 rows=999919 loops=1)
Output: id, c1
Filter: (tbl_pk.c1 <> 1)
Rows Removed by Filter: 81
Buffers: shared hit=4425
Planning time: 0.486 ms
Execution time: 249.335 ms
(7 rows)

让他走索引的写法

postgres=# explain (analyze,verbose,timing,costs,buffers) select t1.* from tbl_pk t1 left join tbl_pk t2 on (t1.id=t2.id and t2.c1=1) where t2.* is null;  
                                                            QUERY PLAN                                                               
-----------------------------------------------------------------------------------------------------------------------------------  
 Hash Left Join  (cost=352.55..18528.53 rows=5000 width=8) (actual time=0.229..395.158 rows=999919 loops=1)  
   Output: t1.id, t1.c1  
   Hash Cond: (t1.id = t2.id)  
   Filter: (t2.* IS NULL)  
   Rows Removed by Filter: 81  
   Buffers: shared hit=4509  
   ->  Seq Scan on public.tbl_pk t1  (cost=0.00..14425.00 rows=1000000 width=8) (actual time=0.009..128.644 rows=1000000 loops=1)  
         Output: t1.id, t1.c1  
         Buffers: shared hit=4425  
   ->  Hash  (cost=351.32..351.32 rows=98 width=36) (actual time=0.211..0.211 rows=81 loops=1)  
         Output: t2.id, t2.*  
         Buckets: 1024  Batches: 1  Memory Usage: 6kB  
         Buffers: shared hit=84  
         ->  Bitmap Heap Scan on public.tbl_pk t2  (cost=5.18..351.32 rows=98 width=36) (actual time=0.044..0.185 rows=81 loops=1)  
               Output: t2.id, t2.*  
               Recheck Cond: (t2.c1 = 1)  
               Heap Blocks: exact=81  
               Buffers: shared hit=84  
               ->  Bitmap Index Scan on idx_tbl_pk  (cost=0.00..5.16 rows=98 width=0) (actual time=0.025..0.025 rows=81 loops=1)  
                     Index Cond: (t2.c1 = 1)  
                     Buffers: shared hit=3  
 Planning time: 0.289 ms  
 Execution time: 461.386 ms  
(23 rows)  

postgres=# set enable_seqscan=off;  
SET  

postgres=# explain (analyze,verbose,timing,costs,buffers) select t1.* from tbl_pk t1 left join tbl_pk t2 on (t1.id=t2.id and t2.c1=1) where t2.* is null;  
                                                                      QUERY PLAN                                                                        
------------------------------------------------------------------------------------------------------------------------------------------------------  
 Merge Left Join  (cost=354.99..33264.46 rows=5000 width=8) (actual time=0.224..482.241 rows=999919 loops=1)  
   Output: t1.id, t1.c1  
   Merge Cond: (t1.id = t2.id)  
   Filter: (t2.* IS NULL)  
   Rows Removed by Filter: 81  
   Buffers: shared hit=7244  
   ->  Index Scan using tbl_pk_pkey on public.tbl_pk t1  (cost=0.42..30408.42 rows=1000000 width=8) (actual time=0.008..215.816 rows=1000000 loops=1)  
         Output: t1.id, t1.c1  
         Buffers: shared hit=7160  
   ->  Sort  (cost=354.57..354.81 rows=98 width=36) (actual time=0.211..0.233 rows=81 loops=1)  
         Output: t2.id, t2.*  
         Sort Key: t2.id  
         Sort Method: quicksort  Memory: 31kB  
         Buffers: shared hit=84  
         ->  Bitmap Heap Scan on public.tbl_pk t2  (cost=5.18..351.32 rows=98 width=36) (actual time=0.046..0.183 rows=81 loops=1)  
               Output: t2.id, t2.*  
               Recheck Cond: (t2.c1 = 1)  
               Heap Blocks: exact=81  
               Buffers: shared hit=84  
               ->  Bitmap Index Scan on idx_tbl_pk  (cost=0.00..5.16 rows=98 width=0) (actual time=0.026..0.026 rows=81 loops=1)  
                     Index Cond: (t2.c1 = 1)  
                     Buffers: shared hit=3  
 Planning time: 0.275 ms  
 Execution time: 548.991 ms  
(24 rows)  

postgres=# set enable_sort=off;  
SET  
postgres=# explain (analyze,verbose,timing,costs,buffers) select t1.* from tbl_pk t1 left join tbl_pk t2 on (t1.id=t2.id and t2.c1=1) where t2.* is null;  
                                                                      QUERY PLAN                                                                        
------------------------------------------------------------------------------------------------------------------------------------------------------  
 Hash Left Join  (cost=352.97..34511.95 rows=5000 width=8) (actual time=0.235..494.068 rows=999919 loops=1)  
   Output: t1.id, t1.c1  
   Hash Cond: (t1.id = t2.id)  
   Filter: (t2.* IS NULL)  
   Rows Removed by Filter: 81  
   Buffers: shared hit=7244  
   ->  Index Scan using tbl_pk_pkey on public.tbl_pk t1  (cost=0.42..30408.42 rows=1000000 width=8) (actual time=0.008..223.681 rows=1000000 loops=1)  
         Output: t1.id, t1.c1  
         Buffers: shared hit=7160  
   ->  Hash  (cost=351.32..351.32 rows=98 width=36) (actual time=0.218..0.218 rows=81 loops=1)  
         Output: t2.id, t2.*  
         Buckets: 1024  Batches: 1  Memory Usage: 6kB  
         Buffers: shared hit=84  
         ->  Bitmap Heap Scan on public.tbl_pk t2  (cost=5.18..351.32 rows=98 width=36) (actual time=0.051..0.201 rows=81 loops=1)  
               Output: t2.id, t2.*  
               Recheck Cond: (t2.c1 = 1)  
               Heap Blocks: exact=81  
               Buffers: shared hit=84  
               ->  Bitmap Index Scan on idx_tbl_pk  (cost=0.00..5.16 rows=98 width=0) (actual time=0.031..0.031 rows=81 loops=1)  
                     Index Cond: (t2.c1 = 1)  
                     Buffers: shared hit=3  
 Planning time: 0.274 ms  
 Execution time: 560.676 ms  
(23 rows)  

postgres=# set enable_hashjoin=off;  
SET  
postgres=# explain (analyze,verbose,timing,costs,buffers) select t1.* from tbl_pk t1 left join tbl_pk t2 on (t1.id=t2.id and t2.c1=1) where t2.* is null;  
                                                                      QUERY PLAN                                                                        
------------------------------------------------------------------------------------------------------------------------------------------------------  
 Merge Left Join  (cost=0.85..65818.07 rows=5000 width=8) (actual time=1.080..664.991 rows=999919 loops=1)  
   Output: t1.id, t1.c1  
   Merge Cond: (t1.id = t2.id)  
   Filter: (t2.* IS NULL)  
   Rows Removed by Filter: 81  
   Buffers: shared hit=14320  
   ->  Index Scan using tbl_pk_pkey on public.tbl_pk t1  (cost=0.42..30408.42 rows=1000000 width=8) (actual time=0.007..208.319 rows=1000000 loops=1)  
         Output: t1.id, t1.c1  
         Buffers: shared hit=7160  
   ->  Index Scan using tbl_pk_pkey on public.tbl_pk t2  (cost=0.42..32908.43 rows=98 width=36) (actual time=1.067..193.630 rows=81 loops=1)  
         Output: t2.id, t2.*  
         Filter: (t2.c1 = 1)  
         Rows Removed by Filter: 999919  
         Buffers: shared hit=7160  
 Planning time: 0.261 ms  
 Execution time: 732.070 ms  
(16 rows)

例子2, 没有PK

没有PK时，可以使用行号进行关联。

postgres=# create index tbl_pk_idx2 on tbl_pk (ctid);  
CREATE INDEX  

postgres=# explain (analyze,verbose,timing,costs,buffers) select t1.* from tbl_pk t1 left join tbl_pk t2 on (t1.ctid=t2.ctid and t2.c1=1) where t2.* is null;  
                                                                      QUERY PLAN                                                                         
-------------------------------------------------------------------------------------------------------------------------------------------------------  
 Merge Left Join  (cost=0.85..92362.07 rows=5000 width=8) (actual time=1.232..720.467 rows=999919 loops=1)  
   Output: t1.id, t1.c1  
   Merge Cond: (t1.ctid = t2.ctid)  
   Filter: (t2.* IS NULL)  
   Rows Removed by Filter: 81  
   Buffers: shared hit=11585 read=2735  
   ->  Index Scan using tbl_pk_idx2 on public.tbl_pk t1  (cost=0.42..43680.43 rows=1000000 width=14) (actual time=0.041..268.576 rows=1000000 loops=1)  
         Output: t1.id, t1.c1, t1.ctid  
         Buffers: shared hit=7157 read=3  
   ->  Index Scan using tbl_pk_idx2 on public.tbl_pk t2  (cost=0.42..46180.43 rows=98 width=38) (actual time=1.186..213.849 rows=81 loops=1)  
         Output: t2.ctid, t2.*  
         Filter: (t2.c1 = 1)  
         Rows Removed by Filter: 999919  
         Buffers: shared hit=4428 read=2732  
 Planning time: 0.336 ms  
 Execution time: 787.713 ms  
(16 rows)

例子3, 数组查询中的不包含

postgres=# create table tbl_arr(id int[], c1 text);  
CREATE TABLE  
postgres=# insert into tbl_arr select array_agg(100000*random()) from generate_series(1,100);  
INSERT 0 1  
postgres=# insert into tbl_arr select array_agg(100000*random()) from generate_series(1,100);  
INSERT 0 1  
postgres=# insert into tbl_arr select array_agg(100000*random()) from generate_series(1,100);  
INSERT 0 1  
postgres=# insert into tbl_arr select array_agg(100000*random()) from generate_series(1,100);  
INSERT 0 1  
postgres=# insert into tbl_arr select array_agg(100000*random()) from generate_series(1,100);  
INSERT 0 1  
postgres=# insert into tbl_arr select array_agg(100000*random()) from generate_series(1,100);  
INSERT 0 1  
postgres=# insert into tbl_arr select array_agg(100000*random()) from generate_series(1,100);  
INSERT 0 1  
postgres=# insert into tbl_arr select array_agg(100000*random()) from generate_series(1,100);  
INSERT 0 1  
postgres=# insert into tbl_arr select array_agg(100000*random()) from generate_series(1,100);  
INSERT 0 1  
postgres=# insert into tbl_arr select array_agg(100000*random()) from generate_series(1,100);  
INSERT 0 1  
postgres=# create index idx_tbl_arr_1 on tbl_arr using gin (id);  
CREATE INDEX  
postgres=# create index idx_tbl_arr_2 on tbl_arr (ctid);  
CREATE INDEX  

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tbl_arr where not (id @> array[1,2]);  
                                                QUERY PLAN                                                   
-----------------------------------------------------------------------------------------------------------  
 Seq Scan on public.tbl_arr  (cost=0.00..1.12 rows=10 width=64) (actual time=0.017..0.029 rows=10 loops=1)  
   Output: id, c1  
   Filter: (NOT (tbl_arr.id @> '{1,2}'::integer[]))  
   Buffers: shared hit=1  
 Planning time: 0.086 ms  
 Execution time: 0.051 ms  
(6 rows)  

postgres=# explain (analyze,verbose,timing,costs,buffers) select t1.* from tbl_arr t1 left join tbl_arr t2 on (t1.ctid=t2.ctid and t2.id @> array[1,2]) where t2.* is null;  
                                                                QUERY PLAN                                                                   
-------------------------------------------------------------------------------------------------------------------------------------------  
 Merge Left Join  (cost=0.27..24.63 rows=1 width=64) (actual time=0.133..0.137 rows=10 loops=1)  
   Output: t1.id, t1.c1  
   Merge Cond: (t1.ctid = t2.ctid)  
   Filter: (t2.* IS NULL)  
   Buffers: shared hit=3 read=1  
   ->  Index Scan using idx_tbl_arr_2 on public.tbl_arr t1  (cost=0.14..12.29 rows=10 width=70) (actual time=0.108..0.110 rows=10 loops=1)  
         Output: t1.id, t1.c1, t1.ctid  
         Buffers: shared hit=1 read=1  
   ->  Index Scan using idx_tbl_arr_2 on public.tbl_arr t2  (cost=0.14..12.31 rows=1 width=94) (actual time=0.022..0.022 rows=0 loops=1)  
         Output: t2.ctid, t2.*  
         Filter: (t2.id @> '{1,2}'::integer[])  
         Rows Removed by Filter: 10  
         Buffers: shared hit=2  
 Planning time: 0.193 ms  
 Execution time: 0.173 ms  
(15 rows)  

postgres=# explain (analyze,verbose,timing,costs,buffers) select t1.* from tbl_arr t1 where not exists (select 1 from tbl_arr t2 where t1.ctid=t2.ctid and t2.id @> array[1,2]);
                                                                QUERY PLAN                                                                 
-------------------------------------------------------------------------------------------------------------------------------------------
 Merge Anti Join  (cost=0.27..24.63 rows=9 width=64) (actual time=0.023..0.026 rows=10 loops=1)
   Output: t1.id, t1.c1
   Merge Cond: (t1.ctid = t2.ctid)
   Buffers: shared hit=4
   ->  Index Scan using idx_tbl_arr_2 on public.tbl_arr t1  (cost=0.14..12.29 rows=10 width=70) (actual time=0.004..0.005 rows=10 loops=1)
         Output: t1.id, t1.c1, t1.ctid
         Buffers: shared hit=2
   ->  Index Scan using idx_tbl_arr_2 on public.tbl_arr t2  (cost=0.14..12.31 rows=1 width=6) (actual time=0.017..0.017 rows=0 loops=1)
         Output: t2.ctid
         Filter: (t2.id @> '{1,2}'::integer[])
         Rows Removed by Filter: 10
         Buffers: shared hit=2
 Planning time: 0.103 ms
 Execution time: 0.047 ms
(14 rows)
文章标签：
云原生数据库 PolarDB
关系型数据库
索引
PostgreSQL
关键词：
PostgreSQL开发
PostgreSQL索引
PostgreSQL数组
PostgreSQL讲堂
PostgreSQL讲堂索引
相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系，提升产品性能，打磨产品功能，从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手，打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系， 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台，为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案，提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
德哥
目录
相关文章
bjcuug
|
消息中间件 存储 关系型数据库
PostgreSQL技术大讲堂 - 第33讲：并行查询管理
PostgreSQL从小白到专家，技术大讲堂 - 第33讲：并行查询管理
bjcuug
479 1
t-25772328213-6-45193
|
关系型数据库 数据库 C语言
PostgreSQL服务端开发学习 -- Datum
在使用C语言开发PostgreSQL后端、客户端应用时，Datum无处不在，所以必须要对Datum有很清楚的了解。
t-25772328213-6-45193
488 2
bjcuug
|
缓存 关系型数据库 数据库
PostgreSQL技术大讲堂 - 第32讲：数据库参数调整
从零开始学PostgreSQL技术大讲堂 - 第32讲：数据库参数调整
bjcuug
628 2
t-25772328213-6-45193
|
关系型数据库 C语言 PostgreSQL
PostgreSQL服务端开发学习 --- 常用结构及宏定义1
本篇主要讲解使用C语言开发PostgreSQL服务端应用（libpq、自定义函数、扩展）常用到的结构及宏定义。
t-25772328213-6-45193
193 0
sunrr
|
5月前
|
监控 关系型数据库 数据库
PostgreSQL的索引优化策略？
【8月更文挑战第26天】PostgreSQL的索引优化策略？
sunrr
127 1
土木林森
|
5月前
|
SQL 关系型数据库 MySQL
SQL Server、MySQL、PostgreSQL：主流数据库SQL语法异同比较——深入探讨数据类型、分页查询、表创建与数据插入、函数和索引等关键语法差异，为跨数据库开发提供实用指导
【8月更文挑战第31天】SQL Server、MySQL和PostgreSQL是当今最流行的关系型数据库管理系统，均使用SQL作为查询语言，但在语法和功能实现上存在差异。本文将比较它们在数据类型、分页查询、创建和插入数据以及函数和索引等方面的异同，帮助开发者更好地理解和使用这些数据库。尽管它们共用SQL语言，但每个系统都有独特的语法规则，了解这些差异有助于提升开发效率和项目成功率。
土木林森
589 0
pbeskoyq7ffm4
|
5月前
|
关系型数据库 数据库 PostgreSQL
PostgreSQL索引维护看完这篇就够了
PostgreSQL索引维护看完这篇就够了
pbeskoyq7ffm4
380 0
t-25772328213-6-45193
|
关系型数据库 C语言 PostgreSQL
PostgreSQL服务端开发学习 -- fmgr.h
fmgr按官方的解释就是Postgres函数管理器和函数调用接口，在使用C语言开发PostgreSQL后端应用时，所以与backend交互时必须遵循fmgr.h中定义的一些规范。
t-25772328213-6-45193
351 0
bjcuug
|
8月前
|
缓存 运维 关系型数据库
PostgreSQL技术大讲堂 - 第43讲：流复制原理
PostgreSQL技术大讲堂 - 第43讲：流复制原理
bjcuug
299 2
bjcuug
|
SQL JSON 关系型数据库
PostgreSQL技术大讲堂 - 第34讲：调优工具pgBagder部署
PostgreSQL从小白到专家技术大讲堂 - 第34讲：调优工具pgBagder部署
bjcuug
1199 1
数据库

关系型数据库

热门文章

最新文章

  • 1
    德哥的PostgreSQL私房菜 - 史上最屌PG资料合集
  • 2
    什么场景应该用 MongoDB ？
  • 3
    PostgreSQL 如何实现批量更新、删除、插入
  • 4
    PostgreSQL upsert功能(insert on conflict do)的用法
  • 5
    Linux 性能诊断 perf使用指南
  • 6
    PostgreSQL 9种索引的原理和应用场景
  • 7
    PostgreSQL 如何潇洒的处理每天上百TB的数据增量
  • 8
    Jedis常见异常汇总
  • 9
    PostgreSQL 百亿数据 秒级响应 正则及模糊查询
  • 10
    PostgreSQL 触发器用法详解 - 1
  • 1
    RDS PostgreSQL版发布 rds_duckdb 插件！
    17
  • 2
    编程技术全景洞察：多领域知识纵横剖析
    73
  • 3
    MySQL进阶突击系列(05)突击MVCC核心原理 | 左右护法ReadView视图和undoLog版本链强强联合
    27
  • 4
    体验领礼啦！体验自建数据库迁移到阿里云数据库RDS，领取桌面置物架！
    45
  • 5
    MySQL秘籍之索引与查询优化实战指南
    54
  • 6
    【AI应用启航workshop】构建高可用数据库、拥抱AI智能问数
    78
  • 7
    【赵渝强老师】PostgreSQL的参数文件
    86
  • 8
    MySQL进阶突击系列(04)事务隔离级别、AICD、CAP、BASE原则一直搞不懂？ | 看这篇就够了
    69
  • 9
    MySQL进阶突击系列(03) MySQL架构原理solo九魂17环连问 | 给大厂面试官的一封信
    83
  • 10
    卓越效能，极简运维，体验Serverless高可用架构，完成任务可领取转轮日历！
    56

    • 相关产品

  • 云原生数据库 PolarDB
    文档详情 产品详情
  • 云数据库 RDS PostgreSQL 版
    文档详情 产品详情

    • 相关课程

    更多
  • PolarDB for PostgreSQL 开源人才初级认证培训课程
  • PolarDB for PostgreSQL 内核解读系列课程
  • 7天突破PolarDB for PostgreSQL 2022版
  • PolarDB for PostgreSQL 入门
  • PostgreSQL云上开发实践
  • PostgreSQL实战进阶

    • 相关电子书

    更多
  • 金融级 PostgreSQL监控及优化
  • PostgreSQL在哈啰的实践-周飞
  • PostgreSQL高并发数据库应用数据

    • 相关实验场景

    更多
  • PolarDB for PostgreSQL 闪回特性体验
  • 函数计算X RDS PostgreSQL，基于LLM大语言模型构建AI知识库
  • SQL进阶之约束、索引
  • 搭建PostgreSQL主从架构
  • AnalyticDB PostgreSQL版向量索引查询
  • 如何开通PostgreSQL的专属集群

    • 推荐镜像

    更多
  • postgresql
  • mysql
  • mongodb
    • 下一篇
    开通oss服务