On Friday, June 14, 2019 at 9:33:49 AM UTC-4, Mike Bayer wrote: > > docs for this general idea are at > https://docs.sqlalchemy.org/en/13/core/custom_types.html#redefining-and-creating-new-operators > > but this is likely a good example to add >
Mike- This is actually a great example to add, because in Postgres (and likely other databases) the query planner will only consult a function index if that function is used in the query. Sorry to sidetrack this for a moment, but can the TypeDecorator support multiple comparisons or would you recommend another SqlAlchemy internal? The use-case I am concerned with a table that has an index on a 32 char md5 value and function index that is a 6char substring of that md5 value. Every time I want to query: query.filter(Foo.md5 == md5) I have to write something like the following, so the query planner will use the function index: query.filter((Foo.md5 == md5, func.substring(Foo.md5, 0, 6) == md5[:6]) With a few billion records in a table, the difference is a few seconds of processing vs instant results. Right now I'm automating this with a function, but a TypeDecorator looks to be a much better option. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/75fff3a2-3e08-44b9-832a-1335443fea9f%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.