title: Optimise PostgreSQL multi-table search query
body: <p>I have a website with a multi-table search tool, displaying the results with a pagination.</p>
  
  <p>The PostgreSQL tables have tens of thousands rows, and I'd like to improve the performance : if I search for a word (or several), it takes a few seconds, but if I search a single letter (for example "e"), it takes around 20 seconds.</p>
  
  <p>Here is a very simplified database for example purpose:</p>
  
  <pre><code>create table DOG
  (
      id_dog SERIAL PRIMARY KEY,
      name VARCHAR(100) UNIQUE NOT NULL,
      birth_date TIMESTAMP NULL
  );
  
  create table CAT
  (
      id_cat SERIAL PRIMARY KEY,
      name VARCHAR(100) UNIQUE NOT NULL,
      birth_date TIMESTAMP NULL
  );
  
  create table BIRD
  (
      id_bird SERIAL PRIMARY KEY,
      name VARCHAR(100) UNIQUE NOT NULL,
      birth_date TIMESTAMP NULL
  );
  
  insert into DOG values (0, 'Rex', '2002-03-05 12:02:06');
  insert into DOG values (1, 'Rantanplan', '2002-03-05 12:02:06');
  insert into DOG values (2, 'Milou', '2002-03-05 12:02:06');
  
  insert into CAT values (0, 'Grominet', '2002-03-05 12:02:06');
  insert into CAT values (1, 'Bella', '2002-03-05 12:02:06');
  insert into CAT values (2, 'Molly', '2002-03-05 12:02:06');
  
  insert into BIRD values (0, 'Titi', '2002-03-05 12:02:06');
  insert into BIRD values (1, 'Bip-Bip', '2002-03-05 12:02:06');
  insert into BIRD values (2, 'Sunny', '2002-03-05 12:02:06');
  </code></pre>
  
  <p>And here is the corresponding simplified request (in the real case I search in more fields and the tables are not that similar) :</p>
  
  <pre><code>select *, count(*) over() as count 
  from 
  (
      (
          select id_dog as id, birth_date as date, name as title, varchar(4) 'DOG' as table
          from DOG d 
          where unaccent(lower(array_to_string(array[d.name, to_char(d.birth_date, 'YYYY-MM-DD HH24:MI:SS')], ' ')))
          like '%' || lower(unaccent('a')) || '%' 
      ) 
  
      union 
      (
          select id_cat as id, birth_date as date, name as title, varchar(4) 'CAT' as table
          from CAT c
          where unaccent(lower(array_to_string(array[c.name, to_char(c.birth_date, 'YYYY-MM-DD HH24:MI:SS')], ' '))) 
          like '%' || lower(unaccent('a')) || '%' 
      ) 
      union 
      (
          select id_bird as id, birth_date as date, name as title, varchar(4) 'BIRD' as table
          from BIRD b
          where unaccent(lower(array_to_string(array[b.name, to_char(b.birth_date, 'YYYY-MM-DD HH24:MI:SS')], ' '))) 
          like '%' || lower(unaccent('a')) || '%' 
      )
  ) 
  as results order by DATE offset 0 limit 10;
  </code></pre>
  
  <p>Any idea to improve this? Maybe with a left join?</p>
  
  <p>The problem seems to be that I need to count the number of results in order to display my pagination. If I limit the number of results in each sub-select the query runs fast, but I don't get the right results count.</p>