Обсуждение: case-insensitive Index
Hello, I'm not sure if I use the index for a case-insensitive search the right way. It is a table with about 45000 records, pers_id is the primary key, pers_nachname is a character varying which should have an case-insensitive index. EXPLAIN SELECT * FROM kundepersonhc WHERE pers_id=42612; says that the index for the primary key is used 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)); What did you wrong here? Regards, Verena
"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 regards, tom lane
Tom Lane wrote: > 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 I didn't specify a locale when I called initdb, so it should be postgreSQL's standard C, or? How can check the locale of a database? Regards, Verena Ruff
"Verena Ruff" <lists@triosolutions.at> writes: > I didn't specify a locale when I called initdb, so it should be > postgreSQL's standard C, or? How can check the locale of a database? It'd depend on what LANG/LC_ALL settings initdb was run under. Use "show lc_collate" to check. regards, tom lane
Tom Lane wrote: > "Verena Ruff" <lists@triosolutions.at> writes: >> I didn't specify a locale when I called initdb, so it should be >> postgreSQL's standard C, or? How can check the locale of a database? > > It'd depend on what LANG/LC_ALL settings initdb was run under. > Use "show lc_collate" to check. de_DE.UTF-8. So I'll dig into the docs you pointed me to. Regards, Verena Ruff
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=#
kmh496 <kmh496@kornet.net> writes: > 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. That's because the index doesn't match the query: > mod=# create index english_english_word_idx on > english_english(UPPER(word) text_pattern_ops) > mod=# explain select word from english_english where word like 'here'; You'd need select word from english_english where upper(word) like upper('here'); to use that index (and also to get the right answer, if your goal is case-insensitive matching). regards, tom lane