title: MySQL vs PostgreSQL performance with a complex query matching patterns
body: <p>I have a complex database, with around 30 tables. One table has more than 500,000 rows and another more than 15,000 and I use both in a separate database until today I decided to implement in only one database.</p>
  
  <p>Before today, the table with 500,000 rows was in a MySQL database and the 15,000 row table was in PostgreSQL. In one page of heavy use, this was the result in a PHP benchmark:</p>
  
  <pre><code>getSimilarAvaiable - 0.0287 s
  getUnavaiable - 0.27 s
  ProcessDataOfUnavaiable - 1.4701 s
  Process - 1.8622 s
  TotalPageTime - 3.631 s
  </code></pre>
  
  <p>After I migrate everything to PostgreSQL, and use <strong>the same SQL code</strong> without any changes the result of the same page was this:</p>
  
  <pre><code>getSimilarAvaiable - 2.7465 s
  getUnavaiableCars - 9.0763 s
  ProcesseDataOfUnavaiableCars - 1.4167 s
  ProcessCars - 1.7207 s
  TotalPageTime - 14.9602 s
  </code></pre>
  
  <p>I put everything the same in MySQL, same index, everything, but I can't understand why there is this huge difference.  What I should do to optimize this?</p>
  
  <p><strong>EDIT</strong>: Now better explained.</p>
  
  <p>The 500.00 table is composed with the follow structure: </p>
  
  <pre><code>id - bigint (primary key)
  plate- varchar(10) Unique key
  manufacturer - varchar(30)
  vin - varchar(30)
  </code></pre>
  
  <p>The major query is something like this:</p>
  
  <pre><code>SELECT plate, vin, 1 as n, substr(plate,1,2) as l 
  FROM imtt_vin WHERE substr(plate,1,1) &gt;= 'A' and substr(plate,1,1) &lt;= 'Z' AND
  (manufacturer ILIKE '%".self::$Manufacturer."%') AND vin LIKE ?
  UNION
  SELECT plate, vin, 3 as n, substr(plate,4,2) as l 
  FROM imtt_vin WHERE substr(plate,4,1) &gt;= 'A' and substr(plate,4,1) &lt;= 'Z' AND
  (manufacturer ILIKE '%".self::$Manufacturer."%') AND vin LIKE ?
  UNION
  SELECT plate, vin, 2 as n, substr(plate,7,2) as l 
  FROM imtt_vin WHERE substr(plate,7,1) &gt;= 'A' and substr(plate,7,1) &lt;= 'Z' AND 
  (manufacturer ILIKE '%".self::$Manufacturer."%') AND vin LIKE ?
  ORDER BY n, l, plate;
  </code></pre>
  
  <p>EDIT2: Tested with a complex single query and I reduced it from 15 to 8/9 seconds. Even so it is too much for me.</p>