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
