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 1emIYX-0001Ud-P0 for pgsql-performance@arkaria.postgresql.org; Thu, 15 Feb 2018 12:22:30 +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 1emIYV-0003cZ-MD for pgsql-performance@arkaria.postgresql.org; Thu, 15 Feb 2018 12:22:27 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1emIYV-0003cI-80 for pgsql-performance@lists.postgresql.org; Thu, 15 Feb 2018 12:22:27 +0000 Received: from w1.tutanota.de ([81.3.6.162]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1emIUL-0006IM-Da for pgsql-performance@lists.postgresql.org; Thu, 15 Feb 2018 12:18:13 +0000 Received: from localhost (unknown [127.0.0.1]) by w1.tutanota.de (Postfix) with ESMTP id 733C8FB9B5F for ; Thu, 15 Feb 2018 12:18:05 +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 PQORBBzbbi7v for ; Thu, 15 Feb 2018 13:18:00 +0100 (CET) Received: from w1.tutanota.de (unknown [192.168.1.162]) by w1.tutanota.de (Postfix) with ESMTP id CA3F9FB9B5D for ; Thu, 15 Feb 2018 12:18:00 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=keemail.me; s=20161216; t=1518697080; bh=nNh6mc1/nItjX0zKD4YE5jtaEg/rXXVqUGAzcJ07h0s=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From; b=yi3RxMziAQTrd4v7CEmgFfpfvMZB7dAh4g1lOEXUATe2m6wZl1WEKJzomFR/fZ88F 79b8sf9Clt3XWIEBh5hvEQNceADIBOehh11YYwJ/F5byr3yP2zdFkHzD84Y2DOZOlj 0p4blntdzHzcRZQK/qgvqQZ+e/ujIqAzYHafJQJhq+DJ4UEaw6DcdgN20gUwGwJPpC ze1vyjoH15WHw2115Bs0Sj0IV4tn5M9Z9wBRj7GDhbI38EaSTg7Luj5YTZp0Fn62CV FMYvHj3NF4k6iu9GA8vTSi+D7vss/YFFSJdg8oI268gG3D87vCRpXUJg7D1NGUfyGk uxYpe1yP9q3Vw== Date: Thu, 15 Feb 2018 13:18:00 +0100 (CET) From: To: Hellmuth Vargas Cc: Pgsql Performance Message-ID: In-Reply-To: References: <> Subject: Re: 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_122368_1158520999.1518697080810" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_122368_1158520999.1518697080810 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hello Hellmuth, Thank you for your response. I've uploaded the query plan for the first query (user_id=3D2) here:=C2=A0h= ttps://gist.github.com/anonymous/6d251b277ef71f8977b03cab91fedccdThe query = plan for the second query (user_id=3D1) can be found here:=C2=A0https://gis= t.github.com/anonymous/32ed485b40cce2651ddc52661f3e7f7b Just like in the original queries, posts_user_id_id_index is not used. Kind regards,Milo 13. Feb 2018 22:13 by hivs77@gmail.com: > Hello: > > EXPLAIN (ANALYZE, BUFFERS)> select * from (> =09> SELECT > posts.id> , > = users.name> , posts.content> =09> FROM posts JOIN users ON posts.user_id = =3D > users.id> =09> WHERE posts.user_id IN (SELECT friend_user_id FROM fri= ends WHERE user_id =3D 1) > =09> ORDER BY > posts.id> DESC=C2=A0> ) as a> ORDER BY > a.id> DESC=C2= =A0> LIMIT 10; > ------ > > EXPLAIN (ANALYZE, BUFFERS)> select * from (> =09> SELECT > posts.id> , > = users.name> , posts.content> =09> FROM posts JOIN users ON posts.user_id = =3D > users.id> =09> WHERE posts.user_id IN (SELECT friend_user_id FROM fri= ends WHERE user_id =3D 2) > =09> ORDER BY > posts.id> DESC=C2=A0> ) as a> ORDER BY > a.id> DESC=C2= =A0> LIMIT 10; > 2018-02-13 8:28 GMT-05:00 <> mkslaf@keemail.me> >: > >> >> 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 CREATE TABLE friends (>> =C2=A0 =C2=A0 =C2=A0 =C2=A0 user_i= d=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 BIGSERIAL PRIMARY KEY,>> =C2=A0 =C2=A0= =C2=A0 =C2=A0 user_id BIGINT=C2=A0 =C2=A0 NOT 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(use= r_id, id); >> Each user can unilaterally follow any number of friends. The posts table= has a large number of rows and is rapidly growing. >> My goal is to retrieve the 10 most recent posts of a user's friends. Thi= s query 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.user_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 reasonabl= y fast (>> https://explain.depesz.com/s/6ykR>> ). But if the user's friends= haven't recently posted or the user has no friends, it quickly deteriorate= s (>> https://explain.depesz.com/s/OnoG>> ). >> If I match only a single post author (e.g. WHERE posts.user_id =3D 5), P= ostgres uses the index posts_user_id_id_index. But if I use IN, the index d= oesn'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 >> ht= tp://dbfiddle.uk/?rdbms=3Dpostgres_9.6&fiddle=3Dcf1489b7f6d53c3fe0b55ed7ccb= ad1f0>> . The output of "SELECT version()" is "PostgreSQL 9.6.5 on x86_64-p= c-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 suggestions you are a= ble to give me. >> Regards,>> Milo>> =20 > > > > --=20 > Cordialmente,=20 > > Ing. Hellmuth I. Vargas S.=20 > Esp. Telem=C3=A1tica y Negocios por Internet=C2=A0> Oracle Database 10g A= dministrator Certified Associate > EnterpriseDB=C2=A0Certified=C2=A0PostgreSQL 9.3=C2=A0Associate > ------=_Part_122368_1158520999.1518697080810 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hello Hellmuth,

Thank you for your response.

I've uploaded the query plan for the first query (user_i= d=3D2) here: https:/= /gist.github.com/anonymous/6d251b277ef71f8977b03cab91fedccd
T= he query plan for the second query (user_id=3D1) can be found here: https://gist.github.com/ano= nymous/32ed485b40cce2651ddc52661f3e7f7b

Just= like in the original queries, posts_user_id_id_index is not used.

Kind regards,
Milo

13. Feb 201= 8 22:13 by hivs77@gmail.com:

Hello:


EXPLAIN (ANALYZE, BU= FFERS)
select *= from (
=09SELECT posts.id, users.name, po= sts.content
=09FROM posts JOIN users ON posts.user= _id =3D users.id
=09WHERE posts.user_id IN (SEL= ECT friend_user_id FROM friends WHERE user_id =3D 1)

=09OR= DER BY posts.id DESC 
) as a
ORDER BY a.id DESC 
LIMIT 10;

------


EXPLAIN = (ANALYZE, BUFFERS)
select * from (
=09SELECT posts.id, users.name, posts.content
=09FROM posts JOIN users O= N posts.user_id =3D users.id
= =09WHERE posts.user_id IN (SELECT friend_user_id FROM friends WHERE = user_id =3D 2)

= =09ORDER BY posts.id DESC 
) as a
ORDER = BY a.= id DESC 
LIMIT 10;

2018-02-13 8:28 GMT-05:00 &l= t;mkslaf@keemail.me>:
=20 =20 =20
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 po= sts.user_id =3D users.id
    WHERE posts.user_id IN = (SELECT friend_user_id FROM friends WHERE user_id =3D 1)
  &= nbsp; ORDER BY posts.id DESC LIMIT 10;

If the= user's friends have recently posted, the query is still reasonably fast (<= a href=3D"https://explain.depesz.com/s/6ykR" target=3D"_blank" rel=3D"noope= ner noreferrer">https://explain.depesz.com/s/6ykR). But if the user's f= riends haven't recently posted or the user has no friends, it quickly deter= iorates (https://explain.depesz.com/s/OnoG).

If I match only a single post author (e.g. WHERE posts.us= er_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 the schema and the queries I've tried to dbfiddle at http://dbfiddle= .uk/?rdbms=3Dpostgres_9.6&fiddle=3Dcf1489b7f6d53c3fe0b55ed7ccbad1f0= . The output of "SELECT version()" is "PostgreSQL 9.6.5 on x= 86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit" f= or me.

Thank you in advance for any insights, po= inters or suggestions you are able to give me.

R= egards,
Milo



--
Cordialmente,

Ing. Hellmuth I. Vargas S.
Esp. Telemática y Negocios por Internet 
Oracle Database = 10g Administrator Certified Associate
EnterpriseDB Certified=  PostgreSQL 9.3 Associate

------=_Part_122368_1158520999.1518697080810--