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.
