Lists: | pgsql-novice |
---|
From: | "Verena Ruff" <lists(at)triosolutions(dot)at> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | case-insensitive Index |
Date: | 2006-07-19 13:07:23 |
Message-ID: | 19682.86.59.55.246.1153314443.squirrel@v2830.vanager.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-novice |
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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Verena Ruff" <lists(at)triosolutions(dot)at> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: case-insensitive Index |
Date: | 2006-07-19 14:53:58 |
Message-ID: | 26019.1153320838@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-novice |
"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
regards, tom lane
From: | "Verena Ruff" <lists(at)triosolutions(dot)at> |
---|---|
To: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: case-insensitive Index |
Date: | 2006-07-19 15:43:41 |
Message-ID: | 21160.86.59.55.246.1153323821.squirrel@v2830.vanager.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-novice |
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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Verena Ruff" <lists(at)triosolutions(dot)at> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: case-insensitive Index |
Date: | 2006-07-19 15:44:53 |
Message-ID: | 5345.1153323893@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-novice |
"Verena Ruff" <lists(at)triosolutions(dot)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
From: | "Verena Ruff" <lists(at)triosolutions(dot)at> |
---|---|
To: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: case-insensitive Index |
Date: | 2006-07-19 15:49:05 |
Message-ID: | 21223.86.59.55.246.1153324145.squirrel@v2830.vanager.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-novice |
Tom Lane wrote:
> "Verena Ruff" <lists(at)triosolutions(dot)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
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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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=#
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | kmh496 <kmh496(at)kornet(dot)net> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: case-insensitive Index |
Date: | 2006-07-26 14:49:38 |
Message-ID: | 1791.1153925378@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-novice |
kmh496 <kmh496(at)kornet(dot)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