Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

Next:From: Alan HodgsonDate: 2006-07-18 14:52:13
Subject: Re: Disk space taken
Previous:From: Richard Broersma JrDate: 2006-07-18 14:43:14
Subject: Re: Disk space taken

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group