PostgreSQL计数+排序性能

本文介绍了PostgreSQL计数+排序性能的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我已经使用PostgreSQL和mental copg2构建了一个小型库存系统。一切都运行得很好，除了当我想要创建内容的聚合摘要/报告时，由于count()和排序，我得到的性能非常差。

数据库架构如下：

CREATE TABLE hosts
(
        id SERIAL PRIMARY KEY,
        name VARCHAR(255)
);
CREATE TABLE items
(
        id SERIAL PRIMARY KEY,
        description TEXT
);
CREATE TABLE host_item
(
        id SERIAL PRIMARY KEY,
        host INTEGER REFERENCES hosts(id) ON DELETE CASCADE ON UPDATE CASCADE,
        item INTEGER REFERENCES items(id) ON DELETE CASCADE ON UPDATE CASCADE
);

还有一些其他字段，但这些字段不相关。

我要提取两个不同的报告：-所有主机的列表，每个主机的项目数从最高排序至最低计数-具有每个主机数量的所有项目列表，从最高计数到最低计数排序

我为此使用了2个查询：

包含主机计数的项目：

SELECT i.id, i.description, COUNT(hi.id) AS count
FROM items AS i
LEFT JOIN host_item AS hi
ON (i.id=hi.item)
GROUP BY i.id
ORDER BY count DESC
LIMIT 10;

具有项目计数的主机：

SELECT h.id, h.name, COUNT(hi.id) AS count
FROM hosts AS h
LEFT JOIN host_item AS hi
ON (h.id=hi.host)
GROUP BY h.id
ORDER BY count DESC
LIMIT 10;

问题是：查询运行5-6秒后才返回任何数据。由于这是基于Web的应用程序，6秒是不可接受的。数据库中有大约50k个主机、1000个项目和400000个主机/项目关系，当(或如果)使用应用程序时，这些关系可能会显著增加。

在尝试之后，我发现通过删除"order by count DESC"部分，两个查询都可以立即执行，没有任何延迟(完成查询的时间不到20ms)。

有没有什么方法可以优化这些查询，以便在不延迟的情况下对结果进行排序？我尝试了不同的索引，但是由于计数是计算出来的，所以可以使用索引来实现这一点。我读到PostgreSQL中的count()‘运算速度很慢，但正是排序给我带来了问题.

我当前的解决办法是以每小时作业的形式运行上面的查询，将结果放入一个新的表中，该表在COUNT列上有索引，以便快速查找。

我使用的是PostgreSQL 9.2。

更新：按订单查询计划：)

EXPLAIN ANALYZE
SELECT h.id, h.name, COUNT(hi.id) AS count
FROM hosts AS h
LEFT JOIN host_item AS hi
ON (h.id=hi.host)
GROUP BY h.id
ORDER BY count DESC
LIMIT 10;


 Limit  (cost=699028.97..699028.99 rows=10 width=21) (actual time=5427.422..5427.424 rows=10 loops=1)
   ->  Sort  (cost=699028.97..699166.44 rows=54990 width=21) (actual time=5427.415..5427.416 rows=10 loops=1)
         Sort Key: (count(hi.id))
         Sort Method: top-N heapsort  Memory: 25kB
         ->  GroupAggregate  (cost=613177.95..697840.66 rows=54990 width=21) (actual time=3317.320..5416.440 rows=54990 loops=1)
               ->  Merge Left Join  (cost=613177.95..679024.94 rows=3653163 width=21) (actual time=3317.267..5025.999 rows=3653163 loops=1)
                     Merge Cond: (h.id = hi.host)
                     ->  Index Scan using hosts_pkey on hosts h  (cost=0.00..1779.16 rows=54990 width=17) (actual time=0.012..15.693 rows=54990 loops=1)
                     ->  Materialize  (cost=613177.95..631443.77 rows=3653163 width=8) (actual time=3317.245..4370.865 rows=3653163 loops=1)
                           ->  Sort  (cost=613177.95..622310.86 rows=3653163 width=8) (actual time=3317.199..3975.417 rows=3653163 loops=1)
                                 Sort Key: hi.host
                                 Sort Method: external merge  Disk: 64288kB
                                 ->  Seq Scan on host_item hi  (cost=0.00..65124.63 rows=3653163 width=8) (actual time=0.006..643.257 rows=3653163 loops=1)
 Total runtime: 5438.248 ms





EXPLAIN ANALYZE
SELECT h.id, h.name, COUNT(hi.id) AS count
FROM hosts AS h
LEFT JOIN host_item AS hi
ON (h.id=hi.host)
GROUP BY h.id
LIMIT 10;


 Limit  (cost=0.00..417.03 rows=10 width=21) (actual time=0.136..0.849 rows=10 loops=1)
   ->  GroupAggregate  (cost=0.00..2293261.13 rows=54990 width=21) (actual time=0.134..0.845 rows=10 loops=1)
         ->  Merge Left Join  (cost=0.00..2274445.41 rows=3653163 width=21) (actual time=0.040..0.704 rows=581 loops=1)
               Merge Cond: (h.id = hi.host)
               ->  Index Scan using hosts_pkey on hosts h  (cost=0.00..1779.16 rows=54990 width=17) (actual time=0.015..0.021 rows=11 loops=1)
               ->  Index Scan Backward using idx_host_item_host on host_item hi  (cost=0.00..2226864.24 rows=3653163 width=8) (actual time=0.005..0.438 rows=581 loops=1)
 Total runtime: 1.143 ms

更新：这个问题的所有答案都有助于学习和理解Postgres的工作原理。这个问题似乎没有任何确定的解决方案，但我非常感谢您提供的所有优秀答案，我将在未来的PostgreSQL工作中使用这些答案。非常感谢各位！

推荐答案

@Gordon和@Willglynn为您的查询速度慢的原因提供了许多有用的背景信息。

解决方法是将计数器添加到表items和hosts以及使它们保持最新的触发器中，从而降低写入操作的成本。
或者像您一样使用实例化视图。我可能会选择那个。

为此，您仍然需要定期执行这些查询，它们可以改进。将第一个重写为：

SELECT id, i.description, hi.ct
FROM   items i
JOIN  (
    SELECT item AS id, count(*) AS ct
    FROM   host_item
    GROUP  BY item
    ORDER  BY ct DESC
    LIMIT  10
    ) hi USING (id);

如果表items中有一行，表host_item中的大多数行都是表host_item，则先聚合再聚合JOIN速度更快。与@will glynn推测的相反，这在Postgres 9.1中没有自动优化。
count(*)比主体上的count(col)快-并且等效，而col不能为空。(aLEFT JOIN可能引入空值。)
简化为LEFT JOIN为JOIN。应该可以安全地假设始终至少有十个不同的主机。对您的原始查询无关紧要，但这是此查询的要求。
表host_item索引无济于事，items主键覆盖睡觉

对于您的情况可能还不够好，但是在我使用Postgres 9.1进行的测试中，这个表单的速度是的两倍多。应转换为9.2，但必须使用EXPLAIN ANALYZE进行测试。

这篇关于PostgreSQL计数+排序性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！