Quais índices devem ser usados ​​para otimizar uma consulta PostgreSQL com uma profundidade JOIN de 2?

^{Considere também a primeira resposta .}

Consulta

Isso faz o que sua consulta atual faz atualmente, apenas mais simples e rápido:

SELECT p.id, p.note, p.photo, p."createdAt",
  u.id           AS "user.id",
  u.name         AS "user.name",
  h.id           AS "hashtags.id",
  h.count        AS "hashtags.count",
  h.name         AS "hashtags.name",
  h."createdAt"  AS "hashtags.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;

A EXISTSsemijunção deve ser mais rápida que a construção da subconsulta. Estou assumindo que a coluna "PostHashtags".idé o PK e não pode ser NULL por si só. Além disso, se a integridade referencial for imposta por uma restrição FK, não há necessidade de ingressar "Hashtags"neste teste.

Índices

índice parcial emPosts

CREATE INDEX posts_foo_idx ON "Posts" ("createdAt", id)
WHERE photo IS NOT NULL;

Observe as colunas: ("createdAt", id). O Postgres fará as postagens mais recentes, espero uma varredura de índice a posts_foo_idxpartir do topo, seguida de um teste para correspondência de entradas PostHashtagsusando ido próximo índice.

índice ÚNICO emPostHashtags

Desta vez, precisamos do índice com "postId"primeiro.

O resto é principalmente como na primeira resposta .

^{Considere também a resposta alternativa .}

Consulta

Isso faz o que sua descrição diz, não o que sua consulta faz atualmente ( veja o comentário ):

Selecione Postagens com uma foto e uma Hashtag com o nome 'morto'

.. limitado aos últimos 10 postos de qualificação:

SELECT
  p.id,
  p.note,
  p.photo,
  p."createdAt",
  u.id           AS "user.id",
  u.name         AS "user.name",
  h.id           AS "hashtags.id",
  h.count        AS "hashtags.count",
  h.name         AS "hashtags.name",
  h."createdAt"  AS "hashtags.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   "Hashtags"     h
JOIN   "PostHashtags" ph ON ph."hashtagId" = h.id
JOIN   "Posts"        p  ON p.id = ph."postId"
JOIN   "Users"        u  ON u.id = p."userId"
WHERE  h.name = 'dead'
AND    p.photo IS NOT NULL
ORDER  BY p."createdAt" DESC
LIMIT  10;

Se você impor a integridade referencial entre Postse Userscom uma restrição FK, faça isso Dessa forma, o Postgres pode escolher as últimas dez linhas antes de considerar os usuários.
LEFTJOIN "Users"

As várias subconsultas e parênteses em suas junções não foram úteis.

Índices

Suas duas ideias são boas. Mais pelo menos mais um.

índice parcial emPosts

CREATE INDEX posts_foo_idx ON "Posts" (id, "createdAt")
WHERE photo IS NOT NULL;

Observe as colunas: (id, "createdAt"). Minha expectativa é que o Postgres comece com o predicado mais seletivo Hashtags.namee trabalhe PostHashtagsaté Posts. Então o índice usado posts_foo_idxjá fornece createdAt, que é necessário para ORDER BY/ LIMIT.

O índice parcial só faz sentido se uma grande porcentagem da tabela tiver photo IS NOT NULL. Caso contrário, torne-o um índice completo.

Índice ÚNICO em Hashtags.name.

CREATE UNIQUE INDEX hashtags_foo_idx ON "Hashtags" (name);

índice ÚNICO emPostHashtags

Assumindo que cada postagem só pode ser associada a cada hashtag uma vez .

ALTER TABLE "PostHashtags" ADD CONSTRAINT ph_uni UNIQUE ("hashtagId", "postId");

Este poderia ser o seu PK. Talvez já seja o seu PK, mas com a ordem das colunas invertida: ("postId", "hashtagId"). Você provavelmente deve ter ambas as variantes. Para minha consulta, você precisa primeiro deste com "hashtagId". Para sua consulta, você precisaria do contrário. Considerar:

• Um índice composto também é bom para consultas no primeiro campo?

Aparte

Eu sugiro que você reconsidere sua convenção de nomenclatura. Use identificadores legais em minúsculas para evitar a confusão de citações e muito potencial para erros.