1

I have a SQL Server database with 4.5 million records. I reproduced the same database with the exact same 4.5 million records in a PostgreSQL database.

When querying the SQL Server database, I am using FTS which returns data sub 1 second which is great. The problem is, when using the Postgresql without FTS, it is obviously extremely slow (25 - 45 seconds if not longer). I am struggling to find out what is the best PostgreSQL FTS equivalent?

API is .net core c# using Entity framework core.

Data is a list of companies. So a typical search might 'Kelly Construction' and I'd expect the API / DB to return results where company name column contains or is 'like' 'kelly construction'.

Improve this question
3
  • 1
    Postgres supports FTS as well. But a seq scan on a table with only 5 million rows shouldn't take 45 seconds on modern hardware.
    – user1822
    Sep 8, 2019 at 20:03
  • Postgres has GIN and GIST indexes (and trigram) which are useful for various types of string searches.
    – ypercubeᵀᴹ
    Sep 8, 2019 at 20:08
  • I closed this and provided links to 2 similar questions. If the answers there are not helpful enough for your issue, please make a comment here and we could reopen.
    – ypercubeᵀᴹ
    Sep 8, 2019 at 20:15

0

Reset to default

Browse other questions tagged or ask your own question.