Re: case-insensitive Index
От | kmh496 |
---|---|
Тема | Re: case-insensitive Index |
Дата | |
Msg-id | 1153905200.5175.1.camel@var.sirfsup.com обсуждение исходный текст |
Ответ на | case-insensitive Index ("Verena Ruff" <lists@triosolutions.at>) |
Ответы |
Re: case-insensitive Index
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-novice |
2006-07-19 (수), 10:53 -0400, Tom Lane 쓰시길: > "Verena Ruff" <lists@triosolutions.at> writes: > > EXPLAIN SELECT * FROM kundepersonhc WHERE UPPER(pers_nachname) LIKE > > UPPER('me%'); > > says that a seq scan is done. > > > I used this statement to define the index: > > CREATE INDEX kundepersonhc_upper_pers_nachname ON kundepersonhc > > (UPPER(pers_nachname)); > > If your locale is not C then you'd need to specify a special index > opclass while creating the index in order to let it support LIKE > queries, eg > > CREATE INDEX kundepersonhc_upper_pers_nachname ON kundepersonhc > (UPPER(pers_nachname) text_pattern_ops); > > http://www.postgresql.org/docs/8.1/static/indexes-opclass.html > I tried declaring a varchar_pattern_ops and text_varchar_ops on a character varying (60) column but it still does a sequential scan on the column. Anybody know any advice about what i can try next? i want to use the index. thanks, joe =============================================== Welcome to psql 8.1.3, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit mod=# explain select word from english_english where word like 'here'; QUERY PLAN ------------------------------------------------------------------- Seq Scan on english_english (cost=0.00..8010.09 rows=5 width=13) Filter: ((word)::text ~~ 'here'::text) (2 rows) mod=# \d english_english; Table "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 (upper(word::text) varchar_pattern_ops) "english_english_wordid_idx" btree (wordid) mod=# drop index english_english_word_idx; DROP INDEX mod=# create index english_english_word_idx on english_english(UPPER(word) text_pattern_ops) mod-# ; CREATE INDEX mod=# explain select word from english_english where word like 'here'; QUERY PLAN ------------------------------------------------------------------- Seq Scan on english_english (cost=0.00..8010.09 rows=5 width=13) Filter: ((word)::text ~~ 'here'::text) (2 rows) mod=#
В списке pgsql-novice по дате отправления: