代码之家 › 专栏 › 技术社区 › TroutKing

Postgres中非常慢的位图堆扫描

indexing postgresql performance

TroutKing · 技术社区 · 14 年前

我有一个包含流量测量数据的简单表:

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的四舍五入表示。

我有大约100.000.000行;在“RoadID”列中有1000个不同的值,在“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
博士后9.0
4GB内存

我非常感谢任何有用的指点!

3 回复 | 直到 14 年前

Daniel 14 年前

缓慢的部分显然是从表中获取数据,因为索引访问似乎非常快。您可以优化RAM使用参数(请参见 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";

应该这么做。

Stephen Denne 14 年前

除了Daniel的回答之外,集群操作是一个一次性的过程,它重新排列了磁盘上的数据。其目的是从更少的磁盘块中获取2000个结果行。

由于这是虚拟数据,用于了解如何快速查询它,因此我建议重新加载它,以更接近生成时如何加载它的模式。我认为数据是一天一次生成的,这将有效地导致 DateID 以及磁盘上的位置。如果是这样的话,我会 日期ID ,或将测试数据拆分为365个单独的加载,然后重新加载。

如果没有这些,并且有随机生成的数据,您很可能需要对磁盘头执行2000次以上的查找。

我还要检查你在Windows 7上运行的其他任何东西没有给那些你不需要的读取增加时间,比如确保读取的块不包含病毒签名,或者同时执行自动计划的磁盘碎片整理(导致磁盘头几乎不可能接近上次的位置读取了数据库块)。

vol7ron 14 年前

4GB RAM->6+您有100万条记录,虽然不太大,但对于台式机内存来说可能很重要。如果这不是桌面,我不知道你为什么会有这么少的内存
AND "DateID">20100610 AND "DateID"<20100618; -> DateID BETWEEN 20100611 AND 20100617;
在DateID上创建索引
去掉字段名周围的所有双引号
代替VarChar,make RoadID 文本字段