问题描述
我已经使用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计数+排序性能的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
更多推荐
[db:关键词]
发布评论