算数为何需要这么长时间？ [英] why does count take so long?

问题描述

在7.3.4数据库上：


解释分析来自elog的select count（*）;

Aggregate（cost = 223764.05..223764.05 rows = 1宽度= 0）（实际

时间= 81372.11..81372.11行= 1循环= 1）

- > seq扫描elog（成本= 0.00..203012.24行= 8300724宽度= 0）

（实际时间= 3.91..71542.53行= 8313762循环= 1）

总计运行时间：81378.42毫秒

（3行）

看起来聚合本身需要10秒。什么'

这么久？

-------------------------- - （广播结束）---------------------------

提示7：别忘了增加你的可用空间地图设置

On a 7.3.4 database:

explain analyse select count(*) from elog;
Aggregate (cost=223764.05..223764.05 rows=1 width=0) (actual
time=81372.11..81372.11 rows=1 loops=1)
-> Seq Scan on elog (cost=0.00..203012.24 rows=8300724 width=0)
(actual time=3.91..71542.53 rows=8313762 loops=1)
Total runtime: 81378.42 msec
(3 rows)
It looks like the aggregate took 10 secs all by itself. What''s taking
so long?
---------------------------(end of broadcast)---------------------------
TIP 7: don''t forget to increase your free space map settings

推荐答案

在Sun，Sep 07,2003 21:06:26 -0400，
Joseph Shraibman< jk *@selectacast.net>写道：

On Sun, Sep 07, 2003 at 21:06:26 -0400,
Joseph Shraibman <jk*@selectacast.net> wrote:

在7.3.4数据库上：

解析分析来自elog的select count（*）;

Aggregate（cost = 223764.05 .. 223764.05行= 1宽度= 0）（实际
时间= 81372.11..81372.11行= 1循环= 1）
- > seq扫描elog（成本= 0.00..203012.24行= 8300724宽度= 0）
（实际时间= 3.91..71542.53行= 8313762循环= 1）
总运行时间：81378.42毫秒
（3排）

看起来聚合本身就需要10秒。什么花了这么长时间？

On a 7.3.4 database:

explain analyse select count(*) from elog;
Aggregate (cost=223764.05..223764.05 rows=1 width=0) (actual
time=81372.11..81372.11 rows=1 loops=1)
-> Seq Scan on elog (cost=0.00..203012.24 rows=8300724 width=0)
(actual time=3.91..71542.53 rows=8313762 loops=1)
Total runtime: 81378.42 msec
(3 rows)
It looks like the aggregate took 10 secs all by itself. What''s taking
so long?

看起来需要计算800万条日志记录。

过去几天有一些关于技巧的讨论如果你需要在大桌子上使用数量，那么
可以获得更好的性能。


---------------------------（广播结束）----------------- ----------

提示4：不要杀死-9''邮政局长

It looks like there are 8 million log records that need to be counted.

There have been some discussions over the last few days about tricks to
get better performance if you need to use count on large tables.

---------------------------(end of broadcast)---------------------------
TIP 4: Don''t ''kill -9'' the postmaster

Bruno Wolff III< br *** @ wolff.to>写道：

Bruno Wolff III <br***@wolff.to> writes:

Joseph Shraibman< jk *@selectacast.net>写道：

Joseph Shraibman <jk*@selectacast.net> wrote:

看起来聚合本身需要10秒。什么花了这么长时间？

It looks like the aggregate took 10 secs all by itself. What''s taking
so long?

看起来需要计算800万条日志记录。

It looks like there are 8 million log records that need to be counted.

是的，但我认为他正在抱怨在71秒的时间内读取800万行的

汇总的10秒增量。那个

对我来说似乎也很高。在一个10密​​耳排的测试台上，我得到了


回归=＃解析分析从foo中选择计数（*）;

QUERY PLAN
--------------------------------------------- -------------------------------------------------- -------------------

总计（成本= 22.50..22.50行= 1宽度= 0）（实际时间= 189865.81..189865.81 rows = 1 loops = 1）

- > Seq扫描foo（成本= 0.00..20.00行= 1000宽度= 0）（实际时间= 18.88..163833.61行= 10240000循环= 1）

总运行时间：189865.91毫秒

（3行）


换言之26秒在163sec之上进行汇总以读取

行。


除非Joseph的机器具有比我的小型b $ b开发机器更好的IO-to-CPU比率，否则他的数字会有些奇怪。


问候，汤姆小巷


------------------------- - （广播结束）---------------------------

提示4：不要' 'kill -9''邮政局长

Yeah, but I think he''s complaining about the 10sec delta for the
aggregate on top of the 71sec to read the 8 million rows. That
seems high to me too. On a 10-mil-row test table, I get

regression=# explain analyze select count(*) from foo;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Aggregate (cost=22.50..22.50 rows=1 width=0) (actual time=189865.81..189865.81 rows=1 loops=1)
-> Seq Scan on foo (cost=0.00..20.00 rows=1000 width=0) (actual time=18.88..163833.61 rows=10240000 loops=1)
Total runtime: 189865.91 msec
(3 rows)

in other words 26sec to do the aggregate on top of 163sec to read the
rows.

Unless Joseph''s machine has a way better IO-to-CPU ratio than my little
development machine, there''s something odd about his numbers.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don''t ''kill -9'' the postmaster

Tom Lane< tg *@sss.pgh.pa.us>写道：

Tom Lane <tg*@sss.pgh.pa.us> writes:

是的，但我认为他正在抱怨在71秒之上的
聚合的10秒增量读取800万行。那对我来说似乎也很高兴。在一个10密​​耳的测试台上，我得到
....换句话说26秒在163sec的顶部做汇总来读取
行。

除非约瑟夫的机器具有比我的小型开发机器更好的IO-CPU比率，他的数字有些奇怪。


为什么8M记录的10s（14％delta）可疑但10M的26s（16％delta）
不可疑？这些结果实际上看起来相当一致。


我认为最初的问题是这个10代表什么工作。

我很好奇太。它真的只需要1000万次cpu周期吗

来调用count（）聚合状态函数吗？


PS：

regression =＃解析分析从foo中选择计数（*）;
QUERY PLAN
----------------------- -------------------------------------------------- -----------------------------------------
汇总（成本= 22.50 ..22.50行= 1宽度= 0）（实际时间= 189865.81..189865.81行= 1个循环= 1）
- > Seq扫描foo（成本= 0.00..20.00行= 1000宽度= 0）（实际时间= 18.88..163833.61行= 10240000循环= 1）
总运行时间：189865.91毫秒
（3行）

Yeah, but I think he''s complaining about the 10sec delta for the
aggregate on top of the 71sec to read the 8 million rows. That
seems high to me too. On a 10-mil-row test table, I get .... in other words 26sec to do the aggregate on top of 163sec to read the
rows.

Unless Joseph''s machine has a way better IO-to-CPU ratio than my little
development machine, there''s something odd about his numbers.
Why is 10s (a 14% delta) for 8M records suspicious but 26s (16% delta) for 10M
not suspicious? These results seem fairly consistent actually.

I think what the original question was is "what work does this 10s represent".
I''m curious too. Is it really just 10 million times the cpu cycles necessary
to dispatch a call to the count() aggregate state function?

PS:
regression=# explain analyze select count(*) from foo;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Aggregate (cost=22.50..22.50 rows=1 width=0) (actual time=189865.81..189865.81 rows=1 loops=1)
-> Seq Scan on foo (cost=0.00..20.00 rows=1000 width=0) (actual time=18.88..163833.61 rows=10240000 loops=1)
Total runtime: 189865.91 msec
(3 rows)

嘿，你还没有分析你的桌子！ :)


-

greg

----------------- ----------（广播结束）---------------------------

提示7：别忘了增加免费空间地图设置

Hey, you haven''t analyzed your table! :)

--
greg
---------------------------(end of broadcast)---------------------------
TIP 7: don''t forget to increase your free space map settings

这篇关于算数为何需要这么长时间？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！