title: how to improve performance of a query with multi join, group by and where in postgresql?
body: <p>I have a query like this:</p>
  <pre><code>select c.title, count(b.role) as directors_num  
          from cast_info a
                  inner join role_type b on a.role_id = b.id
                  inner join movie     c on a.movie_id = c.id
  where 1=1
  and b.role like '%director%'
  and c.title not like '(%' -- if necessary
  group by c.title
  order by count(b.role) desc
  </code></pre>
  <p>I want to improve performance:
  I tried <strong>EXPLAIN ANALYSIS</strong> method to see the performance:
  here is the output:
  <a href="https://i.stack.imgur.com/gyjoi.png" rel="nofollow noreferrer">result 1</a>
  and
  <a href="https://i.stack.imgur.com/haDlv.png" rel="nofollow noreferrer">result 2</a></p>
  <p>I tried using indexing and improving database statistics, however I could not improve execution time, what would you suggest?</p>