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:

  1. PostgreSQL with b-tree indexes and %abc% query - ~15ms per query
  2. PostgreSQL with b-tree indexes and abc% query - ~1ms per query
  3. PostgreSQL with GIN indexes and %abc% query - ~1.5ms per query
  4. PostgreSQL with GIN indexes and abc% query - ~1ms per query
  5. MongoDB without indexes and /abc/ query - ~25ms per query
  6. MongoDB with b-tree indexes and /abc/ query - ~80ms per query
  7. MongoDB with b-tree indexes and /^abc/ query - ~0.25ms per query

Sadly I can't use /^abc/ query.

  • 1
    I assume your GIST/GIN was using Trigram? – Evan Carroll Jun 14 '17 at 1:35
  • 1
    I want to see the actual dataset. With text_pattern_ops and PostgreSQL you should be able to do abc% the same as ^abc, and get get faster performance than with GIN. If gin is getting you 1 ms. I'd assume sub-ms. – Evan Carroll Jun 14 '17 at 1:37
  • @Evan Carroll yes I was using trigram. – Davinel Jun 14 '17 at 6:09
  • @EvanCarroll I dont really need abc% though. I need %abc%. That is why I am using GIN. Also I will add example data now. – Davinel Jun 14 '17 at 6:19
  • @ypercubeᵀᴹ It was 4 times faster than postgresql with GIN indexes. Tested table had around 100k rows. – Davinel Jun 14 '17 at 15:46

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.

  • Very interesting idea, I will try it! – Davinel Jun 15 '17 at 15:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.