Re: again, LIKE operator

Oh really??   Works for me (I don't what locale reference is about though)...

fbax=# create table guestprofile ( firstname text );
CREATE
fbax=# create index guestprofile_firstname on guestprofile ( firstname );
CREATE
fbax=# explain SELECT * FROM GuestProfile WHERE FirstName LIKE 'a%';
NOTICE:  QUERY PLAN:
Index Scan using guestprofile_firstname on guestprofile  (cost=0.00..8.14
rows=10 width=12)
EXPLAIN

I wonder if it has something to do with the size of your table?

Frank

At 05:40 PM 1/12/02 +0700, Andy Samuel wrote:
>Just forget my previous email.
>
>PostgreSQL does not use index on LIKE 'a%' but it will use
>the index if LIKE 'ab%' or something longer.
>So the optimizer thinks it is not good enough if it's only
>1 character.
>Well done !
>
>Thank you
>Andy
>
>> ----- Original Message -----
>> From: Andy Samuel
>> To: pgsql-general@postgresql.org
>> Sent: Saturday, January 12, 2002 11:35 AM
>> Subject: [GENERAL] again, LIKE operator
>>
>> Dear All
>>
>> I've search the archive and manuals and it says I should
>> use C locale in order to enable LIKE to use index.
>> Postgresql was installed after I set the locale to C.  So
>> I'm sure the initdb will use C.
>>  From EXPLAIN I know that :
>>
>> SELECT * FROM GuestProfile WHERE FirstName LIKE 'a'
>>         will use index on FirstName
>>
>> but
>>
>> SELECT * FROM GuestProfile WHERE FirstName LIKE 'a%'
>>         will not use Index on FirstName
>>
>> I have 9,999,999 records and it was VACUUM ANALYZE-d.
>>
>> How do I enforce the optimizer to use the index ?
>>
>> Thank you in advance
>> Andy