From: | Frank Bax <fbax(at)sympatico(dot)ca> |
---|---|
To: | "Andy Samuel" <andysamuel(at)geocities(dot)com> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: again, LIKE operator |
Date: | 2002-01-13 01:37:36 |
Message-ID: | 3.0.6.32.20020112203736.0079f7d0@pop6.sympatico.ca |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)postgresql(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2002-01-13 02:09:11 | Re: again, LIKE operator |
Previous Message | elisabeth maniez | 2002-01-12 19:30:44 | How to send automatically a NOTIFY when a COMMIT is done ? |