@mike, a real thank you for your patience. I'm sorry I was lost for a
while, I understood what you said and it's what I'm doing now. Thanks for
the tips and sorry for your time
Le jeu. 21 mars 2019 à 17:17, Scheck David <da...@sphax.it> a écrit :
> 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
>
--
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.
