0

How can I optimise, index or rewrite the following query so that it's fast? 

SELECT * FROM roads WHERE "some text oxford street blah blah" ILIKE "%"||road_name||"%"

Needless to say there are millions of road records and the query is taking ages. How can we better set this up?

Maybe I'm looking at this the wrong way?

Thanks

Improve this question
4
  • Have a look: stackoverflow.com/a/4553799/3270427 Commented Mar 9, 2017 at 18:31
  • Performance questions should include EXPLAIN ANALYZE and some information about table size, index, current time performance, desire time, etc. Slow is a relative term and we need a real value to compare. MySQL Also Please read How-to-Ask Commented Mar 10, 2017 at 5:56
  • Looks like a clear case for full text search. Commented Mar 10, 2017 at 8:12
  • Possible duplicate of Postgres reverse LIKE lookup indexing and performance Commented Mar 10, 2017 at 9:48
Related questions
1
postgres full text search
1
postgres text search
13
Indexing column for full text search
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.