Re: again, LIKE operator
От | Andy Samuel |
---|---|
Тема | Re: again, LIKE operator |
Дата | |
Msg-id | 001701c19ca5$597c9bc0$0200a8c0@edpgm обсуждение исходный текст |
Ответ на | again, LIKE operator ("Andy Samuel" <andysamuel@geocities.com>) |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: