postgresql - PostgreSQL:一个索引快而另一个索引慢-6ren

postgresql - PostgreSQL:一个索引快而另一个索引慢

转载作者：行者123 更新时间：2023-11-29 11:48:56

我们有一个4.5亿行的数据库，结构如下：

   uid      id_1     id_2   d1  d2  d3  d4  d5  d6  d7  d8  d9  d10 d11 d12 d13 d14 d15 d16 d17
81038392    5655067 5468882 373 117 185 152 199 173 168 138 185 159 154 34  38  50  34  41  57
81038393    5655067 5468883 374 116 184 118 170 143 144 113 164 137 138 37  39  53  37  42  60
81038394    5655067 5468884 371 118 187 118 170 143 144 105 157 131 136 32  35  47  32  39  53
81038395    5655067 5468885 370 116 184 118 170 143 144 105 157 131 136 31  35  46  31  38  53
81038396    5655067 5468886 370 117 185 118 170 143 144 105 157 131 136 29  34  44  29  37  50
81038397    5655067 5470853 368 117 185 110 163 137 140 105 157 131 136 34  36  48  34  39  55
81038398    5655067 5470854 372 119 188 118 170 143 144 113 164 137 138 34  36  49  34  40  55
81038399    5655067 5470855 360 115 182 103 151 131 136 98  145 125 131 30  34  45  30  38  51
81038400    5655067 5470856 357 112 177 103 151 131 136 98  145 125 131 30  34  45  30  37  51
81038401    5655067 5470857 356 111 176 103 151 131 136 98  145 125 131 28  33  43  28  36  50
81038402    5655067 5470858 358 113 179 103 151 131 136 98  145 125 131 31  35  46  31  38  52
81038403    5655067 5472811 344 109 173 152 199 173 168 138 185 159 154 31  36  46  31  39  52
81038404    5655068 5468882 373 117 185 152 199 173 168 138 185 159 154 34  38  50  34  41  57
81038405    5655068 5468883 374 116 184 118 170 143 144 113 164 137 138 37  39  53  37  42  60
81038406    5655068 5468884 371 118 187 118 170 143 144 105 157 131 136 32  35  47  32  39  53
81038407    5655068 5468885 370 116 184 118 170 143 144 105 157 131 136 31  35  46  31  38  53
81038408    5655068 5468886 370 117 185 118 170 143 144 105 157 131 136 29  34  44  29  37  50
81038409    5655068 5470853 368 117 185 110 163 137 140 105 157 131 136 34  36  48  34  39  55
81038410    5655068 5470854 372 119 188 118 170 143 144 113 164 137 138 34  36  49  34  40  55
81038411    5655068 5470855 360 115 182 103 151 131 136 98  145 125 131 30  34  45  30  38  51
81038412    5655068 5470856 357 112 177 103 151 131 136 98  145 125 131 30  34  45  30  37  51
81038413    5655068 5470857 356 111 176 103 151 131 136 98  145 125 131 28  33  43  28  36  50
81038414    5655068 5470858 358 113 179 103 151 131 136 98  145 125 131 31  35  46  31  38  52

我们需要经常做这样的查询：
问题1：

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM mytable WHERE id_1 = 5655067;

                                                               QUERY PLAN                                                                
-----------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using id_1_idx on mytable (cost=0.57..99187.68 rows=25742 width=80) (actual time=47.081..2600.899 rows=21487 loops=1)
   Index Cond: (id_1 = 5655067)
   Buffers: shared hit=9 read=4816
   I/O Timings: read=2563.181
 Planning time: 0.151 ms
 Execution time: 2602.320 ms
(6 rows)

问题2：

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM mytable WHERE id_2 = 5670433;

                                                            QUERY PLAN                                                             
-----------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on mytable (cost=442.02..89887.42 rows=23412 width=80) (actual time=113.200..42127.512 rows=21487 loops=1)
   Recheck Cond: (id_2 = 5670433)
   Heap Blocks: exact=16988
   Buffers: shared hit=30 read=17020
   I/O Timings: read=41971.798
   ->  Bitmap Index Scan on id_2_idx  (cost=0.00..436.16 rows=23412 width=0) (actual time=104.928..104.929 rows=21487 loops=1)
         Index Cond: (id_2 = 5670433)
         Buffers: shared hit=2 read=60
         I/O Timings: read=99.235
 Planning time: 0.163 ms
 Execution time: 42132.556 ms
(11 rows)

大约有23000到25000种独特的
id_1和 id_2值，这两个查询将始终返回大约24000行数据。我们只读取数据，数据不会随时间变化。
问题是：
查询1大约需要3秒，这是有点多，但仍然可以忍受。
查询2需要30-40秒，这对我们来说太多了，因为该服务是交互式web服务。
我们已经索引了 id_1和 id_2。我们还在 id_1和 id_2上添加了一个联合索引，这是Azure PostgreSQL作为数据所在的服务平台所建议的。它没有帮助。
我的假设是，查询1速度很快，因为所有行都按顺序位于数据库中，而当使用查询2时，行总是按非顺序分布在整个数据库中。
重新构造数据以加快查询2不是一个好主意，因为这样会降低查询1的性能我知道这种数据的结构方式并不理想，但我无法控制它。有什么建议可以加快查询2到合理的速度吗？
编辑2：
创建索引语句：

CREATE INDEX id_1_idx ON mytable (id_1);
CREATE INDEX id_2_idx ON mytable (id_2);

用吸尘器清理桌子并没有改变计划。抽真空后， EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM mytable WHERE id_1 = 5655067的输出非常相似。以下是详细真空的输出：

VACUUM (VERBOSE, ANALYZE) mytable;

INFO:  vacuuming "public.mytable"
INFO:  index "mytable_pkey" now contains 461691169 row versions in 1265896 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 2695.21 s.
INFO:  index "id_1_idx" now contains 461691169 row versions in 1265912 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 1493.20 s.
INFO:  index "id_2_idx" now contains 461691169 row versions in 1265912 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 1296.06 s.
INFO:  index "mytable_id_1_id_2_idx" now contains 461691169 row versions in 1265912 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 2364.16 s.
INFO:  "mytable": found 0 removable, 389040319 nonremovable row versions in 5187205 out of 6155883 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 12767
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 13560.60 s.
INFO:  analyzing "public.mytable"
INFO:  "mytable": scanned 30000 of 6155883 pages, containing 2250000 live rows and 0 dead rows; 30000 rows in sample, 461691225 estimated total rows
VACUUM

最佳答案

TL；博士
存储I/O是您的主要瓶颈+没有足够的RAM用于索引，因为您可以自己简单地计算：
对于位图堆扫描，可以计算大约2.5毫秒（41971.798毫秒内读取17020个块）的平均块读取延迟，这太慢了。
避免磁盘读取的唯一方法是大量RAM。更快的存储将使系统更具可伸缩性，因为这很可能不是数据库中唯一的查询类型，也不是唯一的表。
长版本：
读取EXPLAIN的完美输出表明，规划师所做的成本评估远远不够，性能下降来自磁盘读取。
正如您所写的，数据不会随着时间的推移而改变（因此，您事先知道值的范围），您还可以在这两列上对表进行范围划分，然后这两列只需要扫描特定的分区（使用较小的索引，读取较小的表堆）。但是，如果访问这些数据的应用程序最终访问的数据范围或多或少也不会有太大帮助。
因此，您应该考虑替换存储子系统，以便能够在应用程序的性能要求范围内处理查询。
我怀疑PostgreSQL服务器仍在HDD而不是SSD上运行。只有120M行的小测试显示了这两个索引的以下特性：

create table nums (uid integer primary key, id_1 integer, id_2 integer, d1 integer, d2 integer, d3 integer, d4 integer, d5 integer, d6 integer, d7 integer, d8 integer, d9 integer, d10 integer, d11 integer, d12 integer, d13 integer, d14 integer, d15 integer, d16 integer, d17 integer);

INSERT INTO nums select generate_series(80000001, 200000000) AS uid, (random() * 23000)::integer + 5600000 AS id_1, (random() * 25000)::integer + 5600000 AS id_2, (random() * 1000)::integer AS d1, (random() * 1000)::integer AS d2, (random() * 1000)::integer AS d3, (random() * 1000)::integer AS d4, (random() * 1000)::integer AS d5, (random() * 1000)::integer AS d6, (random() * 1000)::integer AS d7, (random() * 1000)::integer AS d8, (random() * 1000)::integer AS d9, (random() * 1000)::integer AS d10, (random() * 1000)::integer AS d11, (random() * 100)::integer AS d12, (random() * 100)::integer AS d13, (random() * 100)::integer AS d14, (random() * 100)::integer AS d15, (random() * 100)::integer AS d16, (random() * 100)::integer AS d17;

create index id_1_idx on nums (id_1);
create index id_2_idx on nums (id_2);
cluster nums using id_1_idx;

…导致以下结果（两个冷读）：

explain (analyze, buffers) select * from nums where id_1 = 5606001;
                                                       QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
 Index Scan using id_1_idx on nums  (cost=0.57..5816.92 rows=5198 width=80) (actual time=1.680..6.394 rows=5185 loops=1)
   Index Cond: (id_1 = 5606001)
   Buffers: shared read=88
   I/O Timings: read=4.397
 Planning Time: 4.002 ms
 Execution Time: 7.475 ms
(6 rows)

Time: 15.924 ms

…对于 id_2：

explain (analyze, buffers) select * from nums where id_2 = 5606001; 
                                                        QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 Index Scan using id_2_idx on nums  (cost=0.57..5346.53 rows=4777 width=80) (actual time=0.376..985.689 rows=4748 loops=1)
   Index Cond: (id_2 = 5606001)
   Buffers: shared hit=1 read=4755
   I/O Timings: read=972.555
 Planning Time: 0.203 ms
 Execution Time: 986.590 ms
(6 rows)

Time: 987.296 ms

所以虽然我的表是“仅仅”12gib+3x2.5gib（PK+2索引）仍然足够快。
如果服务器已经在SSD上运行，请确保（物理上）为WAL/log、表数据（表空间）、索引（表空间）分离数据存储，以尽可能从并行中获益，并减少同一系统上其他服务/应用程序造成的I/O干扰。
还要考虑一个服务器系统，它的表和索引数据的内存要大得多（对于这个大约48 GiB的表+每个索引大约10 GiB，假设所有 integer列都是），然后进行一次预热，将数据从磁盘推送到内存中。至少索引应该能够完全保留在内存中。
编辑：
我的服务器不使用位图（索引+堆）扫描的原因是我在SSD上运行，并且我已经调整了从默认的 4到 1.1的随机页面开销。当然，对于硬盘系统来说，这毫无意义。
编辑2：
对这种情况的重新测试揭示了一种有趣的行为：
在我的测试中，我假设第一列 uid是主键列，并且是 serial（顺序整数），根据它，条目最初在磁盘上排序。在生成数据时，有趣的索引列 id_1和 id_2的值都是随机生成的，这通常是大型表的最坏情况。
然而，在这种情况下并非如此。在创建测试数据和索引之后，在分析表之后，但在使用列 id_1上的索引重新排序数据之前，我现在得到这些结果：

explain (analyze, buffers) select * from nums where id_1 = 5606001;
                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on nums  (cost=63.32..7761.68 rows=5194 width=80) (actual time=1.978..41.007 rows=5210 loops=1)
   Recheck Cond: (id_1 = 5606001)
   Heap Blocks: exact=5198
   Buffers: shared read=5217
   I/O Timings: read=28.732
   ->  Bitmap Index Scan on id_1_idx  (cost=0.00..62.02 rows=5194 width=0) (actual time=1.176..1.176 rows=5210 loops=1)
         Index Cond: (id_1 = 5606001)
         Buffers: shared read=19
         I/O Timings: read=0.124
 Planning Time: 7.214 ms
 Execution Time: 41.419 ms
(11 rows)

…和：

explain (analyze, buffers) select * from nums where id_2 = 5606001;
                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on nums  (cost=58.52..7133.04 rows=4768 width=80) (actual time=7.305..43.830 rows=4813 loops=1)
   Recheck Cond: (id_2 = 5606001)
   Heap Blocks: exact=4805
   Buffers: shared hit=12 read=4810
   I/O Timings: read=28.181
   ->  Bitmap Index Scan on id_2_idx  (cost=0.00..57.33 rows=4768 width=0) (actual time=5.102..5.102 rows=4813 loops=1)
         Index Cond: (id_2 = 5606001)
         Buffers: shared read=17
         I/O Timings: read=2.414
 Planning Time: 0.227 ms
 Execution Time: 44.197 ms
(11 rows)

此处提供所有计划+优化：
using id_1_idx
using id_2_idx
我还遵循了自己的最佳实践，将索引分离到不同物理SSD上的另一个表空间。
如我们所见，要获取~5000个结果行，它必须在这里或多或少地读取相同数量的块，在这两种情况下都使用位图堆扫描。
在这种情况下，两列的相关性：

 attname | correlation | n_distinct 
---------+-------------+------------
 id_1    |  -0.0047043 |      23003
 id_2    |  0.00157998 |      25004

现在，在 CLUSTER ... USING id_1_idx之后并在重新分析之后重新测试查询，结果如下所示：

 attname | correlation  | n_distinct 
---------+--------------+------------
 id_1    |            1 |      22801
 id_2    | -0.000898521 |      24997

……表现如下：

explain (analyze, buffers) select * from nums where id_1 = 5606001;
                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Index Scan using id_1_idx on nums  (cost=0.57..179.02 rows=5083 width=80) (actual time=2.604..5.256 rows=5210 loops=1)
   Index Cond: (id_1 = 5606001)
   Buffers: shared read=90
   I/O Timings: read=4.107
 Planning Time: 4.039 ms
 Execution Time: 5.563 ms
(6 rows)

……这比预期的要好得多，但是：

explain (analyze, buffers) select * from nums where id_2 = 5606001;
                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on nums  (cost=58.57..7140.12 rows=4775 width=80) (actual time=5.866..99.707 rows=4813 loops=1)
   Recheck Cond: (id_2 = 5606001)
   Heap Blocks: exact=4806
   Buffers: shared read=4823
   I/O Timings: read=31.389
   ->  Bitmap Index Scan on id_2_idx  (cost=0.00..57.38 rows=4775 width=0) (actual time=2.992..2.992 rows=4813 loops=1)
         Index Cond: (id_2 = 5606001)
         Buffers: shared read=17
         I/O Timings: read=0.338
 Planning Time: 0.210 ms
 Execution Time: 100.155 ms
(11 rows)

……速度是第一次随机运行时的两倍多，尽管读取的块数几乎与第一次随机运行时的块数完全相同。
为什么速度慢了这么多？
使用index id_1_idx对表数据进行物理重新排序也会影响列的物理顺序。现在，位图堆扫描的目的是从位图索引扫描中获取按物理（磁盘）顺序读取的块列表。在第一种情况下（随机），很有可能多个行与位于磁盘上连续块中的条件匹配，从而减少随机磁盘访问。
有趣的是（但这可能只是因为我在SSD上运行），禁用位图扫描会显示可接受的数字：

explain (analyze, buffers) select * from nums where id_2 = 5606001;
                                                        QUERY PLAN                                                        
--------------------------------------------------------------------------------------------------------------------------
 Index Scan using id_2_idx on nums  (cost=0.57..7257.12 rows=4775 width=80) (actual time=0.151..35.453 rows=4813 loops=1)
   Index Cond: (id_2 = 5606001)
   Buffers: shared read=4823
   I/O Timings: read=30.051
 Planning Time: 1.927 ms
 Execution Time: 35.810 ms
(6 rows)

所有这些数字几乎都是冷启动执行（如您所见，没有或非常低的 Buffers: shared hit数字。
有趣的是，对于 id_2，位图扫描和索引扫描的I/O计时非常相似，但是位图扫描似乎会带来巨大的开销。

关于postgresql - PostgreSQL:一个索引快而另一个索引慢，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/55001312/

文章推荐： postgresql - Dataproc 上的 Sqoop 无法将数据导出为 Avro 格式

文章推荐： sql - 在同一行中的两个 array_agg 列上相交

文章推荐： postgresql - 将本地数据库迁移到 AWS Aurora

html - Z 索引 - 滑动过渡重叠有没有办法创建动态 z 索引？
这几天我一直在努力。我一直在自学 CSS，所以对菜鸟好一点。我正在创建一个推荐 slider 。推荐以 3 个 block 显示。我希望前 2 个下降，第 3 个上升。但是当 slider 激活时，无
node.js - 索引.ejs VS 索引.html
我最近开始学习 Nodejs，现在我很困惑我的网络应用程序使用什么，html 还是 ejs (Express)。 Ejs 使用 Express 模块，而 .html 使用 HTML 模块。我的第一个问
sql - 跨两列/数组的 PostgreSQL 搜索/索引(GIN 索引？)
假设我们有一个 PostgreSQL 表contacts，每条记录都有一堆带标签的电子邮件地址(标签和电子邮件对)——其中一个是“主要”。存储方式如下: id 主键电子邮件文本 email_la
Tesseract 索引 >= 0 && 索引 < size_used_ :Error:Assert failed Error
我成功为一种新的tesseract语言编写了traineddata文件，但是当我完成时，我继续收到以下错误: index >= 0 && index = 0 && 索引 < size_used_ :E
python - .loc[索引, 列] 和 .loc[索引][列] 之间有什么区别？
这个问题已经有答案了: How to deal with SettingWithCopyWarning in Pandas (21 个回答) 已关闭 4 年前。假设我有一个像这样的数据框，第一列“密
Android - 从位置 A(索引)检查位置 B(索引)是否在 GridView 布局中与它成对角线，而不管是否接近
如果我有一个位置或行/列同时用于 A 和 B 位置，请检查 B 是否与 A 成对角线？ 1 2 3 4 5 6 7 8 9 例如，我如何检查 5 是否与 7 成对角线？此外，如果我检查 4 是
MongoDB：索引
MongoDB：索引一、创建索引默认情况下，集合中的_id字段就是索引，我们可以通过getIndexes()方法来查看一个集合中的索引 > db.user.getIndexes() [ { "v
MongoDB——索引
一、索引介绍索引是一种用来快速查询数据的数据结构。 B+Tree就是一种常用的数据库索引数据结构，MongoDB采用B+Tree 做索引，索引创建在colletions上。 MongoDB不使用索引
SQLite 索引
我无法决定索引。就像我有下面的查询需要太多时间来执行: select count(rn.NODE_ID) as Count, rnl.[ISO_COUNTRY_CODE] as Cou
MySQL查询优化——索引
我有这些表: CREATE TABLE `cstat` ( `id_cstat` bigint(20) NOT NULL, `lang_code` varchar(3) NOT NULL,
mysql表性能升级(索引
我正在尝试找到一种方法来提高包含 IP 范围的 mysql 表的性能(在高峰时段每秒最多有 500 个 SELECT 查询(!)，所以我有点担心)。我有一个这种结构的表: id smallint(
jquery 索引()
jquery index() 似乎无法识别元素之一，总是说“无法读取未定义的属性‘长度’”这是我的代码。mnumber 是导致问题的原因。我需要 number 和 mnumber 才能跟踪使用鼠标，并
MongoDB 索引
我们有一个包含近 4000 万条记录的 MongoDB 集合。该集合的当前大小为 5GB。此集合中存储的数据包含以下字段: _id: "MongoDB id" userid: "user id" (i
MongoDB 索引
文档说:如果你有多个字段的复合索引，你可以用它来查询字段的开始子集。所以如果你有一个索引一个，乙，丙你可以用它查询一种一个，乙a,b,c 我的问题是，如果我有一个像这样的复合索引一个，乙，丙我可以查询
jQuery .each() 索引？
我正在使用 $('#list option').each(function(){ //do stuff }); 循环列表中的选项。我想知道如何获取当前循环的索引？因为我不想让 var i = 0;循
快速了解MySQL 索引
MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL
18、SQLite 索引
SQLite 索引（Index）索引（Index）是一种特殊的查找表，数据库搜索引擎用来加快数据检索。简单地说，索引是一个指向表中数据的指针。一个数据库中的索引与一本书后边的索引是非常相似的。
RavenDB MultiMap 索引
我是 RavenDB 的新手。我正在尝试使用多 map 索引功能，但我不确定这是否是解决我的问题的最佳方法。所以我有三个文件:Unit、Car、People。汽车文件看起来像这样: { Id: "
基于标准的 Excel 索引
我有以下数据，我想根据范围在另一个表中建立索引我想要实现的是，例如，如果三星的销售额为 2500，则折扣为 2%，低于 3000 且高于 1000 我知道它可以通过索引来完成，与多个数组匹配，然后指
SQL 索引 - 这是重叠的吗？
我正在检查并删除 SQL 数据库中的重复和冗余索引。所以如果我有两个相同的索引，我会删除。例如，如果我删除了重叠的索引... 索引1:品牌、型号指标二:品牌、型号、价格我删除索引 1。相同顺

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

postgresql - PostgreSQL:一个索引快而另一个索引慢