On 05/23/10 18:03, Andy Colson wrote: > On 05/22/2010 09:40 PM, Ivan Voras wrote: >> 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? >> > > I have something like this, but with PostGIS layers. When a person > clicks I search all the different layers (each a table) for > information. I use a stored proc. Each table has its own index so each > table is fast. It also lets me abstract out differences between the > layers (I can search each a little differently). > > If each of your tables had its own full text fields and indexes, then > write a stored proc to search them all individually, it should be pretty > quick. This looks like an interesting solution. And it could be done generically in our case by having a separate table describing which tables need to be searched and by what fields. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
