Jonathan Vanasco: Thank you. That's a good idea but in this case, I'm really wondering if sqlalchemy should use that small change to improve performance quite a bit in this type of queries. Otherwise, I think I would need to rely on raw SQL to perform a similarity operation and I'm not sure if `session.execute` provides the same checks to prevent SQL injections (which is my main reason to use sqlalchemy in the first place).
Regards On Thursday, March 31, 2016 at 1:43:50 PM UTC-6, Jonathan Vanasco wrote: > > > > On Thursday, March 31, 2016 at 9:43:11 AM UTC-4, Mike Bayer wrote: >> >> this is more of a Postgresql optimization question so I dont have any >> insight on that. >> > > FWIW, I generally handle these types of "optimize postgres" queries using > a function to apply the filter - like this: > > def filter_Table_field_a(query, value, optimize=False): > """Table.field_a has a partial index on a substring of field_a > it will only be consulted by the planner if used in a query.""" > query = query.filter(model.core.Table.field_a = value, ) > if optimize: > query = query.filter(func.substr(model.core.Table.field_a, 0, > 5)) == value[:4], ) > return query > > > def filter_Table_field_b(query, value, optimize=False): > """Table.field_b has a uniqueindex on a lower(field_b); > it will only be consulted by the planner if used in a query.""" > query = query.filter(model.core.Table.field_b = value, ) > if optimize: > query = query.filter(func.lower(model.core.Table.field_b)) == > value.lower(), ) > return query > > q = s.query(TableA) > q = filter_Table_field_b(q, 'hello', optimize=True) > > I toggle the optimization, because some queries will perform better with > it off. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
