Re: Slow query when pg_trgm is in inner lopp

On Wed, Jun 20, 2018 at 9:21 AM, Sasa Vilic <sasavilic(at)gmail(dot)com> wrote:

> Query that we have finds all routes between two set of points. A set is a
> dynamically/loosely defined by pattern given by the user input. So for
> example
> if user wants to find all routes between international airports in Austria
> toward London Heathrow, he or she would use 'LOW%' as
> :from_point_identifier
> and 'EGLL' as :to_point_identifier. Please keep in mind that is a simple
> case,
> and that user is allowed to define search term any way he/she see it fit,
> i.e. '%OW%', 'EG%'.
>

Letting users do substring searches on airport codes in the middle of a
complex query makes no sense. Do all airports with 'OW' in the middle of
them having something in common with each other? If people can't remember
the real airport code of the airport they are using, you should offer a
look-up tool which they can use to figure that out **before** hitting the
main query.

But taking for granted your weird use case, the most obvious improvement to
the PostgreSQL code that I can see is in the executor, not the planner.
There is no reason to recompute the bitmap on idx_point_08 each time
through the nested loop, as the outcome of that scan doesn't depend on the
outer tuple. Presumably the reason this happens is that it is being
'BitmapAnd'ed with another bitmap index scan which does depend on the outer
tuple, and it is just not smart enough to reuse the stable bitmap while
recomputing the parameterized one.

Cheers,

Jeff