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 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
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:
Show quoted text
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 operatorDear 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 FirstNamebut
SELECT * 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 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)...
Show quoted text
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)
EXPLAINI 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 operatorDear 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 FirstNamebut
SELECT * 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 advance
Andy---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?