Received: from localhost (maia-1.hub.org [200.46.204.191])
	by postgresql.org (Postfix) with ESMTP id 68F4C9FBA7B
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri,  9 Mar 2007 11:09:18 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024)
	with ESMTP id 94831-06 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri,  9 Mar 2007 11:09:05 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from fmed.uba.ar (mail.fmed.uba.ar [157.92.152.1])
	by postgresql.org (Postfix) with ESMTP id B08209FBA88
	for <pgsql-sql@postgresql.org>; Fri,  9 Mar 2007 11:09:05 -0400 (AST)
Received: from [157.92.155.58] (unknown [157.92.155.58])
	by fmed.uba.ar (Postfix) with ESMTP id A2A744C2D76
	for <pgsql-sql@postgresql.org>; Fri,  9 Mar 2007 12:19:51 -0300 (ART)
Message-ID: <45F176CA.3050107@fmed.uba.ar>
Date: Fri, 09 Mar 2007 12:01:30 -0300
From: Gerardo Herzig <gherzig@fmed.uba.ar>
User-Agent: Mozilla Thunderbird 1.0.7 (X11/20050923)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: index not being used. Why?
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.29 tagged_above=0 required=5 tests=AWL, BAYES_50,
	SPF_HELO_PASS, SPF_PASS
X-Spam-Level: 
X-Archive-Number: 200703/110
X-Sequence-Number: 27748

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.64 rows=698 width=17)
         Filter: (upper((word)::text) ~~ 'TEST%'::text)
         ->  Bitmap Index Scan on words_upper_idx  (cost=0.00..8.19 rows=698 width=0)
               Index Cond: ((upper((word)::text) ~>=~ 'TEST'::character varying) 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 index on 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