Full-text search with related tables in PostgreSQL

Question

Task: Implement full text search in multiple tables in PostgreSQL.

For example, a table of projects - projects: There are two fields in which need to search: title and description. Create an index on this table:

ALTER TABLE public.projects ADD COLUMN search_fts tsvector;
UPDATE public.projects SET search_fts =
to_tsvector(
    coalesce(title, '') ||' '||
    coalesce(description, '') || ' '
);
CREATE INDEX in_projects_idx ON public.projects
  USING gin (search_fts);

Now search is simple:

SELECT
    DISTINCT p.id,
    p.title,
    ts_rank(
        p.search_fts,
        to_tsquery('word1 | word2')
    ) as rank
FROM
    projects p
WHERE
    p.search_fts @@ to_tsquery('word1 | word2')
ORDER BY rank DESC;

Sophisticate. Now we need to look at the table and project categories - project_categories. Similar to create tsvector and for the table on the field name. Search query now is:

SELECT
    DISTINCT p.id,
    p.title,
    category.name as categoryName,
    ts_rank(
        (
            coalesce(p.search_fts, '') ||' '||
            coalesce(category.search_fts, '')
        ),
        to_tsquery('word1 | word2 | categoryName')
    ) as rank
FROM
    projects p
LEFT JOIN project_categories category
    ON p.category_id = category.category_id
WHERE
    (
        coalesce(p.search_fts, '') ||' '||
        coalesce(category.search_fts, '')
    ) @@ to_tsquery('word1 | word2 | categoryName')
ORDER BY rank DESC;

More sophisticate. Now the search has to work on dozens of tables related to projects as a one-to-many and many-to-many.

Number join's growing. Combine to a growing number of fields.

Actually the question the right way I'm going? How would you solve this problem?

I also think about the version with view. For example, built on such a request:

(
  SELECT 
      p.id as project_id,
      p.search_fts
  FROM projects p
) UNION ALL (
  SELECT 
      p.id as project_id,
      category.search_fts
  FROM projects p
  JOIN project_categories category
      ON p.category_id = category.category_id
)

Finally, we obtain the overall index. By which to search. But in the view have to combine a dozen requests for various communication options. Interested in your views on this option.

Sorry for my english.

Craig Ringer Craig Ringer · Accepted Answer · 2013-02-24T23:51:24

You have a couple of options:

Use triggers on all the input tables to maintain a summary table that combines all the records of interest in text rows. This works OK for simple-ish cases like a customer that has 1 or more phone and one or more address entries, which you can put in the summary search table as |customername|customerid|phone1|phone2|phone3|...|address1|address2|... using simple concatenation and string_agg over joined tables. It scales poorly to deeply nested relationships, though, and can impose some locking and concurrency problems.
Use an external search system like Apache Solr that's designed to solve complex search problems.

Full-text search with related tables in PostgreSQL

1 Answers