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

Re: case-insensitive Index

From: kmh496 <kmh496(at)kornet(dot)net>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: case-insensitive Index
Date: 2006-07-26 09:13:19
Message-ID: 1153905200.5175.1.camel@var.sirfsup.com (view raw or flat)
Thread:
Lists: pgsql-novice
2006-07-19 (수), 10:53 -0400, Tom Lane 쓰시길:
> "Verena Ruff" <lists(at)triosolutions(dot)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=#                                                                 








In response to

Responses

pgsql-novice by date

Next:From: Richard Broersma JrDate: 2006-07-26 11:28:09
Subject: Fwd: Re: Installation "baby" steps (next steps)?
Previous:From: Christoph FrickDate: 2006-07-26 09:09:38
Subject: Re: Limiting character count

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