Unbelievable that this is even getting upvotes. Anyone with a basic understanding of relational databases could see that nested o(n) ilike filter was the problem. I kept reading in the hopes that maybe something actually interesting was going on, besides ineptitude. Disappointing.
When you have a huge list like that in a filter, the answer is to treat it like a table and perform a join. Then the query planner can optimize the ordering of the loops. But this is just mind-boggling ignorance dressed up as a "debugging horror story".
This is a well-written article in which the author lays out their thought process for optimizing a slow SQL query. The article certainly provides more value to someone who may not be an RDBMS expert than your comment does.
When you have a huge list like that in a filter, the answer is to treat it like a table and perform a join. Then the query planner can optimize the ordering of the loops. But this is just mind-boggling ignorance dressed up as a "debugging horror story".