Início / dba / Perguntas / 91247
Accepted
Garrett
Asked: 2015-02-06 18:19:41 +0800 CST

Otimizando uma consulta Postgres com um grande IN

  • 772

Essa consulta obtém uma lista de postagens criadas por pessoas que você segue. Você pode seguir um número ilimitado de pessoas, mas a maioria das pessoas segue < 1.000 pessoas.

Com esse estilo de consulta, a otimização óbvia seria armazenar em cache os "Post"ids, mas infelizmente não tenho tempo para isso no momento.

EXPLAIN ANALYZE SELECT
    "Post"."id",
    "Post"."actionId",
    "Post"."commentCount",
    ...
FROM
    "Posts" AS "Post"
INNER JOIN "Users" AS "user" ON "Post"."userId" = "user"."id"
LEFT OUTER JOIN "ActivityLogs" AS "activityLog" ON "Post"."activityLogId" = "activityLog"."id"
LEFT OUTER JOIN "WeightLogs" AS "weightLog" ON "Post"."weightLogId" = "weightLog"."id"
LEFT OUTER JOIN "Workouts" AS "workout" ON "Post"."workoutId" = "workout"."id"
LEFT OUTER JOIN "WorkoutLogs" AS "workoutLog" ON "Post"."workoutLogId" = "workoutLog"."id"
LEFT OUTER JOIN "Workouts" AS "workoutLog.workout" ON "workoutLog"."workoutId" = "workoutLog.workout"."id"
WHERE
"Post"."userId" IN (
    201486,
    1825186,
    998608,
    340844,
    271909,
    308218,
    341986,
    216893,
    1917226,
    ...  -- many more
)
AND "Post"."private" IS NULL
ORDER BY
    "Post"."createdAt" DESC
LIMIT 10;

Rendimentos:

Limit  (cost=3.01..4555.20 rows=10 width=2601) (actual time=7923.011..7973.138 rows=10 loops=1)
  ->  Nested Loop Left Join  (cost=3.01..9019264.02 rows=19813 width=2601) (actual time=7923.010..7973.133 rows=10 loops=1)
        ->  Nested Loop Left Join  (cost=2.58..8935617.96 rows=19813 width=2376) (actual time=7922.995..7973.063 rows=10 loops=1)
              ->  Nested Loop Left Join  (cost=2.15..8821537.89 rows=19813 width=2315) (actual time=7922.984..7961.868 rows=10 loops=1)
                    ->  Nested Loop Left Join  (cost=1.71..8700662.11 rows=19813 width=2090) (actual time=7922.981..7961.846 rows=10 loops=1)
                          ->  Nested Loop Left Join  (cost=1.29..8610743.68 rows=19813 width=2021) (actual time=7922.977..7961.816 rows=10 loops=1)
                                ->  Nested Loop  (cost=0.86..8498351.81 rows=19813 width=1964) (actual time=7922.972..7960.723 rows=10 loops=1)
                                      ->  Index Scan using posts_createdat_public_index on "Posts" "Post"  (cost=0.43..8366309.39 rows=20327 width=261) (actual time=7922.869..7960.509 rows=10 loops=1)
                                            Filter: ("userId" = ANY ('{201486,1825186,998608,340844,271909,308218,341986,216893,1917226, ... many more ...}'::integer[]))
                                            Rows Removed by Filter: 218360
                                      ->  Index Scan using "Users_pkey" on "Users" "user"  (cost=0.43..6.49 rows=1 width=1703) (actual time=0.005..0.006 rows=1 loops=10)
                                            Index Cond: (id = "Post"."userId")
                                ->  Index Scan using "ActivityLogs_pkey" on "ActivityLogs" "activityLog"  (cost=0.43..5.66 rows=1 width=57) (actual time=0.107..0.107 rows=0 loops=10)
                                      Index Cond: ("Post"."activityLogId" = id)
                          ->  Index Scan using "WeightLogs_pkey" on "WeightLogs" "weightLog"  (cost=0.42..4.53 rows=1 width=69) (actual time=0.001..0.001 rows=0 loops=10)
                                Index Cond: ("Post"."weightLogId" = id)
                    ->  Index Scan using "Workouts_pkey" on "Workouts" workout  (cost=0.43..6.09 rows=1 width=225) (actual time=0.001..0.001 rows=0 loops=10)
                          Index Cond: ("Post"."workoutId" = id)
              ->  Index Scan using "WorkoutLogs_pkey" on "WorkoutLogs" "workoutLog"  (cost=0.43..5.75 rows=1 width=61) (actual time=1.118..1.118 rows=0 loops=10)
                    Index Cond: ("Post"."workoutLogId" = id)
        ->  Index Scan using "Workouts_pkey" on "Workouts" "workoutLog.workout"  (cost=0.43..4.21 rows=1 width=225) (actual time=0.004..0.004 rows=0 loops=10)
              Index Cond: ("workoutLog"."workoutId" = id)
Total runtime: 7974.524 ms

Como isso pode ser otimizado por enquanto?

Tenho os seguintes índices relevantes:

-- Gets used
CREATE INDEX  "posts_createdat_public_index" ON "public"."Posts" USING btree("createdAt" DESC) WHERE "private" IS null;
-- Don't get used
CREATE INDEX  "posts_userid_fk_index" ON "public"."Posts" USING btree("userId");
CREATE INDEX  "posts_following_index" ON "public"."Posts" USING btree("userId", "createdAt" DESC) WHERE "private" IS null;

Talvez isso exija um grande índice composto parcial com createdAte userIdonde private IS NULL?

postgresql index

3 respostas

  1. Best Answer

    Em vez de usar uma lista enorme IN, junte-se a uma VALUESexpressão ou, se a lista for grande o suficiente, use uma tabela temporária, indexe-a e, em seguida, junte-se a ela.

    Seria bom se o PostgreSQL pudesse fazer isso internamente e automaticamente, mas neste momento o planejador não sabe como.

    Tópicos semelhantes:

    • 55

  2. Na verdade, existem duas variantes diferentes da INconstrução no Postgres. Um trabalha com uma expressão de subconsulta (retornando um set ), o outro com uma lista de valores , que é apenas um atalho para

    expression = value1
OR
expression = value2
OR
...

    Você está usando o segundo formulário, que é bom para uma lista curta, mas muito mais lento para listas longas. Forneça sua lista de valores como expressão de subconsulta. Recentemente, tomei conhecimento desta variante :

    WHERE "Post"."userId" IN (VALUES (201486), (1825186), (998608), ... )

    Eu gosto de passar um array, desaninhar e juntar-se a ele. Desempenho semelhante, mas a sintaxe é mais curta:

    ...
FROM   unnest('{201486,1825186,998608, ...}'::int[]) "userId"
JOIN   "Posts" "Post" USING ("userId")

    Equivalente desde que não haja duplicatas no conjunto/array fornecido. Caso contrário, o segundo formulário com a JOINretorna linhas duplicadas, enquanto o primeiro com INretorna apenas uma única instância. Essa diferença sutil também causa planos de consulta diferentes.

    Obviamente, você precisa de um índice em "Posts"."userId".
    Para listas muito longas (milhares), use uma tabela temporária indexada como @Craig sugeriu. Isso permite varreduras de índice de bitmap combinadas em ambas as tabelas, o que normalmente é mais rápido assim que houver várias tuplas por página de dados para buscar do disco.

    Relacionado:

    Além disso: sua convenção de nomenclatura não é muito útil, torna seu código detalhado e difícil de ler. Em vez disso, use identificadores legais, minúsculos e sem aspas.

    • 53

  3. Você pode desabilitar loops aninhados para forçar uma junção de hash indexando os valores. semelhante ao que Craig Ringer disse

    set enable_nestloop to off;

    Essa configuração se aplica apenas à sua sessão de banco de dados atual, se você precisar ativá-la novamente após essa consulta, basta executar

    set enable_nestloop to on;

    no final.

    Você pode esperar melhorias em várias ordens de magnitude no tempo de execução.

    • 0

relate perguntas