Re: index and ilke question
От | CARLADATA, mailing list |
---|---|
Тема | Re: index and ilke question |
Дата | |
Msg-id | 002601c5b934$0c30f460$340aa8c0@geisslinger обсуждение исходный текст |
Ответ на | index and ilke question (Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>) |
Список | pgsql-general |
SHOW ALL list all show the value of a run-time parameter. LC_COLLATE String sort order LC_CTYPE Character classification (What is a letter? Its upper-case equivalent?) LC_MESSAGES Language of messages LC_MONETARY Formatting of currency amounts LC_NUMERIC Formatting of numbers LC_TIME Formatting of dates and times LC_COLLATE and LC_CTYPE is set on C, you just need index on the text field. If not C then you can create an index with a special operator class (s. documation 11.6). ----- Original Message ----- From: "Joost Kraaijeveld" <J.Kraaijeveld@Askesis.nl> To: "Pgsql-General" <pgsql-general@postgresql.org> Sent: Sunday, September 11, 2005 10:49 AM Subject: [GENERAL] index and ilke question > Hi, > > I want to use the following query: > > select * from customers where lastname ilike 'jansen%' > > Explain says it uses a sequential scan on customers while there is an > index on lastname (and 'jansen%' contains 1800 entries in a table of > 370.000 customers so a index scan should be more logical?). > > The docs say "However, if your server does not use the C locale you will > need to create the index with a special operator class to support > indexing of pattern-matching queries." > > This seems to be the case as it does not use the index. > > Two questions: > > 1. How can I check if my (PostgreSQL or Linux?) server uses the C > locale ? > > 2. And if it does not the (correct?) C locale is the syntax for a > correct index the following, assuming that lastname is of type "text": > > CREATE INDEX test_index ON prototype.customers (lastname > text_pattern_ops); > > (I tried this, but it did not change anything so I assume that either my > assumptions about when to use an index as described above or my syntax > are wrong) > > TIA > > -- > Groeten, > > Joost Kraaijeveld > Askesis B.V. > Molukkenstraat 14 > 6524NB Nijmegen > tel: 024-3888063 / 06-51855277 > fax: 024-3608416 > e-mail: J.Kraaijeveld@Askesis.nl > web: www.askesis.nl > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > >
В списке pgsql-general по дате отправления: