why is scan not using index?
От | kmh496 |
---|---|
Тема | why is scan not using index? |
Дата | |
Msg-id | 1153234251.31921.3.camel@var.sirfsup.com обсуждение исходный текст |
Ответы |
Re: why is scan not using index?
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | 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>.
В списке pgsql-novice по дате отправления: