Subject: Re: Optimizing `WHERE x IN` query
To: pgsql-performance@lists.postgresql.org
References: 
 <DM5PR18MB1051CD296F9F94AE0D67FD84DFF70@DM5PR18MB1051.namprd18.prod.outlook.com>
From: Thomas Kellerer <spam_eater@gmx.net>
Message-ID: <86b8a755-2fa4-f7c2-8b0b-3290773feb97@gmx.net>
Date: Sun, 7 Jul 2019 16:33:00 +0200
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; de; rv:1.8.1.21)
 Gecko/20090302 Thunderbird/2.0.0.21 Mnenhy/0.7.5.666
MIME-Version: 1.0
In-Reply-To: 
 <DM5PR18MB1051CD296F9F94AE0D67FD84DFF70@DM5PR18MB1051.namprd18.prod.outlook.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: de-DE
Content-Transfer-Encoding: quoted-printable
Precedence: bulk

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 =3D $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 =3D any(u.subscriptions)
                      and u.email =3D $1);

Did you try if a properly normalized model performs better?