Yes, I'm sure it's because of the number of records in the table.
I have re-VACUUM ANALYZE-d and LIKE 'a%' is still using SEQ SCAN while LIKE
'ab%' is using INDEX SCAN.
Thank you all for the help and replies
Best regards
Andy
----- Original Message -----
From: "Frank Bax" <fbax@sympatico.ca>
To: "Andy Samuel" <andysamuel@geocities.com>
Cc: <pgsql-general@postgresql.org>
Sent: Sunday, January 13, 2002 8:37 AM
Subject: Re: [GENERAL] 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
