对数百万行的PostgreSQL查询在UUID上花费很长时间

Question

我有一个UUID的参考表，它大约有200M行。我有5000个UUID，我想在参考表中查找。参考表如下所示：

CREATE TABLE object_store AS (
    project_id UUID,
    object_id UUID,
    object_name VARCHAR(20),
    description VARCHAR(80)
);

CREATE INDEX object_store_project_idx ON object_store(project_id);
CREATE INDEX object_store_id_idx ON object_store(object_id);

*编辑#2 *

请求temp_objects表定义。

CREATE TEMPORARY TABLE temp_objects AS (
    object_id UUID
)
ON COMMIT DELETE ROWS;

独立索引的原因是因为object_id并不是唯一的，可以属于许多不同的项目。参考表只是UUID (temp_objects)的临时表，我想检查它(5000 object_ids)。

如果我用1 object_id文本值查询上面的引用表，它几乎是瞬时的(2ms)。如果临时表只有1行，则同样是瞬时的(2ms)。但是有5000行，甚至需要25分钟才能返回。当然，它可以收回超过3M行的火柴。

*编辑*

--用于1行比较(4.198 ms):

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)SELECT O.project_id
FROM temp_objects T JOIN object_store O ON T.object_id = O.object_id;
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.57..475780.22 rows=494005 width=65) (actual time=0.038..2.631 rows=1194 loops=1)
   Buffers: shared hit=1202, local hit=1
   ->  Seq Scan on temp_objects t  (cost=0.00..13.60 rows=360 width=16) (actual time=0.007..0.009 rows=1 loops=1)
         Buffers: local hit=1
   ->  Index Scan using object_store_id_idx on object_store l  (cost=0.57..1307.85 rows=1372 width=81) (actual time=0.027..1.707 rows=1194 loops=1)
         Index Cond: (object_id = t.object_id)
         Buffers: shared hit=1202
 Planning time: 0.173 ms
 Execution time: 3.096 ms
(9 rows)

Time: 4.198 ms

用于4911行比较(1579082.974 ms (26:19.083)):

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)SELECT O.project_id
FROM temp_objects T JOIN object_store O ON T.object_id = O.object_id;
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.57..3217316.86 rows=3507438 width=65) (actual time=0.041..1576913.100 rows=8043500 loops=1)
   Buffers: shared hit=5185078 read=2887548, local hit=71
   ->  Seq Scan on temp_objects d  (cost=0.00..96.56 rows=2556 width=16) (actual time=0.009..3.945 rows=4911 loops=1)
         Buffers: local hit=71
   ->  Index Scan using object_store_id_idx on object_store l  (cost=0.57..1244.97 rows=1372 width=81) (actual time=1.492..320.081 rows=1638 loops=4911)
         Index Cond: (object_id = t.object_id)
         Buffers: shared hit=5185078 read=2887548
 Planning time: 0.169 ms
 Execution time: 1579078.811 ms
(9 rows)

Time: 1579082.974 ms (26:19.083)

最后，我希望使用标准分组对匹配的object_ids按project_id进行分组并进行计数。总费用在成本的上端(当然)。只需大约25分钟就能完成以下查询。然而，当我将temp表限制为1行时，它将在21 in内返回。有些东西没有加起来..。

EXPLAIN SELECT O.project_id, count(*)
FROM temp_objects T JOIN object_store O ON T.object_id = O.object_id GROUP BY O.project_id;
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=6189484.10..6189682.84 rows=19874 width=73)
   Group Key: o.project_id
   ->  Nested Loop  (cost=0.57..6155795.69 rows=6737683 width=65)
         ->  Seq Scan on temp_objects t  (cost=0.00..120.10 rows=4910 width=16)
         ->  Index Scan using object_store_id_idx on object_store o  (cost=0.57..1239.98 rows=1372 width=81)
               Index Cond: (object_id = t.object_id)
(6 rows)

我正在运行PostgreSQL 10.6，在SSD上运行两个CPU和8GB内存。我有ANALYZEd表，我将work_mem设置为50 be，shared_buffers设置为2GB，并将random_page_cost设置为1。所有这些都帮助查询在几分钟内返回，但仍然没有我认为的那么快。

如果CPU/RAM/并行化有很大的不同，我可以选择云计算。只想知道如何让这个简单的查询在<几秒内返回(如果可能的话)。

* UPDATE *

根据Jürgen的提示，我将两个object_id字段更改为bigint，只使用UUID的上半部分，并将数据化减少了一半。现在，在查询上面执行聚合查询的时间是16分钟。

接下来，考虑到jjane对set enable_nestloop to off的建议，我的聚合查询跳到了6分钟！不幸的是，所有其他的建议都没有加速超过6分钟，虽然有趣的是，将我的“临时”表改为永久的表，允许2名工人工作，但它并没有改变时间。我认为jjane说IO是这里的约束因素是准确的。以下是6分钟的最新解释计划(希望它更快，但更好！)：

explain (analyze, buffers, format text) select project_id, count(*) from object_store natural join temp_object group by project_id;
                                                                                         QUERY PLAN                                                                                         
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize GroupAggregate  (cost=3966899.86..3967396.69 rows=19873 width=73) (actual time=368124.126..368744.157 rows=153633 loops=1)
   Group Key: object_store.project_id
   Buffers: shared hit=243022 read=2423215, temp read=3674 written=3687
   I/O Timings: read=870720.440
   ->  Sort  (cost=3966899.86..3966999.23 rows=39746 width=73) (actual time=368124.116..368586.497 rows=333427 loops=1)
         Sort Key: object_store.project_id
         Sort Method: external merge  Disk: 29392kB
         Buffers: shared hit=243022 read=2423215, temp read=3674 written=3687
         I/O Timings: read=870720.440
         ->  Gather  (cost=3959690.23..3963863.56 rows=39746 width=73) (actual time=366476.369..366827.313 rows=333427 loops=1)
               Workers Planned: 2
               Workers Launched: 2
               Buffers: shared hit=243022 read=2423215
               I/O Timings: read=870720.440
               ->  Partial HashAggregate  (cost=3958690.23..3958888.96 rows=19873 width=73) (actual time=366472.712..366568.313 rows=111142 loops=3)
                     Group Key: object_store.project_id
                     Buffers: shared hit=243022 read=2423215
                     I/O Timings: read=870720.440
                     ->  Hash Join  (cost=132.50..3944473.09 rows=2843429 width=65) (actual time=7.880..363848.830 rows=2681167 loops=3)
                           Hash Cond: (object_store.object_id = temp_object.object_id)
                           Buffers: shared hit=243022 read=2423215
                           I/O Timings: read=870720.440
                           ->  Parallel Seq Scan on object_store  (cost=0.00..3499320.53 rows=83317153 width=73) (actual time=0.467..324932.880 rows=66653718 loops=3)
                                 Buffers: shared hit=242934 read=2423215
                                 I/O Timings: read=870720.440
                           ->  Hash  (cost=71.11..71.11 rows=4911 width=8) (actual time=7.349..7.349 rows=4911 loops=3)
                                 Buckets: 8192  Batches: 1  Memory Usage: 256kB
                                 Buffers: shared hit=66
                                 ->  Seq Scan on temp_object  (cost=0.00..71.11 rows=4911 width=8) (actual time=0.014..2.101 rows=4911 loops=3)
                                       Buffers: shared hit=66
 Planning time: 0.247 ms
 Execution time: 368779.757 ms
(32 rows)

Time: 368780.532 ms (06:08.781)

所以我现在每查询6分钟。我认为，由于I/O成本，如果可能的话，我可以尝试在这个表上安装一个内存存储，看看SSD是否使它更好。

Answer 1

UUID是针对自适应缓存管理工作的(编辑)，并且由于它们的随机性，由于索引空间大于内存，因此有效地降低了缓存命中率。Id覆盖的数值范围很广，分布均匀，因此实际上每个Id都在索引树上自己的叶子上着陆。当索引页决定将行保存在磁盘中的数据页时，几乎每行都会得到自己的页，从而导致大量极其昂贵的I/O操作来读取所有这些行。

这就是为什么通常不建议使用UUID的原因，如果您确实需要UUID，那么至少要生成数值相近的时间戳/mac前缀UUID(查看uuid_generate_v1() - https://www.postgresql.org/docs/9.4/uuid-ossp.html)，因此数据行聚集在较小的数据页上的可能性更高，从而减少I/O操作以获取更多的数据。

长篇短篇小说:大范围的随机性会扼杀索引(实际上不是索引，在读取时获取数据并在写入时维护索引会导致大量的I/O开销)，因此会将查询速度降低到与没有索引一样好的地步。

这里还有一个可供参考的文章

Answer 2

看起来，您问题的核心是为什么它不从一个输入行线性地扩展到5000输入行。但我觉得这是条红鲱鱼。你是怎么选择一排的？如果每次选择相同的一行，则数据将停留在缓存中，并且速度非常快。我打赌这就是你要做的。如果每次执行一行计划时都选择不同的随机一行，则可能会发现缩放更加线性。

你应该打开track_io_timing。我毫不怀疑IO实际上是瓶颈，但看到它的实际测量和报告总是很好的，我以前很惊讶。

临时表的使用将抑制并行查询。您可能需要使用永久表进行测试，看看是否使用了并行工作人员，如果是，这是否真的有帮助。如果执行此测试，则应使用查询的聚合版本。它们比非聚合查询更高效地并行化，如果这是您的最终目标，那么您应该首先使用它进行测试。

您可以尝试的另一件事是设置大量的effective_io_concurrency。但是，只有当您的计划开始使用位图扫描时才有帮助，而您显示的计划没有。将random_page_cost从1设置为稍高的值可能会鼓励它使用位图扫描。(effective_io_concurrency很奇怪，因为位图计划可以从更高的设置中获得很大的实际利益，但是规划师并没有给位图计划带来任何好处。因此，为了获得好处，你必须“不小心”地使用该计划)

在某种程度上(随着temp_objects中行数的增加)，对该表进行散列并将其哈希连接到object_store表的seq扫描会更快。5000美元是否已经超过了这个速度会更快的点？规划师显然不这么认为，但规划师从来没有完全正确地得到切分点，而且经常有相当多的偏差。如果在运行查询之前执行set enable_nestloop TO off;，会发生什么？

你是否对你的SSD (数据库之外)做过低水平的基准测试？假设您的大部分时间都花在IO读取上，而且几乎没有一个是由文件系统缓存实现的，那么每次读取的时间是1576913/2887548 = 0.55ms。看起来挺长的。这就是我在一台底层笔记本电脑上得到的东西，在那里，SSD是通过VM层公开的。我希望服务器级硬件能带来更好的效果。

Answer 3

确保您也有一个适当的temp_objects表索引

CREATE INDEX temp_object_id_idx ON temp_objects(object_id);

SELECT O.project_id
FROM temp_objects T 
JOIN object_store O ON T.object_id = O.object_id;