Mike Bayer: Thank you for your response. I wasn't really asking whether the optimization I described above (using `%` instead of `similarity`) is correct or not. Based on some resources (e.g. Super Fuzzy Searching on PostgreSQL <http://webcache.googleusercontent.com/search?q=cache:59hB5zYwRzcJ:bartlettpublishing.com/site/bartpub/blog/3/entry/350+&cd=9&hl=en&ct=clnk&gl=mx> , FUZZY STRING MATCHING WITH TRIGRAM AND TRIGRAPHS <http://www.postgresonline.com/journal/archives/169-Fuzzy-string-matching-with-Trigram-and-Trigraphs.html>), I think it is correct although I wouldn't mind being proven wrong. I was mainly asking, why sqlalchemy doesn't seem to take advantage of this improvement?
Your second comment intrigued me: `though this is not really important in the case of automapping as indexes do not affect ORM mappings.`. I'm pretty new to sqlalchemy, so I think I don't understand what you mean. Can you describe this a bit more? Regards On Thursday, March 31, 2016 at 7:43:11 AM UTC-6, Mike Bayer wrote: > > > > On 03/30/2016 09:50 PM, Robert Smith wrote: > > I'm using sqlalchemy 1.0.12 with postgreSQL 9.4.6 with the pg_trgm > > extension enabled. Basically, I'm performing a similarity-based query on > > a single column: > > > > In [26]: > > > str(session.query(Model).order_by(desc(func.similarity(Model.description, > 'string'))).limit(100)) > > Out[26]: 'SELECT model.id AS model_id, model.description AS > > model_description, \nFROM model ORDER BY similarity(model.description, > > :similarity_1) DESC\n LIMIT :param_1' > > > > This query works okay, but I'm worried that looking at the query that is > > being used, it doesn't seem to take advantage of some performance > > improvements that are available in this kind of situation. As a simple > > example, I noticed that people recommend not to use `similarity` and > > instead rely on `%` or alternatively, use `WHERE model.description % > > 'string' AND similarity(model.description, 'string') > 0.5`. This is > > apparently related to the efficient use of a "gist" index. > > this is more of a Postgresql optimization question so I dont have any > insight on that. > > > > > Another question: In the case of having an index for the trigram > > matches, does sqlalchemy pick up that information automatically when I'm > > using `automap_base()` to reflect my database? > > Table reflection will reflect table indexes, though this is not really > important in the case of automapping as indexes do not affect ORM > mappings. > > > > -- 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.
