Btree index ignored on SELECT until VACUUM ANALYZE

From: Lee Kindness <lkindness(at)csl(dot)co(dot)uk>
To: pgsql-bugs(at)postgresql(dot)org
Cc: lkindness(at)csl(dot)co(dot)uk, pgsql-hackers(at)postgresql(dot)org, maves(at)csl(dot)co(dot)uk
Subject: Btree index ignored on SELECT until VACUUM ANALYZE
Date: 2001-10-11 08:27:44
Message-ID: 15301.22528.562924.161962@elsick.csl.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-hackers

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(at)elsick:~% createdb obs_test
CREATE DATABASE
lkind(at)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

Browse pgsql-bugs by date

  From Date Subject
Next Message pgsql-bugs 2001-10-11 10:37:44 Bug #478: Integer overflow in pg_dump
Previous Message Hiroshi Inoue 2001-10-10 23:13:00 Re: SQLPutData bug ?

Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Meskes 2001-10-11 09:28:30 Deadlock? idle in transaction
Previous Message steve 2001-10-11 07:49:06 Re: pg_dump oid problems