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

PostgreSQL的hash索引是否有用?

2014-11-25 937
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云原生数据库 PolarDB MySQL 版，通用型 2核4GB 50GB
云原生数据库 PolarDB PostgreSQL 版，标准版 2核4GB 50GB
简介： 根据PostgreSQL的手册，PostgreSQL中hash索引有很大的缺陷，不推荐使用。 http://58.58.27.50:8079/doc/html/9.3.1_zh/indexes-types.html -----------------------------------------------------------------------------Hash 索引操作目前没有记录 WAL 日志，因此如果数据库崩溃有未写入的改变， 我们可能需要用REINDEX重建 Hash 索引。
根据PostgreSQL的手册，PostgreSQL中hash索引有很大的缺陷，不推荐使用。

http://58.58.27.50:8079/doc/html/9.3.1_zh/indexes-types.html
-----------------------------------------------------------------------------
Hash 索引操作目前没有记录 WAL 日志，因此如果数据库崩溃有未写入的改变， 我们可能需要用REINDEX重建 Hash 索引。另外， 对hash索引的改变在初始的基础备份后不是基于流复制或者基于文件复制的， 所以对于随后使用它们的查询会给出错误的回复。因为这些原因， 我们并不鼓励使用 Hash 索引。
-----------------------------------------------------------------------------
并且hash索引只支持"等于"查询，而PostgreSQL中作为默认索引类型的btree能支持包括“等于”在内的各种比较操作，功能上覆盖了hash索引。因此，在PostgreSQL中，对常规数据类型，btree索引是不二之选。（相比Oracle中有过于丰富的索引类型可选择，在PostgreSQL中建索引是不是更轻松一点？）

那么，既然不推荐使用hash索引，PostgreSQL为什么还要实现hash索引呢？
PostgreSQL的hash索引使用的线性hash算法，可以动态扩展桶数，理论上的查询复杂度接近O(1)，这是btree无法比拟的。下面做个实测，也证明了在大数量的情况下，hash索引的性能确实明显优于btree。所以在某些极少的特殊场景下，hash索引也还是可以考虑的。

环境

测试环境在Linux虚拟机上
宿主机
OS:Win7 64bit
CPU:AMD Athlon II X4 640 3.0G
内存:6G
硬盘:Apacer A S510S 128G SATA

虚拟机
OS:CentOS 6.5
CPU:x4核
MEM:2G
PostgreSQL:9.3( shared_buffers=128M )

测试方法

使用pgbench的TCP-B和Select Only测试对比性能。
分别建2个测试数据库db1和db2。db1是原始的pgbench测试数据库，即btree索引。db2则通过下面的方法修改为hash索引。

  1. [chenhj@hanode1 ~]$ psql db2
  2. psql (9.3.4)
  3. Type "help" for help.

  5. db2=# alter table pgbench_accounts DROP CONSTRAINT pgbench_accounts_pkey;
  6. ALTER TABLE
  7. db2=# \d pgbench_accounts
  8.    Table "public.pgbench_accounts"
  9.   Column | Type | Modifiers
  10. ----------+---------------+-----------
  11.  aid | integer | not null
  12.  bid | integer |
  13.  abalance | integer |
  14.  filler | character(84) |

  16. db2=# create index on pgbench_accounts using hash(aid);
  17. CREATE INDEX

测试结果

sclae=1时，差异不大；sclae=100时，hash几乎是btree的两倍性能。可见数据量越大，hash的性能优势越明显。


sclae=1时，无太大差异
数据略


sclae=10时，大概快了20%

点击(此处)折叠或打开

  1. [chenhj@hanode1 ~]$ pgbench -n -l -c 1 -T 3 db1
  2. transaction type: TPC-B (sort of)
  3. scaling factor: 10
  4. query mode: simple
  5. number of clients: 1
  6. number of threads: 1
  7. duration: 3 s
  8. number of transactions actually processed: 641
  9. tps = 213.418248 (including connections establishing)
  10. tps = 213.716676 (excluding connections establishing)
  11. [chenhj@hanode1 ~]$ pgbench -n -l -c 1 -T 3 db2
  12. transaction type: TPC-B (sort of)
  13. scaling factor: 10
  14. query mode: simple
  15. number of clients: 1
  16. number of threads: 1
  17. duration: 3 s
  18. number of transactions actually processed: 653
  19. tps = 217.440022 (including connections establishing)
  20. tps = 217.770471 (excluding connections establishing)

  22. [chenhj@hanode1 ~]$ pgbench -n -l -c 16 -T 3 db1
  23. transaction type: TPC-B (sort of)
  24. scaling factor: 10
  25. query mode: simple
  26. number of clients: 16
  27. number of threads: 1
  28. duration: 3 s
  29. number of transactions actually processed: 2064
  30. tps = 683.721499 (including connections establishing)
  31. tps = 701.078622 (excluding connections establishing)
  32. [chenhj@hanode1 ~]$ pgbench -n -l -c 16 -T 3 db2
  33. transaction type: TPC-B (sort of)
  34. scaling factor: 10
  35. query mode: simple
  36. number of clients: 16
  37. number of threads: 1
  38. duration: 3 s
  39. number of transactions actually processed: 3278
  40. tps = 1085.589348 (including connections establishing)
  41. tps = 1110.521633 (excluding connections establishing)

  43. [chenhj@hanode1 ~]$ pgbench -n -l -c 1 -T 3 -S db1
  44. transaction type: SELECT only
  45. scaling factor: 10
  46. query mode: simple
  47. number of clients: 1
  48. number of threads: 1
  49. duration: 3 s
  50. number of transactions actually processed: 4922
  51. tps = 1640.242391 (including connections establishing)
  52. tps = 1642.450916 (excluding connections establishing)
  53. [chenhj@hanode1 ~]$ pgbench -n -l -c 1 -T 3 -S db2
  54. transaction type: SELECT only
  55. scaling factor: 10
  56. query mode: simple
  57. number of clients: 1
  58. number of threads: 1
  59. duration: 3 s
  60. number of transactions actually processed: 4771
  61. tps = 1590.028578 (including connections establishing)
  62. tps = 1592.541658 (excluding connections establishing)

  64. [chenhj@hanode1 ~]$ pgbench -n -l -c 16 -T 3 -S db1
  65. transaction type: SELECT only
  66. scaling factor: 10
  67. query mode: simple
  68. number of clients: 16
  69. number of threads: 1
  70. duration: 3 s
  71. number of transactions actually processed: 32188
  72. tps = 10724.978992 (including connections establishing)
  73. tps = 10969.036021 (excluding connections establishing)

  75. [chenhj@hanode1 ~]$ pgbench -n -l -c 16 -T 3 -S db2
  76. transaction type: SELECT only
  77. scaling factor: 10
  78. query mode: simple
  79. number of clients: 16
  80. number of threads: 1
  81. duration: 3 s
  82. number of transactions actually processed: 33367
  83. tps = 11092.571963 (including connections establishing)
  84. tps = 11339.707975 (excluding connections establishing)

sclae=100时，大概快了1倍

点击(此处)折叠或打开

  1. [chenhj@hanode1 ~]$ pgbench -n -l -c 1 -T 3 -S db1
  2. transaction type: SELECT only
  3. scaling factor: 100
  4. query mode: simple
  5. number of clients: 1
  6. number of threads: 1
  7. duration: 3 s
  8. number of transactions actually processed: 1324
  9. tps = 441.307443 (including connections establishing)
  10. tps = 442.905056 (excluding connections establishing)
  11. [chenhj@hanode1 ~]$ pgbench -n -l -c 1 -T 3 -S db2
  12. transaction type: SELECT only
  13. scaling factor: 100
  14. query mode: simple
  15. number of clients: 1
  16. number of threads: 1
  17. duration: 3 s
  18. number of transactions actually processed: 3226
  19. tps = 1075.038414 (including connections establishing)
  20. tps = 1076.599774 (excluding connections establishing)

  22. [chenhj@hanode1 ~]$ pgbench -n -l -c 16 -T 3 db1
  23. transaction type: TPC-B (sort of)
  24. scaling factor: 100
  25. query mode: simple
  26. number of clients: 16
  27. number of threads: 1
  28. duration: 3 s
  29. number of transactions actually processed: 2194
  30. tps = 725.580208 (including connections establishing)
  31. tps = 741.318899 (excluding connections establishing)
  32. [chenhj@hanode1 ~]$ pgbench -n -l -c 16 -T 3 db2
  33. transaction type: TPC-B (sort of)
  34. scaling factor: 100
  35. query mode: simple
  36. number of clients: 16
  37. number of threads: 1
  38. duration: 3 s
  39. number of transactions actually processed: 3174
  40. tps = 1049.927110 (including connections establishing)
  41. tps = 1073.139131 (excluding connections establishing)

  43. [chenhj@hanode1 ~]$ pgbench -n -l -c 1 -T 3 -S db1
  44. transaction type: SELECT only
  45. scaling factor: 100
  46. query mode: simple
  47. number of clients: 1
  48. number of threads: 1
  49. duration: 3 s
  50. number of transactions actually processed: 1385
  51. tps = 461.481151 (including connections establishing)
  52. tps = 462.109984 (excluding connections establishing)
  53. [chenhj@hanode1 ~]$ pgbench -n -l -c 1 -T 3 -S db2
  54. transaction type: SELECT only
  55. scaling factor: 100
  56. query mode: simple
  57. number of clients: 1
  58. number of threads: 1
  59. duration: 3 s
  60. number of transactions actually processed: 3527
  61. tps = 1175.124543 (including connections establishing)
  62. tps = 1177.144768 (excluding connections establishing)

  64. [chenhj@hanode1 ~]$ pgbench -n -l -c 16 -T 3 -S db1
  65. transaction type: SELECT only
  66. scaling factor: 100
  67. query mode: simple
  68. number of clients: 16
  69. number of threads: 1
  70. duration: 3 s
  71. number of transactions actually processed: 6313
  72. tps = 2092.075168 (including connections establishing)
  73. tps = 2137.468368 (excluding connections establishing)
  74. [chenhj@hanode1 ~]$ pgbench -n -l -c 16 -T 3 -S db2
  75. transaction type: SELECT only
  76. scaling factor: 100
  77. query mode: simple
  78. number of clients: 16
  79. number of threads: 1
  80. duration: 3 s
  81. number of transactions actually processed: 14414
  82. tps = 4775.127727 (including connections establishing)
  83. tps = 4886.910106 (excluding connections establishing)

2种索引的大小差不多

  1. db1=# select pg_table_size('pgbench_accounts_pkey'::regclass);
  2.  pg_table_size
  3. ---------------
  4.      224641024
  5. (1 row)

  7. db2=# select pg_table_size('pgbench_accounts_aid_idx'::regclass);
  8.  pg_table_size
  9. ---------------
  10.      268451840
  11. (1 row)


文章标签：
云原生数据库 PolarDB
索引
关系型数据库
测试技术
数据库
PostgreSQL
Linux
Oracle
算法
关键词：
PostgreSQL索引
云数据库 RDS PostgreSQL 版索引
PostgreSQL hash
云数据库 RDS PostgreSQL 版hash
相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系，提升产品性能，打磨产品功能，从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手，打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系， 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台，为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案，提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
小桥河西
目录
相关文章
sunrr
|
5月前
|
监控 关系型数据库 数据库
PostgreSQL的索引优化策略？
【8月更文挑战第26天】PostgreSQL的索引优化策略？
sunrr
118 1
土木林森
|
5月前
|
SQL 关系型数据库 MySQL
SQL Server、MySQL、PostgreSQL：主流数据库SQL语法异同比较——深入探讨数据类型、分页查询、表创建与数据插入、函数和索引等关键语法差异，为跨数据库开发提供实用指导
【8月更文挑战第31天】SQL Server、MySQL和PostgreSQL是当今最流行的关系型数据库管理系统，均使用SQL作为查询语言，但在语法和功能实现上存在差异。本文将比较它们在数据类型、分页查询、创建和插入数据以及函数和索引等方面的异同，帮助开发者更好地理解和使用这些数据库。尽管它们共用SQL语言，但每个系统都有独特的语法规则，了解这些差异有助于提升开发效率和项目成功率。
土木林森
553 0
pbeskoyq7ffm4
|
5月前
|
关系型数据库 数据库 PostgreSQL
PostgreSQL索引维护看完这篇就够了
PostgreSQL索引维护看完这篇就够了
pbeskoyq7ffm4
370 0
bjcuug
|
存储 关系型数据库 数据库
PostgreSQL技术大讲堂 - 第28讲：索引内部结构
从零开始学PostgreSQL技术大讲堂 - 第28讲：索引内部结构
bjcuug
795 2
猫头虎
|
关系型数据库 Go 数据库
《提高查询速度：PostgreSQL索引实用指南》
《提高查询速度：PostgreSQL索引实用指南》
猫头虎
600 0
带你读小助手
|
关系型数据库 分布式数据库 数据库
PolarDB for PostgreSQL 14：全局索引
PolarDB for PostgreSQL 14 相较于 PostgreSQL 14，提供了更多企业级数据库的特性。本实验将体验其中的全局索引功能。
带你读小助手
829 0
带你读小助手
|
弹性计算 关系型数据库 OLAP
AnalyticDB PostgreSQL版向量索引查询
本案例对比了传统查询和使用向量索引执行查询的执行时间，助您体验使用向量索引查询带来的高效和快捷。
带你读小助手
879 0
yzshover
|
存储 SQL 关系型数据库
PostgreSQL插件HypoPG：支持虚拟索引
PostgreSQL插件HypoPG：支持虚拟索引
yzshover
422 0
yzshover
|
存储 缓存 关系型数据库
PostgreSQL 14新特性--减少索引膨胀
PostgreSQL 14新特性--减少索引膨胀
yzshover
499 0
yzshover
|
关系型数据库 PostgreSQL 索引
PostgreSQL通过索引获取heap tuple解析
PostgreSQL通过索引获取heap tuple解析
yzshover
184 0

热门文章

最新文章

  • 1
    【赵渝强老师】PostgreSQL的段、区和块
  • 2
    【赵渝强老师】PostgreSQL的控制文件
  • 3
    【赵渝强老师】PostgreSQL的服务器日志文件
  • 4
    【赵渝强老师】PostgreSQL中的模式
  • 5
    【赵渝强老师】PostgreSQL的参数文件
  • 6
    【赵渝强老师】PostgreSQL的数据库
  • 7
    【赵渝强老师】PostgreSQL的WAL预写日志文件
  • 8
    【赵渝强老师】PostgreSQL的表空间
  • 9
    【赵渝强老师】PostgreSQL的运行日志文件
  • 10
    【赵渝强老师】PostgreSQL的物理存储结构
  • 1
    玩转阿里云RDS PostgreSQL数据库通过pg_jieba插件进行分词
    692
  • 2
    使用ECS和RDS搭建个人博客
    1341
  • 3
    PolarDB | PostgreSQL 高并发队列处理业务的数据库性能优化实践
    869
  • 4
    【ECS生长万物之开源】如何一键本地部署PolarDB for PostgreSQL
    1174
  • 5
    NineData已支持「最受欢迎数据库」PostgreSQL
    366
  • 6
    postgresql snapshot 快照源码解读
    732
  • 7
    如何使用AnalyticDB PostgreSQL 版实现“一站式全文检索”业务
    32344
  • 8
    将 PostgreSQL 迁移到 MySQL 数据库
    1794
  • 9
    flink postgresql cdc实时同步（含pg安装配置等）
    3472
  • 10
    PostgreSQL内存上下文[翻译]
    301

    • 相关课程

    更多
  • 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语句实现数据插入、修改和删除操作
  • 使用SQL语句管理索引
  • SQL进阶之约束、索引
  • 搭建PostgreSQL主从架构

    • 推荐镜像

    更多
  • postgresql
  • mongodb
  • mysql
    • 下一篇
    阿里云无影云电脑免费试用，最长可试用3个月