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 [email protected].
To post to this group, send email to [email protected].
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.
