AW: CTE with JOIN of two tables is much faster than a regular query

> -----Ursprüngliche Nachricht-----
> Von: Stephen Frost <sfrost(at)snowman(dot)net>
> 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