Re: Optimizing `WHERE x IN` query

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