pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedLIKE and SIMILAR TO
5+ messages / 4 participants
[nested] [flat]
* LIKE and SIMILAR TO
@ 2008-08-22 11:13 c k <shreeseva.learning@gmail.com>
2008-08-22 11:24 ` Re: LIKE and SIMILAR TO RW <postgres@tauceti.net>
2008-08-22 11:31 ` Re: [GENERAL] LIKE and SIMILAR TO Tino Wildenhain <tino@wildenhain.de>
2008-08-22 12:00 ` Re: LIKE and SIMILAR TO Ivan Sergio Borgonovo <mail@webthatworks.it>
0 siblings, 3 replies; 5+ messages in thread
From: c k @ 2008-08-22 11:13 UTC (permalink / raw)
To: pgsql-general@postgresql.org; pgsql-admin
Hello all,
As we are migrating our ERP application from MySQL to PostgreSQL we have
some difficulties. One of them is use of Like and Similar to operators. We
often use LIKE to search a string from front-end without case sensetivity.
As postgreSQL's LIKE is case sensitive, we tried ILIKE and SIMILAR TO, but
both are slower than LIKE and we must need case insensitivity. How can we
get this by increases speed. All search columns are VARCHAR(100)to
VARCHAR(250). Currently without index.
Thanks,
CPK
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: LIKE and SIMILAR TO
2008-08-22 11:13 LIKE and SIMILAR TO c k <shreeseva.learning@gmail.com>
@ 2008-08-22 11:24 ` RW <postgres@tauceti.net>
2 siblings, 0 replies; 5+ messages in thread
From: RW @ 2008-08-22 11:24 UTC (permalink / raw)
To: c k <shreeseva.learning@gmail.com>; +Cc: pgsql-general@postgresql.org
Maybe you should give tsearch2 a try and create a GIN index.
Regex searches are sometimes a option.
Robert
c k wrote:
> Hello all,
> As we are migrating our ERP application from MySQL to PostgreSQL we
> have some difficulties. One of them is use of Like and Similar to
> operators. We often use LIKE to search a string from front-end without
> case sensetivity. As postgreSQL's LIKE is case sensitive, we tried
> ILIKE and SIMILAR TO, but both are slower than LIKE and we must need
> case insensitivity. How can we get this by increases speed. All search
> columns are VARCHAR(100)to VARCHAR(250). Currently without index.
>
> Thanks,
> CPK
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [GENERAL] LIKE and SIMILAR TO
2008-08-22 11:13 LIKE and SIMILAR TO c k <shreeseva.learning@gmail.com>
@ 2008-08-22 11:31 ` Tino Wildenhain <tino@wildenhain.de>
2008-08-22 11:43 ` Re: [GENERAL] LIKE and SIMILAR TO c k <shreeseva.learning@gmail.com>
2 siblings, 1 reply; 5+ messages in thread
From: Tino Wildenhain @ 2008-08-22 11:31 UTC (permalink / raw)
To: c k <shreeseva.learning@gmail.com>; +Cc: pgsql-general@postgresql.org; pgsql-admin
Hi,
c k wrote:
> Hello all,
> As we are migrating our ERP application from MySQL to PostgreSQL we have
> some difficulties. One of them is use of Like and Similar to operators.
> We often use LIKE to search a string from front-end without case
> sensetivity. As postgreSQL's LIKE is case sensitive, we tried ILIKE and
> SIMILAR TO, but both are slower than LIKE and we must need case
> insensitivity. How can we get this by increases speed. All search
> columns are VARCHAR(100)to VARCHAR(250). Currently without index.
You could build an index on lower(column) and use lower(column) like ...
this would speed up queries with exact match as well as 'foo%'
e.g. "start with..." match.
Make sure when you create the database cluster (initdb) you
used the currect locale, otherwise lower() (and ilike) probably
do not work as you might expect.
For any more complex searches I'd recommend full text index,
for example tsearch2.
Regards
Tino
Attachments:
smime.p7s (application/x-pkcs7-signature, 3.2K) download
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [GENERAL] LIKE and SIMILAR TO
2008-08-22 11:13 LIKE and SIMILAR TO c k <shreeseva.learning@gmail.com>
2008-08-22 11:31 ` Re: [GENERAL] LIKE and SIMILAR TO Tino Wildenhain <tino@wildenhain.de>
@ 2008-08-22 11:43 ` c k <shreeseva.learning@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: c k @ 2008-08-22 11:43 UTC (permalink / raw)
To: Tino Wildenhain <tino@wildenhain.de>; oleg@sai.msu.su; postgres@tauceti.net; +Cc: pgsql-general@postgresql.org; pgsql-admin
Thanks to all,
I will try to use tsearch2 with some other index and then reply.
Regards,
CPK
On Fri, Aug 22, 2008 at 5:01 PM, Tino Wildenhain <tino@wildenhain.de> wrote:
> Hi,
>
> c k wrote:
>
>> Hello all,
>> As we are migrating our ERP application from MySQL to PostgreSQL we have
>> some difficulties. One of them is use of Like and Similar to operators. We
>> often use LIKE to search a string from front-end without case sensetivity.
>> As postgreSQL's LIKE is case sensitive, we tried ILIKE and SIMILAR TO, but
>> both are slower than LIKE and we must need case insensitivity. How can we
>> get this by increases speed. All search columns are VARCHAR(100)to
>> VARCHAR(250). Currently without index.
>>
>
> You could build an index on lower(column) and use lower(column) like ...
> this would speed up queries with exact match as well as 'foo%'
> e.g. "start with..." match.
>
> Make sure when you create the database cluster (initdb) you
> used the currect locale, otherwise lower() (and ilike) probably
> do not work as you might expect.
>
> For any more complex searches I'd recommend full text index,
> for example tsearch2.
>
> Regards
> Tino
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: LIKE and SIMILAR TO
2008-08-22 11:13 LIKE and SIMILAR TO c k <shreeseva.learning@gmail.com>
@ 2008-08-22 12:00 ` Ivan Sergio Borgonovo <mail@webthatworks.it>
2 siblings, 0 replies; 5+ messages in thread
From: Ivan Sergio Borgonovo @ 2008-08-22 12:00 UTC (permalink / raw)
To: pgsql-general@postgresql.org
On Fri, 22 Aug 2008 16:43:47 +0530
"c k" <shreeseva.learning@gmail.com> wrote:
> Hello all,
> As we are migrating our ERP application from MySQL to PostgreSQL
> we have some difficulties. One of them is use of Like and Similar
> to operators. We often use LIKE to search a string from front-end
> without case sensetivity. As postgreSQL's LIKE is case sensitive,
> we tried ILIKE and SIMILAR TO, but both are slower than LIKE and
> we must need case insensitivity. How can we get this by increases
> speed. All search columns are VARCHAR(100)to VARCHAR(250).
> Currently without index.
I was going to suggest to create a functional index
create index on sometable using btree (upper(somecolumn));
select * from sometable where upper(somecolumn)
like '%' || upper(somestring) || '%';
but indexes aren't going to work if you're searching with a prefixed
%.
I was wondering what are the performances of postgresql vs. MySQL in
such case.
Anyway I'd tweak postgresql.conf before complaining it is slower
than MySQL. I've been surprised as well at how postgresql can be
fast.
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2008-08-22 12:00 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-08-22 11:13 LIKE and SIMILAR TO c k <shreeseva.learning@gmail.com>
2008-08-22 11:24 ` RW <postgres@tauceti.net>
2008-08-22 11:31 ` Tino Wildenhain <tino@wildenhain.de>
2008-08-22 11:43 ` c k <shreeseva.learning@gmail.com>
2008-08-22 12:00 ` Ivan Sergio Borgonovo <mail@webthatworks.it>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox