Postgres中位图堆扫描非常缓慢

Question

我有一个简单的表格，其中包含流量测量数据：

CREATE TABLE "TrafficData"
(
  "RoadID" character varying NOT NULL,
  "DateID" numeric NOT NULL,
  "ExactDateTime" timestamp NOT NULL,
  "CarsSpeed" numeric NOT NULL,
  "CarsCount" numeric NOT NULL
)
CREATE INDEX "RoadDate_Idx" ON "TrafficData" USING btree ("RoadID", "DateID");

列RoadID唯一标识正在记录数据的道路，而DateID标识数据的年份中的天数（1..365）-基本上是ExactDateTime的四舍五入表示。

我有大约1亿行; 在“RoadID”列中有1,000个不同的值，在“DateID”列中有365个不同的值。

然后我运行以下查询：

SELECT * FROM "TrafficData"
WHERE "RoadID"='Station_1'
AND "DateID">20100610 AND "DateID"<20100618;

这需要长达三秒钟的时间才能完成，我无论如何都想不通为什么。

EXPLAIN ANALYZE 给出了以下输出：

Bitmap Heap Scan on "TrafficData"  (cost=104.84..9743.06 rows=2496 width=47) (actual time=35.112..2162.404 rows=2016 loops=1)
  Recheck Cond: ((("RoadID")::text = 'Station_1'::text) AND ("DateID" > 20100610::numeric) AND ("DateID" < 20100618::numeric))
  ->  Bitmap Index Scan on "RoadDate_Idx"  (cost=0.00..104.22 rows=2496 width=0) (actual time=1.637..1.637 rows=2016 loops=1)
        Index Cond: ((("RoadID")::text = 'Station_1'::text) AND ("DateID" > 20100610::numeric) AND ("DateID" < 20100618::numeric))
Total runtime: 2163.985 ms

我的电脑配置：

• Windows 7操作系统
• Postgres 9.0数据库
• 4GB内存

如果您有任何有用的建议，我将不胜感激！

- TroutKing

---

啊，没错；谢谢你指出来。站点编号==路段编号。是我弄错了。我只是在表定义中更改了列名，以使内容更直观，但忘记在查询定义和查询输出中更改名称。我已更新问题以包含正确的列名。 - TroutKing

@Simon，我认为从8.3或8.4开始，自动清理（autovacuum）应该是默认开启的。 - vol7ron

@vol7ron 是的，应该是这样，但我见过一些人关闭了这个功能（只是因为它会使数据库变慢 :) ） - Szymon Lipiński

@TroutKing：这两个字段是没有意义的。不同数据的索引字段才是重要的。如果所有100M记录的日期都相同，那么搜索索引表的需求就是没有意义的。另外，你在运行什么样的CPU？可能你已经达到了2100ms的最大性能。虽然对我来说，psql总是比mysql快，但你应该比较一下这两者；因为你是在桌面上进行这个操作的。 - vol7ron

因此，限制因素可能是您的硬盘或内存，但要搜索100M条记录并不少。对于台式电脑来说，我认为2秒是一个不错的响应时间，特别是在搜索两个条件的情况下。 - vol7ron

Answer 1

明显最慢的部分是从表中获取数据，因为索引访问似乎非常快。您可以通过优化内存使用参数（参见 http://wiki.postgresql.org/wiki/Performance_Optimization 和 http://www.varlena.com/GeneralBits/Tidbits/perf.html）或者通过发出 CLUSTER 命令来优化表中数据的布局（参见 http://www.postgresql.org/docs/8.3/static/sql-cluster.html）。

CLUSTER "TrafficData" USING "RoadDate_Idx";

应该这样做。

- Daniel

---

昨天我尝试了这个，但是当 CLUSTER 命令在运行了十一个小时后仍然没有完成时，我不得不放弃。 - TroutKing

你好...也许一个简单的排序选择插入到另一个表中会更快？如果CLUSTER这么慢，那么它似乎真的是一个io问题，并且数据似乎根本不匹配索引。如果可以的话，请在服务器级别的硬件上尝试一下... - Daniel

Answer 2

补充一下Daniel的回答，集群操作是一次性的过程，重新排列磁盘上的数据。目的是从较少的磁盘块中获取2000个结果行。

由于这是虚拟数据，用于找出如何快速查询它，我建议以更接近生成模式的方式重新加载它。我想数据是按天生成的，这将在DateID和磁盘位置之间产生强烈的相关性。如果是这样的话，那么我要么按DateID进行集群，要么将测试数据分成365个单独的加载，并重新加载它们。

如果没有这样做，并且有随机生成的数据，你最有可能需要对磁盘头执行超过2000次寻道操作。

我还会检查你在Windows 7上运行的其他任何程序是否会增加你不需要的读取时间，例如确保读取的块不包含病毒签名，或同时执行自动计划的磁盘碎片整理（导致磁盘头几乎永远都不在上次读取数据库块时的位置）。

- Stephen Denne

Answer 3

• 4GB RAM -> 6+ 如果你有一亿条记录，这并不算大，但对于台式机来说，内存可能很重要。如果这不是台式机，我不确定为什么你会有这么少的内存
• AND "DateID">20100610 AND "DateID"<20100618; -> DateID BETWEEN 20100611 AND 20100617;
• 在 DateID 上创建索引
• 去掉所有字段名称周围的双引号
• 将 RoadID 改为文本字段，而不是 VarChar

- vol7ron

---

很遗憾，RAM升级不是一个选项。更多的RAM也不应该真正产生任何影响，因为索引文件已经足够“小”以加载到内存中。我也尝试了BETWEEN日期ID，但遗憾的是没有任何改变。 - TroutKing

你也想在DateID上创建一个索引；请查看添加的内容。 - vol7ron

好的，我已经在DateID上添加了索引。可惜没有任何区别。 - TroutKing