index not being used. Why? - Mailing list pgsql-sql

Hi all. I have this 2 relations

gse=# \d pages                                    Table "public.pages"      Column        |       Type        |
           Modifiers                      
 
---------------------+-------------------+----------------------------------------------------id                  |
integer          | not null default nextval('pages_id_seq'::regclass)fullpath            | character varying |
last_modified_stamp| bigint            | title               | character varying | 
 
Indexes:   "pages_pkey" PRIMARY KEY, btree (id)   "pages_fullpath_idx" UNIQUE, btree (fullpath)   "pages_id_idx" btree
(id)


gse=# \d words            Table "public.words"   Column     |       Type        | Modifiers 
---------------+-------------------+-----------page_id       | integer           | word          | character varying |
word_position| integer           | 
 
Indexes:   "words_idx" btree (word)   "words_page_id_idx" btree (page_id)   "words_page_id_word_position_id" btree
(page_id,word_position)   "words_upper_idx" btree (upper(word::text) varchar_pattern_ops)
 

Now, when i execute
gse=# explain select * from words, pages where words.page_id = pages.id and upper(word) like 'TEST%';
                                           QUERY PLAN                                                                
 

------------------------------------------------------------------------------------------------------------------------------------------Hash
Join (cost=18.29..916.33 rows=698 width=72)  Hash Cond: ("outer".page_id = "inner".id)  ->  Bitmap Heap Scan on words
(cost=8.19..885.64rows=698 width=17)        Filter: (upper((word)::text) ~~ 'TEST%'::text)        ->  Bitmap Index Scan
onwords_upper_idx  (cost=0.00..8.19 rows=698 width=0)              Index Cond: ((upper((word)::text) ~>=~
'TEST'::charactervarying) AND (upper((word)::text) ~<~'TESU'::character varying))  ->  Hash  (cost=9.08..9.08 rows=408
width=55)       ->  Seq Scan on pages  (cost=0.00..9.08 rows=408 width=55)
 
(8 rows)


Watch the last row of the explain command. It makes a sequential scan on the pages table, like it is not using the
indexon the "id" field.
 

The result itself is OK, but i will populate the tables so i think that later that sequential scan would be a problem.

I have not idea why this is happening, hope you guys could give me a clue or make me understand the situation.

Im using postgres 8.1.3

Thanks!
Gerardo