Обсуждение: 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
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 SamuelSent: Saturday, January 12, 2002 11:35 AMSubject: [GENERAL] again, LIKE operatorDear AllI'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 FirstNamebutSELECT * FROM GuestProfile WHERE FirstName LIKE 'a%' will not use Index on FirstNameI have 9,999,999 records and it was VACUUM ANALYZE-d.How do I enforce the optimizer to use the index ?Thank you in advanceAndy
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
Frank Bax <fbax@sympatico.ca> writes:
> I wonder if it has something to do with the size of your table?
Statistics, or lack thereof ...
regards, tom lane
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