Good databases for unanchored LIKE condition Basically I need a database that is good with queries like LIKE %abc%.

I already tried PostgreSQL with GIN indexes and it is very good, but maybe there is something even better? I also tried MongoDB and found out that the query like "/abc/" works really badly, and Mongo indexes support only "/^abc/".

My database structure is very simple. 

Example query in PostgreSQL:

SELECT DISTINCT(id), title FROM data AS data
INNER JOIN datatosynonym AS dts ON dts.data_id = data.id
WHERE dts.synonym_simple LIKE "%abc%"


And in MongoDB

db.data.find({synonymssimple: /abc/})


Where synonymssimple is an array of strings. 

Example data in PostgreSQL 

data table:

id | title | timestamp
 1 |  Abc  | 1145836800
 2 |  Qwe  | 1145836800


datatosynonym table:

id | synonym_simple | data_id
 1 |       abc      |   1
 2 |       bac      |   1


My benchmarks show the following results:


PostgreSQL with b-tree indexes and %abc% query - ~15ms per query
PostgreSQL with b-tree indexes and abc% query - ~1ms per query
PostgreSQL with GIN indexes and %abc% query - ~1.5ms per query
PostgreSQL with GIN indexes and abc% query - ~1ms per query
MongoDB without indexes and /abc/ query - ~25ms per query
MongoDB with b-tree indexes and /abc/ query - ~80ms per query
MongoDB with b-tree indexes and /^abc/ query - ~0.25ms per query


Sadly I can't use /^abc/ query.
One technique is to split the "unanchored" query into two "anchored" parts.

As you've shown a B-Tree search with a trailing wildcard is fast. The problem is you also need a leading wildcard. If you can turn the leading wildcard into a trailing one it would be good. The REVERSE function will help here. The query becomes

WHERE dts.synonym_simple LIKE "abc%"
AND dts.synonym_simple_reversed LIKE "cba%"


To be effective there must be an index on the reversed text. This is an overhead at write-time and will use additional storage. Waiting until read-time would require a scan of the data, which rather defeats the purpose. See examples here amongst others.