> -----Ursprüngliche Nachricht----- > Von: Stephen Frost <sfrost@xxxxxxxxxxx> > Gesendet: Samstag, 18. August 2018 16:39 Hello, > > > What can I do to improve the performance of the regular query without > > using a CTE? > > You could possibly build a trigram index on the field you're searching, which > could avoid the full table scan. Of course, that index could be quite large, so > there's downsides to that. If these are words you're looking for then you > could use PG's full text indexing to build indexes on the words and then use > that instead. If you are fine working with words but are concerned about > misspellings then you can extract out the distinct words, build a trigram index > on those, find the most similar words based on the input and then search for > those words using the FTI. > > Unfortunately, we don't currently pay attention to things like average string > length when considering the cost of performing an 'ilike', so we figure that > doing the filtering first and then the join will be faster, but that obviously falls > over in some cases, like this one. Using the CTE forces PG to (today, at least) > do the join first, but that isn't really good to rely on. A trigram index would be a possible help in this particular scenario but size and updating the index in other parts of the application would be probably create other issues. I may try it, though. But thanks to confirming my assumption. I just thought that it should be obvious to the optimizer to do the join first and filter on this result. But I'm reading you r post that there is nothing that I can do to modify the behavior of the optimizer. Or is there a way to specify the cost for an operator (ILIKE in this case) on a specific column? Thanks Klaus
