CREATE INDEX spoils IndexScan planns

Hi!

SOME BACKGROUND:
I am having a rather annoying problem which I have no explonation to so
I am calling it a bug.
Currenty I am designing a rather huge database with 700+ tables and
unfortunately I am quite late to discover
this as my test data is imported via \i
the_whole_database_definition_and_test_data.sql

The definition file is getting pretty big, so when I noticed that I
forgot to define one of the indexes I did a
CREATE INDEX by hand. It was than that I noticed that all planns to use
IndexScan on that table had been canceled
and that everything was to be done via SeqScan (full table scan?). Which
renders my table and the database useless
in real life. There are about 4.3 million records added to that table,
per month. SeqScan BAD - IndexScan GOOD! :)

Google time... Users complained about VACUUM (analyze | full)
"corrupting" the index or at least it's
planned usage. There have been some suggestions about strict datatype
casting (helps the optimizer), locale and
collation settings, etc. I've tried every resolution suggestion I was
able to find. Just to be absolutely sure,
I've even donwloaded the 7.4 BETA 5 and verified that the problem is
still present. No luck.


PROBLEM (btree index):
Index Scan plannes are ignored after ANY of the following:
CREATE INDEX... , VACUUM, VACUUM FULL, VACUUM ANALYZE, ANALYZE, REINDEX,
REINDEX TABLE, REINDEX INDEX
and ./contrib/reindexdb (yes, I've tried that too).

I have droped the whole database for each of the above commands and did
traces for all of them
as the one described bellow. What is even stranger is that the even the
new index is never used?


PLATFORM (scope):
- The original problem was discovered on postgres 7.3.4 running on
RedHat 7.3 (Intel)
- This has been verified on another server running postgres 7.3.4 RedHat
7.2 (Intel)
- Problem still present in postgres 7.4.beta5


RESOLUTION ATTEMPTS:
- droping and recreating the database makes it OK (duh?!) until any of
the above commands are executed,
- reindex doesn't help, but is actually a part of the problem,
- iso8859-1 LATIN1 (no change)
- iso8859-2 LATIN2 (no change)
- --collate=C (no change)
- strict type casting the id SERIAL as ::INTEGER and ::INT4 (no change)
- type casting the SELECT query with id::int4 (no change)
- problem persistant throughout all column data types (INT, SERIAL8,
TEXT, VARCHAR, ...)


RESOLUTION (a really bad one):
Someone suggested SET -ing the enable_seqscan to OFF / FALSE, which
seems to do the trick, but I generaly
consider this to be bad idea.


TRACE:
The original tables are much bigger, so I've tried to simplify things here.
Please let me know if there is anything that I could help you with.


In desperate need of help,

Kind regards,

Vince.


Here's the step-by-step trace on an (out of the box clean) 7.4.beta5:
-------------------------------------------------------------------------

[root@charlie test]# psql -U postgres template1
Welcome to psql 7.4beta5, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

template1=# create database test;
CREATE DATABASE

template1=# \c test
You are now connected to database "test".

test=# \d
No relations found.

test=# CREATE table test(
test(#  id SERIAL not NULL PRIMARY KEY,
test(#  first_name VARCHAR(50),
test(#  last_name  VARCHAR(50)
test(# )with OIDS;
NOTICE:  CREATE TABLE will create implicit sequence "test_id_seq" for
"serial" column "test.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"test_pkey" for table "test"
CREATE TABLE

test=# INSERT INTO test(first_name, last_name) VALUES('John', 'Smith');
INSERT INTO test(first_name, last_name) VALUES('Rob', 'Roy');
INSERT INTO test(first_name, last_name) VALUES('William', 'McAndrews');
INSERT INTO test(first_name, last_name) VALUES('Sean', 'O''Neil');
INSERT INTO test(first_name, last_name) VALUES('Terrance', 'Phillup');
INSERT 17164 1
test=# INSERT INTO test(first_name, last_name) VALUES('Rob', 'Roy');
INSERT 17165 1
test=# INSERT INTO test(first_name, last_name) VALUES('William',
'McAndrews');
INSERT 17166 1
test=# INSERT INTO test(first_name, last_name) VALUES('Sean', 'O''Neil');
INSERT 17167 1
test=# INSERT INTO test(first_name, last_name) VALUES('Terrance',
'Phillup');
INSERT 17168 1
test=#

test=# select * from test;
 id | first_name | last_name
----+------------+-----------
  1 | John       | Smith
  2 | Rob        | Roy
  3 | William    | McAndrews
  4 | Sean       | O'Neil
  5 | Terrance   | Phillup
(5 rows)

test=# explain analyze select * from test;
                                            QUERY PLAN
---------------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=0.00..20.00 rows=1000 width=90) (actual
time=0.040..0.077 rows=5 loops=1)
 Total runtime: 0.347 ms
(2 rows)

test=# explain analyze select * from test where id = 3;
                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Index Scan using test_pkey on test  (cost=0.00..4.82 rows=2 width=90)
(actual time=0.095..0.110 rows=1 loops=1)
   Index Cond: (id = 3)
 Total runtime: 0.466 ms
(3 rows)

test=# select * from test where id = 3;
 id | first_name | last_name
----+------------+-----------
  3 | William    | McAndrews
(1 row)



-- PROBLEM STARTS HERE!
--------------------------


test=# create index my_index on test(last_name);
CREATE INDEX

test=# explain analyze select * from test where id = 3;
                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=0.00..1.06 rows=2 width=90) (actual
time=0.098..0.122 rows=1 loops=1)
   Filter: (id = 3)
 Total runtime: 0.524 ms
(3 rows)

test=# \d test
                                    Table "public.test"
   Column   |         Type          |                      Modifiers
------------+-----------------------+------------------------------------------------------
 id         | integer               | not null default
nextval('public.test_id_seq'::text)
 first_name | character varying(50) |
 last_name  | character varying(50) |
Indexes:
    "test_pkey" primary key, btree (id)
    "my_index" btree (last_name)

test=# reindex table test;
REINDEX

test=# explain analyze select * from test where id = 3;
                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=0.00..1.06 rows=2 width=90) (actual
time=0.088..0.112 rows=1 loops=1)
   Filter: (id = 3)
 Total runtime: 0.361 ms
(3 rows)

test=# vacuum full;
VACUUM

test=# explain analyze select * from test where id = 3;
                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=0.00..1.06 rows=2 width=90) (actual
time=0.099..0.122 rows=1 loops=1)
   Filter: (id = 3)
 Total runtime: 0.527 ms
(3 rows)

test=# vacuum analyze;
VACUUM

test=# explain analyze select * from test where id = 3;
                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=0.00..1.06 rows=2 width=23) (actual
time=0.090..0.113 rows=1 loops=1)
   Filter: (id = 3)
 Total runtime: 0.405 ms
(3 rows)

test=# analyze;
ANALYZE

test=# explain analyze select * from test where id = 3;
                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=0.00..1.06 rows=2 width=23) (actual
time=0.097..0.121 rows=1 loops=1)
   Filter: (id = 3)
 Total runtime: 2.669 ms
(3 rows)

test=# reindex index test_pkey;
REINDEX

test=# explain analyze select * from test where id = 3;
                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=0.00..1.06 rows=2 width=23) (actual
time=0.089..0.113 rows=1 loops=1)
   Filter: (id = 3)
 Total runtime: 0.366 ms
(3 rows)

test=# explain analyze select * from test where last_name = 'McAndrews';
                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=0.00..1.06 rows=2 width=23) (actual
time=0.107..0.140 rows=1 loops=1)
   Filter: ((last_name)::text = 'McAndrews'::text)
 Total runtime: 0.393 ms
(3 rows)

test=# insert into test(first_name, last_name) values('Vince', 'K.');
INSERT 17173 1

test=# explain analyze select * from test where last_name = 'K.';
                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=0.00..1.06 rows=2 width=23) (actual
time=0.136..0.147 rows=1 loops=1)
   Filter: ((last_name)::text = 'K.'::text)
 Total runtime: 0.393 ms
(3 rows)

-- END: trace
=================================================================================