Full text search on a complex schema - a classic problem?

From: Ivan Voras <ivoras(at)freebsd(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: Full text search on a complex schema - a classic problem?
Date: 2010-05-23 02:40:20
Message-ID: hta53k$aib$1@dough.gmane.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

I have a schema which tracks various pieces of information which would
need to be globally searchable. One approach I came up with to make all
of the data searchable is to create a view made of UNION ALL queries
that would integrate different tables into a common structure which
could be uniformly queried by using tsearch2 functions. This would work,
up to the point where it would be practically unavoidable (for
performance reasons) to create indexes on this view, which cannot be
done. I would like to avoid using a "hand-made" materialized view (via
triggers, etc.) because of administrative overhead and because it would
duplicate data, of which there is potentially a lot.

I think this looks like a fairly common problem with full text searches
on a large-ish schemas, so I'm wondering what are the best practices
here, specifically with using tsearch2?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Luis Daniel Lucio Quiroz 2010-05-23 02:45:12 Moving from Mysql
Previous Message Francisco Reyes 2010-05-22 23:22:55 Re: Select max(primary_key) taking a long time