0

I have a performance issue when querying an events table that has over a million rows. Our application logic stipulates that users can only access events that they have access to via their 'groups'. Groups have a many-to-many relationship to assets and assets have a many-to-many relationship to events. So in order to find the events that a user has access to we are currently joining on events -> events_assets -> assets -> groups_assets

I have indexes on events.id, events_assets.event_id, assets.id, and groups_assets.asset_id.

The nature of the application is that the number of events and therefore events_assets grows while the number of assets and groups_assets stays relatively low and static.

Here's the schema:

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);

The query:

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

And the query plan:

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
0

1 Answer 1

4

It would seem you can radically simplify to:

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;

The LEFT JOIN you had was an INNER JOIN in disguise. A WHERE clause filtering on the table to the right contradicts the nature of a LEFT JOIN. See:

Cut out the middleman, the table assets - assuming referential integrity enforced by FK constraints or otherwise, so we don't need to check whether a corresponding row in table assets exists, and can join events_assets & groups_assets directly.

A single EXISTS subquery (instead of 2x IN) should do the job. Qualifying rows in events are returned only once, even if qualifying multiple times.

These are all the indexes you need to support this query:

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);

Or, if the EXISTS subquery is (estimated to be) not very selective, Postgres will start at the other end, and you need indexes like:

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);

Many little (and big) details influence the query plan.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.