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 ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
