Postgresql计数+排序性能

我使用 postgresql 和 psycopg2 构建了一个小型库存系统。一切都很好，除了当我想创建内容的聚合摘要/报告时，由于 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 个不同的报告： - 所有主机的列表以及每个主机的项目数，从最高的顺序排列 到最低计数 - 所有项目的列表，其中包含每个主机的数量，从最高到最低计数排序

我为此目的使用了 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 个项目和 400 000 个主机/项目关系，并且当（或者如果）使用应用程序时，数据库可能会显着增加。

经过尝试，我发现通过删除“ORDER BY count DESC”部分，两个查询都会立即执行，没有任何延迟（完成查询的时间不到 20 毫秒）。

有什么方法可以优化这些查询，以便我可以毫不延迟地对结果进行排序？我尝试了不同的索引，但由于计算了计数，因此可以使用索引。我读过 postgresql 中的 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 and hosts以及使它们保持最新状态的触发器 - 写入操作的成本不菲。
或者像您一样使用物化视图。我可能会选择那样。

为此，您仍然需要定期执行这些查询，并且它们can得到改善。将您的第一个重写为：

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，先聚合再聚合速度更快JOIN。与 @willglynn 推测的相反，Postgres 9.1 中不会自动优化这一点。

• count(*)比count(col)原则上 - 以及等效的同时col不能为 NULL。 （ALEFT JOIN可能会引入 NULL 值。）

• 简化版LEFT JOIN to JOIN。可以安全地假设总是至少有十个不同的主机。对于您的原始查询来说并不重要，但这是此查询的一项要求。

• 表上的索引host_item没有帮助，而且PKitems覆盖其余部分。

对于您的情况可能仍然不够好，但在我对 Postgres 9.1 的测试中，这种形式是快两倍以上。应该转换为 9.2，但测试时使用EXPLAIN ANALYZE为了确定。