代码之家 › 专栏 › 技术社区 › asmaier

PostgreSQL:如何优化我的数据库来存储和查询一个巨大的图表

graph postgresql optimization

asmaier · 技术社区 · 15 年前

我在1.83 GHz Intel Core Duo Mac Mini上运行PostgreSQL 8.3,内存为1GB,Mac OS X为10.5.8。我在PostgreSQL数据库中存储了一个巨大的图形。它由160万个节点和3000万个边缘组成。我的数据库架构如下:

CREATE TABLE nodes (id INTEGER PRIMARY KEY,title VARCHAR(256));
CREATE TABLE edges (id INTEGER,link INTEGER,PRIMARY KEY (id,link));
CREATE INDEX id_idx ON edges (id);
CREATE INDEX link_idx ON edges (link);

表边缘的数据看起来像

因此,它为每个ID为x的节点存储到ID为y的传出链接。

搜索所有传出链接的时间正常:

=# explain analyze select link from edges where id=4620;
                           QUERY PLAN                                                        
    ---------------------------------------------------------------------------------
     Index Scan using id_idx on edges  (cost=0.00..101.61 rows=3067 width=4) (actual time=135.507..157.982 rows=1052 loops=1)
       Index Cond: (id = 4620)
     Total runtime: 158.348 ms
    (3 rows)

但是,如果我搜索到一个节点的传入链接,数据库的速度会慢100倍以上(尽管生成的传入链接数仅比传出链接数高5-10倍):

=# explain analyze select id from edges where link=4620;
                         QUERY PLAN                                                           
----------------------------------------------------------------------------------
     Bitmap Heap Scan on edges  (cost=846.31..100697.48 rows=51016 width=4) (actual time=322.584..48983.478 rows=26887 loops=1)
       Recheck Cond: (link = 4620)
       ->  Bitmap Index Scan on link_idx  (cost=0.00..833.56 rows=51016 width=0) (actual time=298.132..298.132 rows=26887 loops=1)
             Index Cond: (link = 4620)
     Total runtime: 49001.936 ms
    (5 rows)

我试图强迫Postgres不要使用位图扫描

=# set enable_bitmapscan = false;

但是对传入链接的查询速度没有提高:

=# explain analyze select id from edges where link=1588;
                      QUERY PLAN                                                           
-------------------------------------------------------------------------------------------
 Index Scan using link_idx on edges  (cost=0.00..4467.63 rows=1143 width=4) (actual time=110.302..51275.822 rows=43629 loops=1)
   Index Cond: (link = 1588)
 Total runtime: 51300.041 ms
(3 rows)

我还将共享缓冲区从24MB增加到了512MB,但没有用。所以我想知道为什么我对传出和传入链接的查询显示出这样的不对称行为?我的索引选择有问题吗?还是应该创建第三个表来保存ID为x的节点的所有传入链接?但那将是相当浪费的磁盘空间。但既然我是SQL数据库的新手,也许我这里缺少一些基本的东西?

5 回复 | 直到 15 年前

Community CDub 8 年前

我想 habe 是正确的。

您可以使用 cluster link_idx on edges; analyze edges 填好桌子后。现在,第二个查询应该很快,而第一个查询应该很慢。

要使两个查询都快速进行,您必须使用第二个表来取消规范化,正如您所建议的那样。只需记住在加载数据后集群和分析第二个表,这样所有链接到节点的EGDE都将物理分组。

如果您不想一直查询这个表,并且不想存储和备份第二个表,那么您可以在查询之前临时创建它:

create temporary table egdes_backwards
  as select link, id from edges order by link, id;
create index edges_backwards_link_idx on edges_backwards(link);

您不必集群这个临时表,因为它将在创建时按物理顺序排列。它对一个查询没有意义,但对一行中的多个查询有帮助。

habe 15 年前

我想这是因为磁盘上相同的键记录有一个__密度__。我认为具有相同ID的记录存储在密集的(即块的数量很少)中,而具有相同链接的记录存储在稀疏的(即分布到大量块中)。如果已按ID的顺序插入记录,则可能会发生这种情况。

假设: 1。有10000条记录, 2。它们按顺序存储,例如(id,link)=(1,1),(1,2),…,(1,100),(2,1)…,和三。50条记录可以存储在一个块中。

在上述假设中,块1 3分别由记录(1,1)~(1,50)、(1,51)~(1,100)和(2,1)~(2,50)组成。

当你 SELECT * FROM edges WHERE id=1 只有2个块(1,2)需要加载和扫描。另一方面, SELECT * FROM edges WHERE link=1 需要50个块(1、3、5,…),即使行数相同。

Ants Aasma 15 年前

如果您需要良好的性能,并且可以在不受外键约束的情况下进行处理(或使用触发器手动实现它们),请尝试 intarray 和 intagg 扩展模块。而不是边表有一个 outedges integer[] 节点表上的列。这将向表中添加大约140MB,因此整个内容仍可能适合内存。对于反向查找,要么在outedges列上创建一个gin索引(对于额外的280MB),要么只添加一个inedges列。

PostgreSQL有很高的行开销,所以幼稚的边缘表只为表产生1g的空间,而索引则增加了1.5。考虑到数据集的大小,如果使用整数数组存储关系,则很有可能将大部分数据集保存在缓存中。这将使任何查找都非常迅速。我看到了大约0.08ms的查找时间来获取给定节点任意方向的边。即使您不把它全部放在内存中,您仍然可以在内存中拥有更大的比例和更好的缓存位置。

tommym 15 年前

您的问题似乎与磁盘IO相关。Postgres必须读取索引匹配的元组,以查看行是否可见(这不能从索引中完成,因为它不包含必要的信息)。

如果您有大量删除的行和/或更新的行,真空分析(或简单分析)将有助于。先运行它,看看是否有任何改进。

集群也可能有帮助。基于您的示例,我想说使用link_idx作为集群密钥。”使用链接“idx”群集边缘。但是,它可能会降低ID查询的性能(您的ID查询可能很快,因为它们已经在磁盘上排序)。记住在集群之后运行分析。

接下来的步骤包括微调内存参数、添加更多内存或添加更快的磁盘子系统。

-1

Peter Neubauer 15 年前

你在www.neo4j.org上试过这样做吗?在一个图形数据库中,这几乎是微不足道的,应该可以在MS范围内为您的用例提供性能。