Very slow bitmap heap scan in Postgres

Question

I have the following simple table that contains traffic measurement data: ``` 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"); ``` The column RoadID uniquely identifies the road whose data is being recorded, while DateID identifies the day of the year (1..365) of the data - basically a rounded off representation of ExactDateTime. I have about 100.000.000 rows; there are 1.000 distinct values in the column "RoadID" and 365 distinct values in the column "DateID". I then run the following query: ``` SELECT * FROM "TrafficData" WHERE "RoadID"='Station_1' AND "DateID">20100610 AND "DateID"<20100618; ``` This takes up to three mind-boggling seconds to finish, and I cannot for the life of me figure out WHY. EXPLAIN ANALYZE gives me the following output: ``` Bitmap Heap Scan o…

Accepted Answer

The slow part is obviosly fetching the data from the tables, since the index access seems to be very fast. You might either optimize your RAM usage parameters (see http://wiki.postgresql.org/wiki/Performance_Optimization and http://www.varlena.com/GeneralBits/Tidbits/perf.html), or optimize the layout of the data in the table by issuing a CLUSTER command (see http://www.postgresql.org/docs/8.3/static/sql-cluster.html).

```
CLUSTER "TrafficData" USING "RoadDate_Idx";
```

should do it.

Very slow bitmap heap scan in Postgres

Solution

Problem