问题描述
嗨Posgres用户/开发人员，
任何人都可以解释为什么PosgreSQL（Linux上的7.4.5版本）执行完整的
表扫描来计算在真空分析后在基表上计数（*）
已经完成，没有任何可能已经过时的任何
统计数据的更新。正如人们所预料的那样，直接解释命令确实会立即从目录中提供正确数量的
元组。还是
优化器认为它需要全表扫描来计算。
见下面的例子：
------ 8< ------------------------------------------ -------------------------------------------------- -
TestDB =＃\d test_tbl;
表" public.test_tbl"
列|输入|修饰符
-------- + --------- + -----------
pre |整数|不是null
name |文字| not null
索引：
" test_tbl_pkey"主键，btree（pre）
" test_tbl_pre_index" unique，btree（pre）
" test_tbl_name_index" btree（名字）
TestDB =＃解释从test_tbl选择计数（*）;
查询计划
---- -------------------------------------------------- ----------------------
汇总（成本= 34293.60..34293.60行= 1宽度= 0）
- > seq扫描test_tbl（费用= 0.00..34293.60行= 166558宽度= 0）
（2行）
时间：25.188毫秒
TestDB =＃select count（*）from test_tbl;
count
--------
166558
（1行）
时间：1024.803 ms
TestDB =＃
------ 8< ------------------------------------------ -------------------------------------------------- -
这个看似奇怪的计数实现的结果是非常慢的数量。
问候，
Henk Ernst Blok
-
地址：DB集团，计算机科学，EEMCS部门，特温特大学， br />
PO Box 217,7500 AE，ENSCHEDE，THE NETHERLANDS
phone：++ 31（0）53 489 3754（如果没有回复：3690）
电子邮件：
WWW ：
---------------------------（结束广播）---------------------------
提示4：不要杀人-9 ''postmaster
Hi Posgres users/developers,
Can anyone explain why PosgreSQL (version 7.4.5 on Linux) does a full
table scan to compute a count(*) on a base table after a vacuum analyze
has been done with no following updates that might have outdated any
statistics. Strangly the explain command does give the correct number of
tuples instantaniously from the catalog, as one would expect. Still the
optimizer thinks it needs a full table scan to do count.
See example below:
------8<---------------------------------------------------------------------------------------------
TestDB=# \d test_tbl;
Table "public.test_tbl"
Column | Type | Modifiers
--------+---------+-----------
pre | integer | not null
name | text | not null
Indexes:
"test_tbl_pkey" primary key, btree (pre)
"test_tbl_pre_index" unique, btree (pre)
"test_tbl_name_index" btree (name)
TestDB=# explain select count(*) from test_tbl;
QUERY PLAN
----------------------------------------------------------------------------
Aggregate (cost=34293.60..34293.60 rows=1 width=0)
-> Seq Scan on test_tbl (cost=0.00..34293.60 rows=166558 width=0)
(2 rows)
Time: 25.188 ms
TestDB=# select count(*) from test_tbl;
count
--------
166558
(1 row)
Time: 1024.803 ms
TestDB=#
------8<---------------------------------------------------------------------------------------------
The consequence of this seemingly odd count implementation is a very
very slow count.
Regards,
Henk Ernst Blok
--
address: DB group, Computer Science, EEMCS Dept., University of Twente,
PO Box 217, 7500 AE, ENSCHEDE, THE NETHERLANDS
phone: ++31 (0)53 489 3754 (if no response: 3690)
email: h.******@utwente.nl
WWW: http://www.cs.utwente.nl/~blokh
---------------------------(end of broadcast)---------------------------
TIP 4: Don''t ''kill -9'' the postmaster
推荐答案
简单来说，count（）不会''看看统计数据，因为大部分时间都是过时的。在任何情况下，它们对于带有WHERE子句的任何
查询都没有用。
下一个最明显的选择是使用主键索引而不是扫描表格的
。但是，MVCC意味着我们可以拥有多个视图
表，其中一些后端看到的行数与
其他行不同。所以 - 我们需要将多个索引条目版本存储为
以及多行版本，或者您需要在这些情况下检查
中的实际行。 PostgreSQL做了第二次，这导致了你看到的完整扫描
。
有很多讨论（以及max（）/ min （）汇总
函数）在邮件列表档案中。
HTH
-
Richard Huxton
Archonet Ltd
-------------------------- - （广播结束）---------------------------
提示3：如果通过Usenet发布/阅读，请发送适当的
subscribe-nomail命令到以便您的
消息可以干净地通过邮件列表
To put it simply, count() doesn''t look at the statistics because most of
the time they are out of date. In any case, they aren''t useful for any
query with a WHERE clause.
The next most obvious choice is to use the primary-key index rather than
scanning the table. However, MVCC means that we can have multiple views
of the table, with some backends seeing a different number of rows than
others. So - either we need to store multiple index-entry versions as
well as multiple row versions or you need to check the actual row in
these cases. PostgreSQL does the second, which results in the full scan
which you see.
There is plenty of discussion of this (and also max()/min() aggregate
functions) in the mailing list archives.
HTH
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly
查询规划器应该如何知道真空度已经足够了，那么
是不是对表格进行了修改？
如果您对粗略数字感兴趣，可以阅读系统表
以获取最后的真空统计数据。如果你需要快速计算并且可以在插入上花费一些周期，只需在插入后制作一个带有计数结果的缓冲表
。
不合格的计数，例如没有WHERE子句不需要
可以使用很多。
问候
Tino
---------------------------（广播结束）----------------- ----------
提示9：如果你的
加入列的数据类型没有，那么规划人员会忽略你选择索引扫描的愿望匹配
How should the query planner know the vacuum was recent enough and there
were no modifications to the table since?
If you are interested in rough numbers you could read the system tables
for the last vacuum statistics. If you need fast count and can spend
some cycles on inserts, just make a buffer table with count results
after insert.
Unqualified count e.g. without a WHERE clause should not need to
be used a lot.
Regards
Tino
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column''s datatypes do not match
...
...
查询规划器应该如何知道真空是最近的，并且
从那以后没有对表格进行修改？
如果您对粗略数字感兴趣，可以阅读系统表格
以获取最后的真空统计数据。如果你需要快速计数并且可以在插入上花费一些周期，只需在插入后制作一个带有计数结果的缓冲表。
不合格的计数，例如没有WHERE子句不需要经常使用。
关于
Tino
How should the query planner know the vacuum was recent enough and there
were no modifications to the table since?
If you are interested in rough numbers you could read the system tables
for the last vacuum statistics. If you need fast count and can spend
some cycles on inserts, just make a buffer table with count results
after insert.
Unqualified count e.g. without a WHERE clause should not need to
be used a lot.
Regards
Tino
-
地址：数据库组，计算机科学，EEMCS部门，特温特大学，
PO Box 217,7500 AE，ENSCHEDE，THE NETHERLANDS
电话：++ 31（0）53 489 3754（如果没有回复：3690）
电子邮件：
WWW：
这篇关于奇怪的计数（*）实现？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！