时间戳

首页 > 解决方案 > PostgreSQL 图邻居查询慢

postgresql - PostgreSQL 图邻居查询慢

问题描述

编辑JOIN在我最初的问题中，我注意到使用 a和使用子句搜索邻居之间的区别WHERE .. IN，@LukaszSzozda 正确地指出这是一个半连接。事实证明我的节点列表有重复，这就解释了为什么JOIN运行时间更长。谢谢，@LukaszSzozda。不过，我的问题中更重要的方面仍然存在，这就是下面的内容。更新我在底部添加了相关的配置选项，并使用ANALYZE（感谢@joop）更新了统计信息。另外，我用三个不同的索引（B-Tree、hash、BRIN）进行了测试。最后，我注意到使用不同的查询返回的行数不同tmp_nodes，可能是因为排序不同，所以我将其固定为一组相当随机的 8,000 个节点。

在 PostgreSQL 中，我在 ~200*10 6个节点（在 ~1.3*10 9 个边内）搜索 8,000 个节点的邻居的查询很慢（使用哈希索引约 30 秒；请参阅下面的索引基准测试）。

鉴于我在下面描述的设置，我的服务器软件、数据库、表或查询是否有进一步的优化，以使邻居搜索更快？考虑到 PostgreSQL 在 ArangoDB NoSQL 基准测试中的表现，我对这个速度感到特别惊讶。

进一步来说：

  1. 我知道AgnesGraph，但不希望转向图形数据库解决方案，特别是因为我无法从 AgnesGraph 告诉它与 PostgreSQL 保持同步的情况。有人可以解释查询在 AgnesGraph 与 PostgreSQL 中实际发生的方式的性能优势，以便我可以决定是否迁移？
  2. 是否有任何配置调整（无论是在服务器中还是在操作系统中）会根据计划影响我的查询，从而导致它运行的时间超过所需时间？

设置

我在 PostgreSQL（PostgreSQL 10.1，我必须从 PPA 中提取）中有一个大型图形数据库（~10 9 个边，~200*10 6zesty个节点），存储在云上（DigitalOcean，6 核，16GB RAM 机器，Ubuntu 17.10，Intel(R) Xeon(R) CPU E5-2650 v4 @ 2.20GHz），并使用PGTune建议的参数进行设置（见底部）。我在服务器上查询。

我已经创建了前向和后向边缘表（请参阅此问题）

CREATE TABLE edges_fwd (src BIGINT, dest BIGINT, PRIMARY KEY (src, dest));
CREATE TABLE edges_back (src BIGINT, dest BIGINT, PRIMARY KEY (dest, src));

并由各自的键聚类（以防万一）：

CLUSTER edges_fwd USING edges_fwd_pkey;
CLUSTER edges_back USING edges_back_pkey;

为了测试我的查询，我关闭enabled_seqscan了（见下面的附注）。

我想加载 8,000 个节点的所有出边（其中 8,000 个节点可以根据用户查询进行更改），其标识符是表中的列表tmp_nodes（带有单列，nid）。我最初在查询中编写了这个版本（在已经遵循PGCon11 的图表谈话的行之后拍拍自己）：

SELECT e.*  
  FROM tmp_nodes
  JOIN edges AS e
    ON e.src = tmp_nodes.nid;

我也试过：

SELECT * FROM edges_fwd AS e
 WHERE e.src IN (SELECT nid FROM tmp_nodes);

它们都很慢，最多需要大约 30 秒才能运行（使用哈希索引）。EXPLAIN ANALYZE输出被带到下面。

我预计事情通常会运行得更快。为了在聚集表中查找 8,000 个键（是的，我知道它不是真正的聚集索引），因为服务器知道行是有序的，我应该期望页面读取少于返回的总行数。因此，虽然获取了 243,708 行，这不是一点点，但它们与 8,000 个不同的键相关联，并且读取的数量不应该比这大很多：每个键平均 30 行，每个键大约 1,400 字节读取（表大小为 56GB 并且有 1.3B 行，因此每行大约 46 个字节；顺便说一下，对于 16 个字节的数据来说这是相当膨胀的）。这远低于系统的页面大小 (4K)。我不认为阅读 8,000 页，即使是随机访问，也应该花这么长时间。

这让我回到了我的问题（上图）。

强制使用索引

我从另一个问题的答案中听取了建议，至少是为了测试（不过，由于我的数据库是只读的，我可能很想在生产中使用它），设置enable_seqscan为off，以强制使用索引。我每次跑了 5 次——时间在这里和那里相差几秒钟。

EXPLAIN ANALYZE输出

注意刷新缓存的操作系统磁盘并重新启动服务器以反映正确的随机搜索时间，我EXPLAIN ANALYZE在两个查询中都使用了。我使用了两种类型的索引——B-Tree 和 hash。我还尝试了 BRIN 选项的不同值pages_per_range（2、8、32 和 128），但它们都比上面提到的慢（数量级或数量级）。我在下面给出结果以供参考。

B-Tree索引，JOIN查询：

Nested Loop  (cost=10000000000.58..10025160709.50 rows=15783833 width=16) (actual time=4.546..39152.408 rows=243708 loops=1)
  ->  Seq Scan on tmp_nodes  (cost=10000000000.00..10000000116.00 rows=8000 width=8) (actual time=0.712..15.721 rows=8000 loops=1)
  ->  Index Only Scan using edges_fwd_pkey on edges_fwd e  (cost=0.58..3125.34 rows=1973 width=16) (actual time=4.565..4.879 rows=30 loops=8000)
        Index Cond: (src = tmp_nodes.nid)
        Heap Fetches: 243708
Planning time: 20.962 ms
Execution time: 39175.454 ms

B-Tree 索引、WHERE .. IN查询（半连接）：

Nested Loop  (cost=10000000136.58..10025160809.50 rows=15783833 width=16) (actual time=9.578..42605.783 rows=243708 loops=1)
  ->  HashAggregate  (cost=10000000136.00..10000000216.00 rows=8000 width=8) (actual time=5.903..35.750 rows=8000 loops=1)
        Group Key: tmp_nodes.nid
        ->  Seq Scan on tmp_nodes  (cost=10000000000.00..10000000116.00 rows=8000 width=8) (actual time=0.722..2.695 rows=8000 loops=1
)
  ->  Index Only Scan using edges_fwd_pkey on edged_fwd e  (cost=0.58..3125.34 rows=1973 width=16) (actual time=4.924..5.309 rows=30 loops=8000)
        Index Cond: (src = tmp_nodes.nid)
        Heap Fetches: 243708
Planning time: 19.126 ms
Execution time: 42629.084 ms

哈希索引，JOIN查询：

Nested Loop  (cost=10000000051.08..10056052287.01 rows=15783833 width=16) (actual time=3.710..34131.371 rows=243708 loops=1)
  ->  Seq Scan on tmp_nodes  (cost=10000000000.00..10000000116.00 rows=8000 width=8) (actual time=0.960..13.338 rows=8000 loops=1)
  ->  Bitmap Heap Scan on edges_fwd e  (cost=51.08..6986.79 rows=1973 width=16) (actual time=4.086..4.250 rows=30 loops=8000)
        Heap Blocks: exact=8094
        ->  Bitmap Index Scan on ix_edges_fwd_src_hash  (cost=0.00..50.58 rows=1973 width=0) (actual time=2.563..2.563 rows=31

loops=8000) 执行时间：34155.511 ms

哈希索引、WHERE .. IN查询（半连接）：

Nested Loop  (cost=10000000187.08..10056052387.01 rows=15783833 width=16) (actual time=12.766..31834.767 rows=243708 loops=1)
  ->  HashAggregate  (cost=10000000136.00..10000000216.00 rows=8000 width=8) (actual time=6.297..30.760 rows=8000 loops=1)
        ->  Seq Scan on tmp_nodes  (cost=10000000000.00..10000000116.00 rows=8000 width=8) (actual time=0.883..3.108 rows=8000 loops=$

) -> 在 edges_fwd e (cost=51.08..6986.79 rows=1973 width=16) (实际时间=3.768..3.958 rows=30 loops=8000) Heap Blocks: 精确=8094 -> Bitmap Index Scan on ix_edges_fwd_src_hash (cost=0.00..50.58 rows=1973 width=0) (实际时间=2.340..2.340 rows=31 loops=8000) 执行时间：31857.692 ms

postgresql.conf设置

我按照PGTune的建议设置了以下配置选项：

max_connections = 10
shared_buffers = 4GB
effective_cache_size = 12GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 500
random_page_cost = 4
effective_io_concurrency = 2
work_mem = 69905kB
min_wal_size = 4GB
max_wal_size = 8GB
max_worker_processes = 6
max_parallel_workers_per_gather = 3
max_parallel_workers = 6

标签： postgresqlperformancequery-optimizationgraph-databases

解决方案

似乎这种设置的随机访问就是这么慢。运行一个脚本来检查一个大文件中 8,000 个不同的随机 4K 块的随机访问需要将近 30 秒。使用 Linuxtime和链接脚本，我平均得到大约 24 秒：

File size: 8586524825 Read size: 4096
32768000 bytes read

real    0m24.076s

因此，似乎随机访问应该更快的假设是错误的。加上读取实际索引所花费的时间，这意味着性能处于峰值，而无需更改硬件。为了提高性能，我可能需要使用 RAID 设置或集群。如果 RAID 设置将以接近线性的方式提高性能，我会接受我自己的答案。

推荐阅读