just to confirm, run this inside of any execute() callable, eg. in alembic:
op.execute("CREATE INDEX img_createdby_uri_index ON image ((image_metadata -> 'systemfields' -> 'created_by' ->> 'uri'))") with an engine with engine.connect() as conn: conn.execute("CREATE INDEX img_createdby_uri_index ON image ((image_metadata -> 'systemfields' -> 'created_by' ->> 'uri'))") On Thu, Mar 21, 2019 at 12:17 PM Scheck David <da...@sphax.it> wrote: > 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. > -- 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.