Le 07/07/2019 à 16:33, Thomas Kellerer a écrit :
> Omar Roth schrieb am 07.07.2019 um 15:43:
>> Currently, the query I'm using to generate a user's feed is:
>>
>> ```
>> SELECT * FROM channel_videos WHERE ucid IN (SELECT
>> unnest(subscriptions) FROM
>> users WHERE email = $1) ORDER BY published DESC;
>> ```
>
> You could try an EXISTS query without unnest:
>
> select cv.*
> from channel_videos cv
> where exists ucid (select *
> from users u
> where cv.ucid = any(u.subscriptions)
> and u.email = $1);
>
> Did you try if a properly normalized model performs better?
>
>
Hi
We had big performance issues with queries like that, and we modified
them to use && (see
https://www.postgresql.org/docs/current/functions-array.html ),
resulting in a big perf boost
so, with your model, the query could be
```
select cv.*
from channel_videos cv
inner join user u on cv.ucid && u.subscription
where u.email = $1;
```
or
```
select cv.*
from channel_videos cv
inner join ( select subscription from user where email = $1) as u on
cv.ucid && u.subscription ;
```
(disclaimer, I didn't try this queries, they may contain typos)
Regards
Nicolas