When adding an index to a (quite large, ~2 million rows) table
PostgreSQL continues to do sequential lookups until VACUUM ANALYZE is
run. Naturally performance is poor.
The CREATE INDEX statement takes considerable time.
Seen with 7.1.3 on Intel Linux (RedHat 7.0 & 7.1 and Solaris 2.6.
In the example below the data file (8 MB) can be found at:
http://services.csl.co.uk/postgresql/obs.gz
Consider the session below:
lkind@elsick:~% createdb obs_test
CREATE DATABASE
lkind@elsick:~% psql obs_test
obs_test=# CREATE TABLE obs (setup_id INTEGER, time REAL, value REAL, bad_data_flag SMALLINT);
CREATE
obs_test=# COPY obs FROM '/user/lkind/obs';
COPY
obs_test=# SELECT COUNT(*) FROM obs; count
---------1966593
(1 row)
obs_test=# CREATE UNIQUE INDEX obs_idx ON obs USING BTREE(setup_id, time);
CREATE
obs_test=# EXPLAIN SELECT * FROM obs WHERE setup_id = 300 AND time = 118;
NOTICE: QUERY PLAN:
Seq Scan on obs (cost=0.00..42025.90 rows=197 width=14)
EXPLAIN
obs_test=# VACUUM ANALYZE obs ;
VACUUM
obs_test=# EXPLAIN SELECT * FROM obs WHERE setup_id = 300 AND time = 118;
NOTICE: QUERY PLAN:
Index Scan using obs_idx on obs (cost=0.00..9401.60 rows=1 width=14)
EXPLAIN
obs_test=# \q