Home > mailing lists

Thread: again, LIKE operator

again, LIKE operator

From

"Andy Samuel"

Date:

12 January 2002, 02:35:35

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

Re: again, LIKE operator

From

"Andy Samuel"

Date:

12 January 2002, 23:15:47

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

Re: again, LIKE operator

From

Frank Bax

Date:

12 January 2002, 23:45:47

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

Re: again, LIKE operator

From

Tom Lane

Date:

13 January 2002, 00:16:07

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

Re: again, LIKE operator

From

"Andy Samuel"

Date:

14 January 2002, 00:46:17

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