can you go onto some Postgresql forums and ask there? this is a Postgresql-specific issue.
On Thu, Mar 21, 2019 at 8:44 AM Scheck David <da...@sphax.it> wrote: > In fact it's a function in postgresql which catch all urls and store it in > a field. but it's not what I'm searching for. I just have to create an > index gin and I still don't know how to. I'm just stuck because I don't > understand how to create index on a nested field. > > op.create_index('ix_law_search_vector', 'law', ['search_vector'], > unique=False, postgresql_using='gin') > > my model is like > Image > metadata = JSONB > > JSONB field : > { 'updated_by': {'uri': 'https://alink', 'description': 'Beeldbank'}}, > 'akey': [{'uri': 'alink'}], 'aanduidingsobjecten': [] > } > > And I need to optimise the search through all the keys "uri". > > because right now we do something like that : > > filter( > or_( > > Image.image_metadata['systemfields']['updated_by']['uri'].astext == uri, > Image.image_metadata.contains({'akey': [{'uri': uri}]}), > ImageState.actor_uri == uri > )) > > and it takes 70s on a database with 270000 records. and that's just not > possible. > > I've another query where it's 2 sec (I don't understand that) : > filter(Image.image_metadata.contains({'akey': [{'uri': uri}]})) > > > -- > > With kindest regards, > > > *David SCHECK* > > PRESIDENT/DEVELOPER > > [image: Signature Logo Sphax Bleu-01.png] > > Phone: +32 4 87 86 70 12 > Visit our website ! https://www.sphax.org > > -- > 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. > For more options, visit https://groups.google.com/d/optout. > -- 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. For more options, visit https://groups.google.com/d/optout.