Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1elafY-00041S-3P for pgsql-performance@arkaria.postgresql.org; Tue, 13 Feb 2018 13:30:48 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1elafW-0006It-Qy for pgsql-performance@arkaria.postgresql.org; Tue, 13 Feb 2018 13:30:46 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1elafW-0006Ik-GQ for pgsql-performance@lists.postgresql.org; Tue, 13 Feb 2018 13:30:46 +0000 Received: from w1.tutanota.de ([81.3.6.162]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1elafM-0005Wp-Uu for pgsql-performance@lists.postgresql.org; Tue, 13 Feb 2018 13:30:46 +0000 Received: from localhost (unknown [127.0.0.1]) by w1.tutanota.de (Postfix) with ESMTP id B4F99FB99B4 for ; Tue, 13 Feb 2018 13:30:34 +0000 (UTC) Received: from w1.tutanota.de ([127.0.0.1]) by localhost (w1.tutanota.de [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id NwWSJvvWuLc5 for ; Tue, 13 Feb 2018 14:30:32 +0100 (CET) Received: from w1.tutanota.de (unknown [192.168.1.162]) by w1.tutanota.de (Postfix) with ESMTP id 78B34FB99C6 for ; Tue, 13 Feb 2018 13:28:07 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=keemail.me; s=20161216; t=1518528487; bh=2MZGyKxAAmKtrzA90+gqP6vC/lKYWog9vNj6OjfNuDM=; h=Date:From:To:Subject:From; b=uJRTRtto02jGZBaEmQD3hmURxHPiMLwying5y7Giaq01peVwqm1ulZxMlVUzVOscB qreiRdq6T3TG1W0uKXOZ+RREHzeo9nK5SngQRZW6U6naFnJ8miB0cly9Mcj9oktARf IuJcAIAINQyFfNSA4vFoi0RDbhjSUHE96Uaj7gOok3Z210h2pDI1NyVtynIZX4WXqG a7YsWIPtrlUvCsi2M4WvtmRj8NG5p2cWcJ3YuM3w4tHUI/gToRD95Kj/uD7os/MWAn FgskZumvbOOpPgrjCS5Ry5xI8WIYMU/xywe9Zvf96QeO5Eh4jPqlF1p6bowAWspdKi hKxysjC2SvDog== Date: Tue, 13 Feb 2018 14:28:07 +0100 (CET) From: To: Message-ID: Subject: Efficiently searching for the most recent rows where a column matches any result from a different query MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_73759_34898250.1518528487473" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_73759_34898250.1518528487473 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hello, I have the following schema: =C2=A0 =C2=A0 CREATE TABLE users (=C2=A0 =C2=A0 =C2=A0 =C2=A0 id=C2=A0 =C2= =A0BIGSERIAL PRIMARY KEY,=C2=A0 =C2=A0 =C2=A0 =C2=A0 name TEXT=C2=A0 =C2=A0= =C2=A0 NOT NULL UNIQUE=C2=A0 =C2=A0 );=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0 CRE= ATE TABLE friends (=C2=A0 =C2=A0 =C2=A0 =C2=A0 user_id=C2=A0 =C2=A0 =C2=A0 = =C2=A0 BIGINT NOT NULL REFERENCES users,=C2=A0 =C2=A0 =C2=A0 =C2=A0 friend_= user_id BIGINT NOT NULL REFERENCES users,=C2=A0 =C2=A0 =C2=A0 =C2=A0 UNIQUE= (user_id, friend_user_id)=C2=A0 =C2=A0 );=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0 = CREATE TABLE posts (=C2=A0 =C2=A0 =C2=A0 =C2=A0 id=C2=A0 =C2=A0 =C2=A0 BIGS= ERIAL PRIMARY KEY,=C2=A0 =C2=A0 =C2=A0 =C2=A0 user_id BIGINT=C2=A0 =C2=A0 N= OT NULL REFERENCES users,=C2=A0 =C2=A0 =C2=A0 =C2=A0 content TEXT=C2=A0 =C2= =A0 =C2=A0 NOT NULL=C2=A0 =C2=A0 );=C2=A0 =C2=A0 CREATE INDEX posts_user_id= _id_index ON posts(user_id, id); Each user can unilaterally follow any number of friends. The posts table ha= s a large number of rows and is rapidly growing. My goal is to retrieve the 10 most recent posts of a user's friends. This q= uery gives the correct result, but is inefficient: =C2=A0 =C2=A0 SELECT posts.id, users.name, posts.content=C2=A0 =C2=A0 FROM = posts JOIN users ON posts.user_id =3D users.id=C2=A0 =C2=A0 WHERE posts.use= r_id IN (SELECT friend_user_id FROM friends WHERE user_id =3D 1)=C2=A0 =C2= =A0 ORDER BY posts.id DESC LIMIT 10; If the user's friends have recently posted, the query is still reasonably f= ast (https://explain.depesz.com/s/6ykR). But if the user's friends haven't = recently posted or the user has no friends, it quickly deteriorates (https:= //explain.depesz.com/s/OnoG). If I match only a single post author (e.g. WHERE posts.user_id =3D 5), Post= gres uses the index posts_user_id_id_index. But if I use IN, the index does= n't appear to be used at all. How can I get these results more efficiently? I've uploaded the schema and the queries I've tried to dbfiddle at http://d= bfiddle.uk/?rdbms=3Dpostgres_9.6&fiddle=3Dcf1489b7f6d53c3fe0b55ed7ccbad1f0.= The output of "SELECT version()" is "PostgreSQL 9.6.5 on x86_64-pc-linux-g= nu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit" for me. Thank you in advance for any insights, pointers or suggestions you are able= to give me. Regards,Milo ------=_Part_73759_34898250.1518528487473 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hello,

I have the following schema:

    CREATE TABLE users (
  &nb= sp;     id   BIGSERIAL PRIMARY KEY,
  &n= bsp;     name TEXT      NOT NULL UNIQUE
=     );
    
    CREA= TE TABLE friends (
        user_id  &nbs= p;     BIGINT NOT NULL REFERENCES users,
    =     friend_user_id BIGINT NOT NULL REFERENCES users,
&n= bsp;       UNIQUE (user_id, friend_user_id)
 =   );
    
    CREATE TAB= LE posts (
        id      BIG= SERIAL PRIMARY KEY,
        user_id BIGINT&nb= sp;   NOT NULL REFERENCES users,
       = content TEXT      NOT NULL
    );
=
    CREATE INDEX posts_user_id_id_index ON posts(user_id, id= );

Each user can unilaterally follow any number = of friends. The posts table has a large number of rows and is rapidly growi= ng.

My goal is to retrieve the 10 most recent po= sts of a user's friends. This query gives the correct result, but is ineffi= cient:

    SELECT posts.id, users.name= , posts.content
    FROM posts JOIN users ON posts.user= _id =3D users.id
    WHERE posts.user_id IN (SELECT fri= end_user_id FROM friends WHERE user_id =3D 1)
    ORDER= BY posts.id DESC LIMIT 10;

If the user's friend= s have recently posted, the query is still reasonably fast (https://explain.depesz.com/s/OnoG).

<= div>If I match only a single post author (e.g. WHERE posts.user_id =3D 5), = Postgres uses the index posts_user_id_id_index. But if I use IN, the index = doesn't appear to be used at all.

How can I get = these results more efficiently?

I've uploaded th= e schema and the queries I've tried to dbfiddle at http://dbfiddle.uk/?rdbms=3Dp= ostgres_9.6&fiddle=3Dcf1489b7f6d53c3fe0b55ed7ccbad1f0. The output o= f "SELECT version()" is "PostgreSQL 9.6.5 on x86_64-pc-linux= -gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit" for me.

Thank you in advance for any insights, pointers or sugg= estions you are able to give me.

Regards,
<= div>Milo ------=_Part_73759_34898250.1518528487473--