MySQL vs PostgreSQL performance with a complex query matching patterns

MySQL uses more memory by default. I think it is assigned to use more than 256MB by def install. Not sure on the exact number. PostgreSQL by default is set to use something like 32MB. Try to bump each one up to 1GB of ram in config file then run benchmarks and get back to us.

You need to post EXPLAIN yourquery (for mysql) and EXPLAIN ANALYZE yourquery (for postgres) ; without that it's impossible to say anything relevant.

Also SELECT pg_relation_size('imtt_vin')

For instance what is the value of "?" in this query ?

SELECT plate, vin, 1 as n, substr(plate,1,2) as l 
FROM imtt_vin WHERE substr(plate,1,1) >= 'A' and substr(plate,1,1) <= 'Z' AND
(manufacturer ILIKE '%".self::$Manufacturer."%') AND vin LIKE ?

I don't know about license plates where you work but this part :

WHERE substr(plate,1,1) >= 'A' and substr(plate,1,1) <= 'Z'

probably selects all rows in the database, thus its only purpose is to burn CPU cycles. You could at least rewrite it (and all the others) like this to avoid a call to substr() :

WHERE substr(plate,1,1) BETWEEN 'A' AND 'Z'

And of course remove the condition when it is not useful.

Then we have :

manufacturer ILIKE '%".self::$Manufacturer."%'

Bad database design : are there 500.000 car manufacturers in the world ? Probably not. You should put the manufacturers in another table and use a foreign key. That would turn this unindexable condition into an indexable one.

For the rest, post EXPLAIN / EXPLAIN ANALYZE.

Seems to me that you likely have not updated the statistics on the Postgres database. With improper statistics, the database will not perform very well.

I took your first query, reformatted and slightly modified it to:

SELECT plate, vin, 1 as n, left(plate,2) as l 
FROM   imtt_vin
WHERE  left(plate, 1) >= 'A'  -- left() is simpler than substr()
AND    left(plate, 1) <= 'Z'
AND    (lower(manufacturer) LIKE '%".self::$manufacturer."%')  -- lower case!
AND    vin LIKE ?

Bold emphasis not because it is very important, just to point out the change.

A B-tree index cannot be used for arbitrary (non-left-anchored) LIKE expressions. But a trigram GiST index can. You need the additional module pg_trgm. Install once per database with CREATE EXTENSION in PostgreSQL 9.1 or later. Consult the manual for older versions.

CREATE EXTENSION pg_trgm;

I don't have much information to go on, but something like this partial multi-column GiST index on an expression should work wonders:

CREATE INDEX imtt_vin_partial_gist_idx
ON     imtt_vin
USING  gist (lower(manufacturer) gist_trgm_ops, vin gist_trgm_ops)
WHERE  left(plate, 1) >= 'A'
AND    left(plate, 1) <= 'Z';

• Conditions in the query have to match the conditions of the partial index, so the planner can use it.
• Be sure to provide the pattern for manufacturer in your query in lower case.
• Trigram index only helps with patterns of three or more characters.
• Test with EXPLAIN ANALYZE whether the index is actually used. If it is, query time should be a matter of milliseconds, not seconds.
• Create similar indexes for your other queries. Only the WHERE condition varies.
• The speed comes at a minor cost for write operations for index maintenance.
• You can't do any of this with MySQL.

  ---

Also, change UNION to UNION ALL. UNION would be used to eliminate duplicates, which is obviously not the case here.

You still haven't provided enough information -- what indexes do you have, EXPLAIN ANALYZE output for slow queries, etc.

Some thoughts on optimizing your example query:

1: UTF-8 string functions are generally not very fast. If you want to speed up string functions, use the bytea type instead of varchar for this column (or change your whole database encoding to SQL_ASCII, but this is unadvisable)

2: Given your queries, the database probably has to go through all rows in the table and compute these string functions for each.

I don't know how many matches they have, so the index might not be useful, but functional indexes might help you out:

 CREATE INDEX imtt_vin_plate_1 ON imtt_vin (substr(plate,1,1));
 CREATE INDEX imtt_vin_plate_4 ON imtt_vin (substr(plate,4,1));
 CREATE INDEX imtt_vin_plate_7 ON imtt_vin (substr(plate,7,1));

3: If you can tolerate duplicate outputs, use UNION ALL instead of UNION in your queries -- this will save you some processing with larger result sets.

4: Avoid LIKE/ILIKE whenever you can you can.