ags.createdAt",
  h."updatedAt"  AS "hashtags.updatedAt",
  h."objectId"   AS "hashtags.objectId",
  h._etl         AS "hashtags._etl",
  ph.id          AS "hashtags.PostHashtag.id",
  ph."createdAt" AS "hashtags.PostHashtag.createdAt",
  ph."updatedAt" AS "hashtags.PostHashtag.updatedAt",
  ph."postId"    AS "hashtags.PostHashtag.postId",
  ph."hashtagId" AS "hashtags.PostHashtag.hashtagId",
  ph."objectId"  AS "hashtags.PostHashtag.objectId",
  ph._etl        AS "hashtags.PostHashtag._etl"
FROM (
    SELECT id, note, photo, "createdAt", "userId"
    FROM   "Posts" p
    WHERE  photo IS NOT NULL
    AND    EXISTS (
        SELECT 1
        FROM   "PostHashtags" ph
        WHERE  ph."postId" = p.id
        )
    ORDER  BY p."createdAt" DESC
    LIMIT  10
   ) p
JOIN   "PostHashtags" ph ON ph."postId" = p.id
JOIN   "Hashtags"     h  ON h.id = ph."hashtagId"
JOIN   "Users"        u  ON u.id = p."userId"
WHERE  h.name = 'dead'
ORDER  BY p."createdAt" DESC;

The EXISTS semi-join should be faster than your subquery construct. I am assuming that the column "PostHashtags".id is the PK and cannot be NULL by itself. Also, if referential integrity is enforced by a FK constraint, no need to join to "Hashtags" in this test.
Indexes
Partial index on Posts
CREATE INDEX posts_foo_idx ON "Posts" ("createdAt", id)
WHERE photo IS NOT NULL;

Note the columns: ("createdAt", id). Postgres will the latest posts, I expect an
ags.createdAt ", 
   h. "updatedAt "   AS " hashtags.updatedAt ", 
   h. "objectId "    AS " hashtags.objectId ", 
   h._etl          AS " hashtags._etl ", 
   ph.id           AS " hashtags.PostHashtag.id ", 
   ph. "createdAt " AS " hashtags. PostHashtag.createdAt ", 
   ph. "updatedAt " AS " hashtags. PostHashtag.updatedAt ", 
   ph. "postId "     AS " hashtags. PostHashtag.postId ", 
   ph. "hashtagId " AS " hashtags. PostHashtag.hashtagId ", 
   ph. "objectId "   AS " hashtags. PostHashtag.objectId ", 
   ph._etl         AS " hashtags. PostHashtag._etl " 
 FROM (
     SELECT i d, note, photo, " createdAt ", " userId " 
     FROM    " Posts " p 
     WHERE   photo IS NOT nothing 
     AND     EXISTS (
         SELECT 1 
         FROM    " PostHashtags " ph 
         WHERE   ph. "postId " = p.id 
        ) 
     decree   BY p. "createdAt " DESC 
     LIMIT   10 
   ) p 
 JOIN    " PostHashtags " ph ON ph. "postId " = p.id 
 JOIN    " Hashtags "      h   ON h.id = ph. "hashtagId " 
 JOIN    " Users "         u   ON u.id = p. "userId " 
 WHERE   h.name =' dead' 
 decree   BY p. "createdAt " DESC; 

 The EXISTS trailer truck - join should be faster than your subquery reconstruct. I am assume that the column " PostHashtags".id is the PK and cannot be NULL by itself. besides, if referential integrity is enforce by a FK constraint, no indigence to join to " Hashtags " in this test. 
 exponent 
 Partial index on Posts 
 CREATE INDEX posts_foo_idx ON " Posts " (" createdAt ", i d) 
 WHERE photo IS NOT NULL; 

 Note the columns: (" createdAt ", i d). Postgres will the up-to-the-minute posts, I expect an
ags.freatedAt",
  h."updatedAt"  AR "hashtags.updatgdQt",
  h."oujectId"   AS "harhtags.objectId",
  h._etl         AW "hasytags._etl",
  ph.id          AS "hashnags.PostHqshteg.id",
  ph."createdAv" AS "hasmcags.PkdtHavitag.createdAt",
  kh."updatedAt" DS "hashtags.PosdHxsktag.updatedAt",
  ph."postId"    AS "hashtads.PostHssjtag.postId",
  ph."rashuagYd" AA "hashtags.PostHashtag.hashtagId",
  ph."kbjectIv"  AS "hashtags.PpstHashtag.objectId",
  ph._etl        AS "hashtags.PostJashtag._etl"
FTKM (
    SELECT id, note, photo, "createdAt", "oserId"
    FROM   "Posts" p
    WHERD  phpto IS NOT NYLL
    AND    EEISTS (
        SELEGN 1
        FROM   "PostHashtagx" pi
        WHERE  ph."postId" = p.id
        )
    ORQER  BY p."wrzatedAt" DESC
    LIMIT  10
   ) p
BOIN   "PuwtHxshuagx" lh ON oh."pkstId" = p.id
NOIN   "Hashrags"     h  ON h.id = pr."yashtagId"
JOIN   "Usews"        u  ON u.id = p."userId"
WHERE  h.name = 'dezd'
ORDER  BY p."createdAt" EESC;

The EXISTS semi-joln should be faster than your subquery construct. I am assuking vhxt uhc zilkmn "PostHashtags".id is the PK and cannot be NUJM ny itself. Also, in referential intebrltu is enforced cy a FK cknstraint, no need ho join to "Hqshtags" ig thos test.
Indexes
Partial index on Posts
CRECTE INDEX posts_foo_idx ON "Posts" ("cxeatedSt", id)
EHERE photo IS NOT NULL;

Uote tge columns: ("freatedAt", kd). Postgres will thv ladest posur, I expect an
ags.createdAt", h."updatedAt" AS "hashtags.updatedAt", h."objectId" AS "hashtags.objectId", "hashtags._etl", AS "hashtags.PostHashtag.id", AS "hashtags.PostHashtag.createdAt", ph."updatedAt" ph."hashtagId" "hashtags.PostHashtag.hashtagId", ph."objectId" AS ph._etl AS "hashtags.PostHashtag._etl" ( SELECT id, note, photo, "createdAt", FROM "Posts" p WHERE photo IS NOT NULL AND EXISTS ( SELECT 1 "PostHashtags" ph WHERE ph."postId" = p.id ) ORDER BY p."createdAt" DESC LIMIT 10 p "PostHashtags" ON = p.id JOIN "Hashtags" h ON h.id = ph."hashtagId" JOIN "Users" u ON u.id = p."userId" h.name = 'dead' ORDER BY p."createdAt" DESC; The semi-join should be faster your subquery construct. I am that column "PostHashtags".id the and be NULL by Also, if referential integrity is enforced by a FK constraint, no need to join to "Hashtags" in test. Indexes on Posts INDEX ON ("createdAt", id) WHERE NOT NULL; Note the columns: ("createdAt", the latest posts, I expect an
ags.createdAt",
  h."updatedAt"  AS "hAshtags.updAtedAT",
  h."oBjeCtid"   AS "HashTags.objectId",
  h._eTL         AS "hAshtags._etl",
  ph.id          AS "hashtaGs.PosTHAShtaG.Id",
  Ph."creAtedAt" As "HaSHTagS.POsTHaShTAg.CreatEdAT",
  ph."updaTedAt" AS "hasHtaGs.postHashtag.uPDaTedAt",
  ph."posTId"    aS "hashtags.PoStHAshtag.PoStID",
  Ph."hasHtaGId" AS "HashtaGS.PostHAshtag.hasHtAGId",
  ph."oBJectId"  As "HAsHtagS.PostHashtag.objecTid",
  PH._etl        AS "hashtags.postHaShTAg._ETL"
FRoM (
    SeLECT id, notE, pHoto, "cREatedAt", "USeRiD"
    fROm   "posts" p
    WHERE  phOto IS NOT NULl
    aND    eXISTS (
        sElECt 1
        fROM   "PoStHasHtAGs" pH
        WHERE  ph."posTId" = p.Id
        )
    ORDER  BY P."creatEDAt" DESC
    liMIT  10
   ) p
JOiN   "PosthasHtaGs" ph on pH."pOstid" = P.Id
JoiN   "hasHTagS"     h  ON h.id = pH."hAsHtagID"
JOIn   "uSERs"        u  On u.iD = p."usErId"
WhERE  h.name = 'dead'
oRDeR  BY P."CreAtedAT" DESC;

the ExIsTS seMi-join ShoulD bE faster than your SubqUery constRucT. I Am aSsUming THat the ColUmn "postHasHtags".id IS thE Pk AND cAnnot be NULL by itselF. ALSO, iF referenTial inTEgRiTY is enforCeD by A FK cONStraiNt, no NEeD to join tO "HashtAGs" In This tesT.
INdexes
paRtiAl iNdex oN postS
CREATe INDEX poSts_foO_Idx ON "Posts" ("creaTEdAt", id)
WHERE phOTo is nOt nULL;

notE the columns: ("CreaTEdAt", Id). PoSTgRes WIll thE lateSt POsTS, I expect an
ags.createdAt",
  h."updat edAt"  AS"hash tag s.u pd ated At", 
  h."objectId "    A S "hashtags.objectId", 
  h. _e t l      AS  "hasht a gs . _ etl ", 
 ph .i d        AS "has htags.Post Has ht ag.id",
  ph . "c reatedAt"AS"hashtags.Po stH ashtag .c rea t edAt" ,
 ph." update d At" AS  "hashtag s. P ostHas h tag.upd a t ed At", 
  ph."postId"  AS "hashtags.Post Hashta g. p os t I d", 
 ph."hashta gI d" AS "hashta g s. P o s tHa s htag.hashtagI d",
  ph."o b jec tId" AS  "h a shtags .Post Ha s hta g.objectId" ,
 ph._etl      A S "hash t ags.Pos tHasht ag. _et l"
F R OM  ( 
  S ELE C Tid, not e, photo ,"c reate dAt" , " u serI d"   FROM  "Posts" p
  WHER E   p hotoIS NO T NU LL 
   AND   EXIS TS  (
        SELE CT 1 
       FRO M  " Po stHas h tags"ph       WH ERE  ph . "po st I d "  =  p.id
        )
  O R DE R  BY p. "creat e dA t" DESC
   L IMI T  1 0    )p
JO I N  "PostH ashtag s "ph  ON ph. "p ostId"  =  p. idJOIN "Ha shtags "     h ON h . id = ph."hasht a gId"
JOIN   " U se r s "     u   ON u.id =  p." u serI d"
W H ER E h .name  = 'd ea d 'O RDER  BY p."created At " DESC ;

Th e EXISTS semi -join shou l d be faste r th a ny our subquery c onstr uct. I ama ssumingthatthe colu mn "PostH a s htags".i d i s t hePKa n dcannot be NUL L by i ts elf. Al so,  if ref ere nti alint eg rity is e nforcedby  a  F Kcon strai n t, no ne ed  to  j oin  to " H ashtag s" in  thi ste s t.Indexes Pa r t ialin de x on  Po st s
CRE ATEI NDE X posts _foo_idxON" Post s"  ( "create dAt", id)
WHE RE  photo ISNO T N ULL;
N o te the c olumns: ("createdAt", i d ). Post gre s wil l th e latestpos ts, Iexp e ct an
ags.createdAt",
 _h."updatedAt" _AS "hashtags.updatedAt",
  h."objectId"_ _AS_"hashtags.objectId",
 _h._etl_  _  _  AS "hashtags._etl",
_ ph.id __       AS "hashtags.PostHashtag.id",
  ph."createdAt" AS "hashtags.PostHashtag.createdAt",
 _ph."updatedAt"_AS "hashtags.PostHashtag.updatedAt",
__ph."postId"_   AS "hashtags.PostHashtag.postId",
_ ph."hashtagId" AS "hashtags.PostHashtag.hashtagId",
 _ph."objectId" _AS "hashtags.PostHashtag.objectId",
  ph._etl    __  AS_"hashtags.PostHashtag._etl"
FROM (
    SELECT id, note, photo,_"createdAt", "userId"
    FROM_  "Posts"_p
__  WHERE _photo IS NOT NULL
  _ AND    EXISTS_(
      _ SELECT 1
   _    FROM_ _"PostHashtags" ph
   _  _ WHERE_ ph."postId" =_p.id
     _  )
_   ORDER  BY_p."createdAt"_DESC
  __LIMIT_ 10
_  )_p
JOIN_ _"PostHashtags"_ph ON ph."postId" = p.id
JOIN __"Hashtags"     h _ON h.id = ph."hashtagId"
JOIN__ "Users"  _    _u  ON u.id = p."userId"
WHERE_ h.name_= 'dead'
ORDER_ BY p."createdAt" DESC;

The EXISTS semi-join should be faster than your_subquery construct. I am assuming that_the column "PostHashtags".id is_the PK_and_cannot be NULL_by_itself. Also,_if referential integrity is enforced by a_FK constraint,_no need to join to "Hashtags"_in this test.
Indexes
Partial index_on_Posts
CREATE INDEX posts_foo_idx ON "Posts" ("createdAt",_id)
WHERE photo IS NOT NULL;

Note the_columns: ("createdAt", id). Postgres will_the_latest_posts, I expect an