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