title: COUNT with JOIN and WHERE clause - very slow query
body: <p>The queries on the website I am working on are pretty slow (there are only about 300k records in the database and some queries take more than 30 seconds), so I wanted to find the problem and decided to do some performance tests with some simple queries, here you will find 3 different scenarios:</p>
  <pre><code> SELECT count(*) FROM offer_test
   INNER JOIN searchmod_location_city
      ON (offer_test.city_id = searchmod_location_city.id)
  
   SELECT count(*) FROM offer_test
   WHERE price &gt; 500000
  
   SELECT count(*) FROM offer_test
   INNER JOIN searchmod_location_city
      ON (offer_test.city_id = searchmod_location_city.id)
   WHERE price &gt; 500000
  </code></pre>
  <p>The first select takes about 0.2s, second one about 0.06s, and the third one which is simply combination of two previous selects takes about 20 seconds, this seems very strange to me, I would expect it to take no longer than sum of first and second query. Of course I don't really need the JOIN to count offers in the last scenario (price column is in offer_test table), but it is just for test purposes and understading this behaviour will help me with optimization of similar queries in the system (more JOINS and more conditions in the WHERE clause).</p>
  <p>(There are of course indexes on price column, and on foreign_keys columns).</p>
  <p>Can someone explain to me why the last query takes so long and what to do about it?</p>
  <p>Below you will find explain for the last query
  <a href="https://i.stack.imgur.com/KwbZS.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/KwbZS.png" alt="enter image description here" /></a></p>