title: Optimize Postgres Full Text Search Across Related Tables
body: <p>I'm looking for a review of my search function. Details below.</p>
  <p>I have the following tables:</p>
  <pre class="lang-sql prettyprint-override"><code>CREATE TABLE table_a (
      id serial PRIMARY KEY,
      text_a text UNIQUE
      -- other fields ...
  );
  
  CREATE TABLE table_b (
      id serial PRIMARY KEY,
      table_a_id integer NOT NULL REFERENCES table_a(id), 
      text_b text,
      -- other fields ...
      unique (table_a_id, text_b)
  );
  </code></pre>
  <p>I need to search in <code>table_a</code> text combined from <code>text_a </code> and <code>text_b</code> and return a table containing <code>table_a</code> instances, but not repeating, e.g maybe the search returns 2 records from <code>table_b</code> that points to the same <code>text_a</code>, in this case, the response should contain this single instance from <code>table_a</code> only once, for each <code>table_a</code> instance.</p>
  <p>Other specification is put more weight on the <code>text_a</code>.</p>
  <p>I already have this working, but maybe it can be done better, here is the code:</p>
  <pre class="lang-sql prettyprint-override"><code>CREATE EXTENSION pg_trgm;
  
  CREATE INDEX table_a_text_a_gin_idx ON table_a
  USING GIN ((text_a) gin_trgm_ops);
  
  CREATE INDEX table_b_text_a_gin_idx ON table_b
  USING GIN ((text_b) gin_trgm_ops);
  
  CREATE FUNCTION search_combined_text(search text)
  RETURNS SETOF table_a AS $$
      WITH summary AS (
      SELECT
          a.*,
          GREATEST(
              SIMILARITY(concat_ws(' ', a.text_a, b.text_b), search),
              SIMILARITY(a.text_a, search) * 1.5
              ) as sml,
          ROW_NUMBER() OVER(PARTITION BY a.id) AS rk
      FROM table_a AS a
      INNER JOIN table_b AS b ON a.id = b.table_a_id
      WHERE (
          SIMILARITY(concat_ws(' ', a.text_a, b.text_b), search) &gt; 0.3 OR
          SIMILARITY(a.text_a, search) * 1.5 &gt; 0.3
          )
      ORDER BY sml DESC
      )
      SELECT s.id, s.text_a -- other fields..., except s.sml and s.rk
      FROM summary s WHERE s.rk = 1;
  $$ LANGUAGE sql STABLE;
  </code></pre>