why is scan not using index?

From: kmh496 <kmh496(at)kornet(dot)net>
To: pgsql-novice(at)postgresql(dot)org
Subject: why is scan not using index?
Date: 2006-07-18 14:50:51
Message-ID: 1153234251.31921.3.camel@var.sirfsup.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hello.
this should be in the FAQ, right?
well, i have a table (see "a" below) with 2 indexes
i run vacuum analyze.
then i run "explain .... select .." (see "b" below).
so, back to the original question.
why isn't is using the indexes?
I have lots of words to run these queries on, they need to use my little
index :)

-----------------a table struture ----------------
mod=# \d english_english;
Table "modpgwebuser.english_english"
Column | Type |
Modifiers
-----------+-----------------------------+------------------------------------------------------------
wordid | integer | not null default 0
see | character varying(100) |
hint | text |
source | integer |
submitter | character varying(25) |
pos | character varying(25) |
posn | integer |
syn | character varying(200) |
ant | character varying(200) |
word | character varying(60) |
def | text |
wordsize | smallint |
doe | timestamp without time zone | default '2006-03-23
22:50:04'::timestamp without time zone
Indexes:
"english_english_word_idx" btree (word)
"english_english_wordid_idx" btree (wordid)

mod=#

---------------- b---------- explain output ------------

mod=# vacuum analyze english_english;
VACUUM
mod=# explain
mod-# select word,def,wordid,pos,posn,wordsize,syn from
english_english where word like 'dispatch' order by wordsize desc;
QUERY PLAN
--------------------------------------------------------------------------
Sort (cost=8010.15..8010.16 rows=5 width=126)
Sort Key: wordsize
-> Seq Scan on english_english (cost=0.00..8010.09 rows=5
width=126)
Filter: ((word)::text ~~ 'dispatch'::text)
(4 rows)

mod=# select word,def,wordid,pos,posn,wordsize,syn from
english_english where word like 'dispatch' order by wordsize desc;
word | def
| wordid | pos | posn | wordsize | syn
----------+-------------------------------------------------------------------------------------------------+--------+-----+------+----------+---------------------------------------------
dispatch | killing a person or animal
| 42553 | n | 4 | 8 | {despatch}
dispatch |
| 42553 | n | 1 | 8 | {despatch},{communique}
dispatch | the property of being prompt and efficient; "it was done
with dispatch" | 42553 | n | 3 | 8 |
{despatch}, {expedition}, {expeditiousness}
dispatch | the act of sending off something
| 42553 | n | 2 | 8 | {despatch}, {shipment}
dispatch | dispose of rapidly and without delay and efficiently; "He
dispatched the task he was assigned" | 42553 | v | 4 | 8 |
dispatch |
| 42553 | v | 1 | 8 | {despatch}, {sendoff}
dispatch | kill intentionally and with premeditation; "The mafia boss
| 42553 | v | 3 | 8 | {murder}, {slay}, {hit},, {remove}
dispatch |
| 42553 | v | 2 | 8 | {discharge},{complete}
dispatch | kill without delay; "the traitor was dispatched by the
conspirators" | 42553 | v | 5 | 8
|
(9 rows)

--
<a href="http://openandout.com/~jcs/">who am I?</a>.

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Alan Hodgson 2006-07-18 14:52:13 Re: Disk space taken
Previous Message Richard Broersma Jr 2006-07-18 14:43:14 Re: Disk space taken