On Jun 5, 2014, at 7:14 PM, Jonathan Vanasco <jonat...@findmeon.com> wrote:
> 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? yeah we have that, use SQL level processing: http://docs.sqlalchemy.org/en/rel_0_9/core/types.html#applying-sql-level-bind-result-processing, though for the expression on the column comparison you might also have to use column_property() or hybrids on the mapper side. -- 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.
