On Wed, Jun 20, 2018 at 9:21 AM, Sasa Vilic <sasavilic@xxxxxxxxx> wrote:
Query that we have finds all routes between two set of points. A set is adynamically/loosely defined by pattern given by the user input. So for exampleif user wants to find all routes between international airports in Austriatoward London Heathrow, he or she would use 'LOW%' as :from_point_identifierand '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
