CREATE INDEX img_createdby_uri_index ON image ((image_metadata -> 'systemfields' -> 'created_by' ->> 'uri'));
that's what I want. thank you Le jeu. 21 mars 2019 à 14:25, Mike Bayer <mike...@zzzcomputing.com> a écrit : > (noting again, you need the CREATE INDEX statement that PG wants. > SQLAlchemy part is only a bonus which isn't strictly needed here). > > On Thu, Mar 21, 2019 at 9:24 AM Mike Bayer <mike...@zzzcomputing.com> > wrote: > >> 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. > -- 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.