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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
