index and ilke question - Mailing list pgsql-general

From Joost Kraaijeveld
Subject index and ilke question
Date
Msg-id 1126428593.3723.15.camel@Panoramix
Whole thread Raw
List 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.Kraaijeveld@Askesis.nl
web: www.askesis.nl



pgsql-general by date:

Previous
From: "Sim Zacks"
Date:
Subject: pg_autovacuum not doing anything
Next
From: Ron Mayer
Date:
Subject: Re: Postgresql Hosting