From: Gerardo Herzig <gherzig@fmed.uba.ar>
To: pgsql-sql@postgresql.org
Subject: index not being used. Why?
Date: Fri, 09 Mar 2007 12:01:30 -0300
Message-ID: <45F176CA.3050107@fmed.uba.ar> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gherzig@fmed.uba.ar
Subject: Re: index not being used. Why?
In-Reply-To: <45F176CA.3050107@fmed.uba.ar>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox