Início / dba / Perguntas / 340434
Accepted
sh3nan1gans
Asked: 2024-06-22 05:27:21 +0800 CST

Como otimizar a consulta com vários relacionamentos muitos para muitos

  • 772

Tenho um problema de desempenho ao consultar uma tabela de eventos que possui mais de um milhão de linhas. Nossa lógica de aplicação estipula que os usuários só podem acessar eventos aos quais tenham acesso por meio de seus 'grupos'. Os grupos têm um relacionamento muitos-para-muitos com os ativos e os ativos têm um relacionamento muitos-para-muitos com os eventos. Portanto, para encontrar os eventos aos quais um usuário tem acesso, estamos atualmente ingressando em events -> events_assets -> assets -> groups_assets

Tenho índices em events.id, events_assets.event_id, assets.id e groups_assets.asset_id.

A natureza do aplicativo é que o número de eventos e, portanto, events_assets cresce enquanto o número de ativos e groups_assets permanece relativamente baixo e estático.

Aqui está o esquema:

create table events (id text, last_updated TIMESTAMP);
create table events_assets (event_id text, asset_id text);
create table assets (id text);
create table groups_assets (group_id text, asset_id text);

A pergunta:

EXPLAIN ANALYZE
SELECT
  events.*
FROM
  events
WHERE
  (
    events.id IN (
      SELECT
        events.id
      FROM
        events
        INNER JOIN events_assets ON (events.id = events_assets.event_id)
        INNER JOIN assets ON (assets.id = events_assets.asset_id)
      WHERE
        (
          assets.id in (
            (
              SELECT
                id
              FROM
                assets
                LEFT JOIN groups_assets ON (groups_assets.asset_id = assets.id)
              WHERE
                (groups_assets.group_id IN ('default'))
            )
          )
        )
    )
  )
ORDER BY
  last_updated DESC
LIMIT
  25 OFFSET 0

E o plano de consulta:

Limit  (cost=77402.59..77402.66 rows=25 width=1970) (actual time=2147.720..2148.033 rows=25 loops=1)
  ->  Sort  (cost=77402.59..77462.96 rows=24147 width=1970) (actual time=2147.714..2148.024 rows=25 loops=1)
        Sort Key: events.last_updated DESC
        Sort Method: top-N heapsort  Memory: 109kB
        ->  Nested Loop  (cost=15979.68..76721.18 rows=24147 width=1970) (actual time=1074.494..2097.334 rows=144882 loops=1)
              ->  HashAggregate  (cost=15979.26..16220.73 rows=24147 width=74) (actual time=1074.361..1225.749 rows=144882 loops=1)
                    Group Key: events_1.id
                    Batches: 5  Memory Usage: 8241kB  Disk Usage: 11304kB
                    ->  Gather  (cost=1013.12..15918.90 rows=24147 width=74) (actual time=0.872..99.711 rows=144882 loops=1)
                          Workers Planned: 1
                          Workers Launched: 1
                          ->  Nested Loop  (cost=13.12..12504.20 rows=14204 width=74) (actual time=1.567..278.523 rows=72441 loops=2)
                                ->  Hash Join  (cost=12.70..3543.54 rows=14254 width=37) (actual time=1.019..34.109 rows=72441 loops=2)
                                      Hash Cond: (events_assets.asset_id = assets.id)
                                      ->  Parallel Seq Scan on events_assets  (cost=0.00..3103.23 rows=85523 width=74) (actual time=0.183..18.023 rows=72441 loops=2)
                                      ->  Hash  (cost=12.68..12.68 rows=1 width=100) (actual time=0.808..0.813 rows=8 loops=2)
                                            Buckets: 1024  Batches: 1  Memory Usage: 10kB
                                            ->  Nested Loop  (cost=9.37..12.68 rows=1 width=100) (actual time=0.777..0.793 rows=8 loops=2)
                                                  Join Filter: (assets.id = groups_assets.asset_id)
                                                  ->  HashAggregate  (cost=9.24..9.25 rows=1 width=66) (actual time=0.755..0.759 rows=8 loops=2)
                                                        Group Key: assets_1.id
                                                        Batches: 1  Memory Usage: 24kB
                                                        Worker 0:  Batches: 1  Memory Usage: 24kB
                                                        ->  Nested Loop  (cost=0.13..9.23 rows=1 width=66) (actual time=0.702..0.730 rows=9 loops=2)
                                                              ->  Seq Scan on groups_assets  (cost=0.00..1.07 rows=1 width=32) (actual time=0.220..0.222 rows=9 loops=2)
                                                                    Filter: (group_id = 'default'::text)
                                                              ->  Index Only Scan using assets_id on assets assets_1  (cost=0.13..8.15 rows=1 width=34) (actual time=0.055..0.056 rows=1 loops=18)
                                                                    Index Cond: (id = groups_assets.asset_id)
                                                                    Heap Fetches: 18
                                                  ->  Index Only Scan using assets_id on assets  (cost=0.13..3.42 rows=1 width=34) (actual time=0.003..0.003 rows=1 loops=16)
                                                        Index Cond: (id = assets_1.id)
                                                        Heap Fetches: 16
                                ->  Index Only Scan using events_id on events events_1  (cost=0.42..0.62 rows=1 width=37) (actual time=0.003..0.003 rows=1 loops=144882)
                                      Index Cond: (id = events_assets.event_id)
                                      Heap Fetches: 18621
              ->  Index Scan using events_id on events  (cost=0.42..2.50 rows=1 width=1970) (actual time=0.006..0.006 rows=1 loops=144882)
                    Index Cond: (id = events_1.id)
Planning Time: 1.994 ms
Execution Time: 2159.146 ms
postgresql

1 respostas

  1. Best Answer

    Parece que você pode simplificar radicalmente para:

    SELECT e.*
FROM   events e
WHERE  EXISTS (
   SELECT
   FROM   events_assets ea
   JOIN   groups_assets ga USING (asset_id)
   WHERE  ea.event_id = e.id
   AND    ga.group_id = 'default'
   )
ORDER  BY e.last_updated DESC
LIMIT  25
OFFSET 0;

    O que LEFT JOINvocê tinha era um INNER JOINdisfarce. Uma WHEREcláusula filtrada na tabela à direita contradiz a natureza de a LEFT JOIN. Ver:

    Elimine o intermediário, a tabela assets- assumindo a integridade referencial imposta por restrições FK ou não, para que não precisemos verificar se assetsexiste uma linha correspondente na tabela e podemos unir events_assets& groups_assetsdiretamente.

    Uma única EXISTSsubconsulta (em vez de 2x IN) deve resolver o problema. As linhas qualificadas eventssão retornadas apenas uma vez, mesmo que sejam qualificadas várias vezes.

    Estes são todos os índices que você precisa para dar suporte a esta consulta:

    CREATE INDEX ON groups_assets (group_id, asset_id);
CREATE INDEX ON events_assets (asset_id, event_id);
CREATE INDEX ON events (id, last_updated DESC);

    Ou, se a EXISTSsubconsulta não for (estimada) muito seletiva, o Postgres começará do outro lado e você precisará de índices como:

    CREATE INDEX ON events (last_updated DESC);
CREATE INDEX ON events_assets (event_id, asset_id);
CREATE INDEX ON groups_assets (group_id, asset_id);

    Muitos pequenos (e grandes) detalhes influenciam o plano de consulta.

    • 4

relate perguntas