From: | Joost Kraaijeveld <J(dot)Kraaijeveld(at)Askesis(dot)nl> |
---|---|
To: | Pgsql-General <pgsql-general(at)postgresql(dot)org> |
Subject: | index and ilke question |
Date: | 2005-09-11 08:49:53 |
Message-ID: | 1126428593.3723.15.camel@Panoramix |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)Kraaijeveld(at)Askesis(dot)nl
web: www.askesis.nl
From | Date | Subject | |
---|---|---|---|
Next Message | Sim Zacks | 2005-09-11 09:26:36 | pg_autovacuum not doing anything |
Previous Message | Matthew Peter | 2005-09-11 07:30:59 | Re: back references using regex |