Apologies, I can't think of a better way to phrase this. This doesn't seem
to fall into the typical CustomType or Augmenting A Column concepts.
I'm not sure how/if SqlAlchemy can do this
Here is my situation-
I have a handful of tables in PostgreSQL where tables contain columns that
have ancillary "keys" that are a 32-64 character hash. Basically public
facing UIDs.
The columns have their own index, which speeds things up a lot... but still
they can be not-as-great.
I did some searching online to better optimize it, and I found a decent
trick on the Instagram engineering blog -- using a partial index on the
column
create index speedy_idx_foo_bar_8 on foo(substr(bar,0,8);
this index uses a fraction of the diskspace, and tends to perform the same,
if not better, than a full index search.
there's a caveat though... in order to use the index, queries have use the
substing function -- otherwise the planner won't care to use it.
- select * from foo where bar = %(bar)s ;
+ select * from foo where bar = %(bar)s and substr(bar,0,8) =
%(bar_substring)s ;
+ select * from foo where bar = %(bar)s and substr(bar,0,8) =
substr(%(bar_substring)s,0,8) ;
- select * from foo where bar in ( %(bar_1)s , %(bar_2)s );
+ select * from foo where bar in ( %(bar_1)s , %(bar_2)s ) AND
subst(bar,0,8) in ( %(bar_substr_1)s , %(bar_substr_2)s );
+ select * from foo where bar in ( %(bar_1)s , %(bar_2)s ) AND
subst(bar,0,8) in ( substr(%(bar_1)s,0,8) , substr(%(bar_2)s,0,8) );
I'm doing all this manually now -- which isn't a big deal ; but I'm
wondering if there's a better way to handle this within SQLalchemy. That
would hopefully let me migrate this type of stuff into the 'model' and I'd
be able to handle joins/etc as well.
has anyone out there tried to do stuff like this before?
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
