Thread: full text search and ILIKE type clauses.
I want to be able to search a lot of fields using queries that use ILIKE and unfortunately many of the queries will be using the '%SOMETHING%' or '%SOMETHING' type clauses. Since indexes are useless on those I was thinking I could use tsvectors but I can't figure out how to accomplish this. One option I have seen reccomended is to create a tsvector field and concat the various fields into that field as a tsvector. That would work for general text search but I am not sure how I could query FIELD1 ILIKE '%SOMETHING%' vs FIELD2 ILIKE '%SOMETHING%' . Would using hstore help in this case? I should point out that some of the fields are numbers. I am willing to deal with those separately by building other indexes for them but of course a unified approach would be preferable. Any help would be much appreciated. Thanks.
Tim Uckun <timuckun@gmail.com> writes:
> I want to be able to search a lot of fields using queries that use
> ILIKE and unfortunately many of the queries will be using the
> '%SOMETHING%' or '%SOMETHING' type clauses. Since indexes are useless
> on those I was thinking I could use tsvectors but I can't figure out
> how to accomplish this.
Full text search is not going to help for this unless you are willing to
be very lax about replicating the semantics of ILIKE. For example,
ILIKE '%foo%' should match "foo" anywhere within a word, but FTS is not
going to be able to do better than finding words that begin with "foo".
If you're using 9.1, you might look into contrib/pg_trgm instead.
regards, tom lane
> > If you're using 9.1, you might look into contrib/pg_trgm instead. If I was to use trgm would it be better to create a trigram index on each text field? In the past I have created a text field which contains the rest of the fields concatenated. That works great as long as you are looking for any word. Is there a way to specify which word should match? Could I combine hstore and tgm to match against individual words?
Tom, We made most of our text, varchar columns citext data types so that we could do case insensitive searches. Is this going to negate most of the index searches? It appeared to our DBA that it would be easier to use citext data type then need to use ILIKE instead? Michael Gould Intermodal Software Solutions, LLC 904-226-0978 -------- Original Message -------- Subject: Re: [GENERAL] full text search and ILIKE type clauses. From: Tom Lane <tgl@sss.pgh.pa.us> Date: Sun, March 11, 2012 7:56 pm To: Tim Uckun <timuckun@gmail.com> Cc: pgsql-general <pgsql-general@postgresql.org> Tim Uckun <timuckun@gmail.com> writes: > I want to be able to search a lot of fields using queries that use > ILIKE and unfortunately many of the queries will be using the > '%SOMETHING%' or '%SOMETHING' type clauses. Since indexes are useless > on those I was thinking I could use tsvectors but I can't figure out > how to accomplish this. Full text search is not going to help for this unless you are willing to be very lax about replicating the semantics of ILIKE. For example, ILIKE '%foo%' should match "foo" anywhere within a word, but FTS is not going to be able to do better than finding words that begin with "foo". If you're using 9.1, you might look into contrib/pg_trgm instead. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
> > We made most of our text, varchar columns citext data types so that we > could do case insensitive searches. Is this going to negate most of the > index searches? It appeared to our DBA that it would be easier to use > citext data type then need to use ILIKE instead? > In the same vein... Does postgres have case insensitive collations yet? Now that 9.1 supports column level collations that would be a really great option for case insensitive queries.
Tim, It is my understanding that since the extention citext is available that this gives you what your asking for and at least at this point isn't going to be part of the core. Michael Gould Intermodal Software Solutions, LLC 904-226-0978 -------- Original Message -------- Subject: Re: [GENERAL] full text search and ILIKE type clauses. From: Tim Uckun <timuckun@gmail.com> Date: Mon, March 12, 2012 1:20 pm To: mgould@isstrucksoftware.net Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-general <pgsql-general@postgresql.org> > > We made most of our text, varchar columns citext data types so that we > could do case insensitive searches. Is this going to negate most of the > index searches? It appeared to our DBA that it would be easier to use > citext data type then need to use ILIKE instead? > In the same vein... Does postgres have case insensitive collations yet? Now that 9.1 supports column level collations that would be a really great option for case insensitive queries.
> It is my understanding that since the extention citext is available that > this gives you what your asking for and at least at this point isn't > going to be part of the core. > For me it's more of a workaround than a solution but yes probably good enough. Collation is more subtle than case insensitive comparisons but for english anyway it should be good enough.
2012/3/12 Tim Uckun <timuckun@gmail.com>: >> It is my understanding that since the extention citext is available that >> this gives you what your asking for and at least at this point isn't >> going to be part of the core. >> > > For me it's more of a workaround than a solution but yes probably good > enough. Collation is more subtle than case insensitive comparisons but > for english anyway it should be good enough. Postgres uses system locales - so theoretically you can write own case insensitive locale. Regards Pavel Stehule > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general