explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mFiP

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 42,474.456 ↑ 1.0 21 1

Limit (cost=149,181.45..149,183.90 rows=21 width=280) (actual time=42,463.501..42,474.456 rows=21 loops=1)

2. 22.758 42,473.853 ↑ 7,347.5 21 1

Gather Merge (cost=149,181.45..167,184.14 rows=154,298 width=280) (actual time=42,463.485..42,473.853 rows=21 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
3. 1,121.716 42,451.095 ↑ 389.6 198 3 / 3

Sort (cost=148,181.42..148,374.29 rows=77,149 width=280) (actual time=42,449.520..42,451.095 rows=198 loops=3)

  • Sort Key: v."dateCreated" DESC
  • Sort Method: external merge Disk: 9,504kB
4. 27,171.227 41,329.379 ↓ 1.1 85,988 3 / 3

Nested Loop (cost=49,609.44..131,898.21 rows=77,149 width=280) (actual time=20,628.916..41,329.379 rows=85,988 loops=3)

5. 14,158.152 14,158.152 ↑ 1.0 21 1 / 3

Join Filter: (((b."authLevel" = 'ANOLimit (cost=149,181.45..149,183.90 rows=21 width=280) (actual time=42,463.501..42,474.456 rows=21 loops=1)

6. 22.758 42,473.853 ↑ 7,347.5 21 1

Gather Merge (cost=149,181.45..167,184.14 rows=154,298 width=280) (actual time=42,463.485..42,473.853 rows=21 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 1,121.716 42,451.095 ↑ 389.6 198 3 / 3

Sort (cost=148,181.42..148,374.29 rows=77,149 width=280) (actual time=42,449.520..42,451.095 rows=198 loops=3)

  • Sort Key: v."dateCreated" DESC
  • Sort Method: external merge Disk: 9,504kB
8. 0.000 41,329.379 ↓ 1.1 85,988 3 / 3

Nested Loop (cost=49,609.44..131,898.21 rows=77,149 width=280) (actual time=20,628.916..41,329.379 rows=85,988 loops=3)

  • Join Filter: (((b."authLevel" = 'ANONYMOUS'::"enum_boards_authLevel") OR (u.email IS NOT NULL)) AND ((b.visibility = 'PUBLIC'::enum_boards_visibility) OR (u.email IS NOT NULL)))
  • Rows Removed by Join Filter: 29,293
  • Rows Removed by Join Filter: 29,293
9. 7,852.884 35,086.719 ↓ 1.4 115,281 3 / 3

Hash Join (cost=49,609.01..87,614.04 rows=85,190 width=84) (actual time=20,628.871..35,086.719 rows=115,281 loops=3)

  • Hash Cond: (v."postId" = p.id)
10. 6,605.394 6,605.394 ↑ 1.3 537,512 3 / 3

Parallel Seq Scan on votes v (cost=0.00..34,631.85 rows=672,336 width=44) (actual time=0.186..6,605.394 rows=537,512 loops=3)

  • Filter: (("dateDeleted" IS NULL) AND ("dateCreated" < '2020-07-18 13:01:54.032+00'::timestamp with time zone))
  • Rows Removed by Filter: 80,729
11. 230.271 20,628.441 ↓ 1.2 17,629 3 / 3

Hash (cost=49,426.19..49,426.19 rows=14,626 width=56) (actual time=20,628.436..20,628.441 rows=17,629 loops=3)

  • Buckets: 32,768 (originally 16384) Batches: 1 (originally 1) Memory Usage: 1,770kB
12. 613.917 20,398.170 ↓ 1.2 17,629 3 / 3

Hash Join (cost=37,568.88..49,426.19 rows=14,626 width=56) (actual time=19,116.726..20,398.170 rows=17,629 loops=3)

  • Hash Cond: (p."userId" = pu.id)
  • Join Filter: ((b.visibility = 'PUBLIC'::enum_boards_visibility) OR (pu.email IS NOT NULL))
13. 418.438 672.403 ↓ 1.1 17,629 3 / 3

Nested Loop (cost=413.20..5,924.91 rows=15,465 width=72) (actual time=2.861..672.403 rows=17,629 loops=3)

14. 0.054 0.054 ↑ 1.0 1 3 / 3

Index Scan using boards_pkey on boards b (cost=0.28..8.30 rows=1 width=24) (actual time=0.042..0.054 rows=1 loops=3)

  • Index Cond: (id = '8b3fc46d-e606-47b9-8824-aa464779b812'::uuid)
15. 251.734 253.911 ↓ 1.1 17,629 3 / 3

Bitmap Heap Scan on posts p (cost=412.91..5,761.96 rows=15,465 width=80) (actual time=2.789..253.911 rows=17,629 loops=3)

  • Recheck Cond: ("boardId" = '8b3fc46d-e606-47b9-8824-aa464779b812'::uuid)
  • Filter: ("dateDeleted" IS NULL)
  • Rows Removed by Filter: 473
  • Heap Blocks: exact=3,883
16. 2.177 2.177 ↓ 1.0 18,117 3 / 3

Bitmap Index Scan on posts_board_id (cost=0.00..409.05 rows=17,684 width=0) (actual time=2.172..2.177 rows=18,117 loops=3)

  • Index Cond: ("boardId" = '8b3fc46d-e606-47b9-8824-aa464779b812'::uuid)
17. 9,450.722 19,111.850 ↓ 1.3 708,452 3 / 3

Hash (cost=24,302.18..24,302.18 rows=553,000 width=61) (actual time=19,111.846..19,111.850 rows=708,452 loops=3)

  • Buckets: 65,536 Batches: 16 Memory Usage: 3,086kB
18. 9,661.128 9,661.128 ↓ 1.3 708,452 3 / 3

Seq Scan on users pu (cost=0.00..24,302.18 rows=553,000 width=61) (actual time=0.044..9,661.128 rows=708,452 loops=3)

  • Filter: ("dateDeleted" IS NULL)
  • Rows Removed by Filter: 16
19. 2,190.345 2,190.345 ↑ 1.0 1 345,844 / 3

Index Scan using users_pkey on users u (cost=0.42..0.50 rows=1 width=77) (actual time=0.016..0.019 rows=1 loops=345,844)

  • Index Cond: (id = v."userId")
  • Filter: ("dateDeleted" IS NULL)
20. 7,852.884 35,086.719 ↓ 1.4 115,281 3 / 3

Hash Join (cost=49,609.01..87,614.04 rows=85,190 width=84) (actual time=20,628.871..35,086.719 rows=115,281 loops=3)

  • Hash Cond: (v."postId" = p.id)
21. 6,605.394 6,605.394 ↑ 1.3 537,512 3 / 3

Parallel Seq Scan on votes v (cost=0.00..34,631.85 rows=672,336 width=44) (actual time=0.186..6,605.394 rows=537,512 loops=3)

  • Filter: (("dateDeleted" IS NULL) AND ("dateCreated" < '2020-07-18 13:01:54.032+00'::timestamp with time zone))
  • Rows Removed by Filter: 80,729
22. 230.271 20,628.441 ↓ 1.2 17,629 3 / 3

Hash (cost=49,426.19..49,426.19 rows=14,626 width=56) (actual time=20,628.436..20,628.441 rows=17,629 loops=3)

  • Buckets: 32,768 (originally 16384) Batches: 1 (originally 1) Memory Usage: 1,770kB
23. 613.917 20,398.170 ↓ 1.2 17,629 3 / 3

Hash Join (cost=37,568.88..49,426.19 rows=14,626 width=56) (actual time=19,116.726..20,398.170 rows=17,629 loops=3)

  • Hash Cond: (p."userId" = pu.id)
  • Join Filter: ((b.visibility = 'PUBLIC'::enum_boards_visibility) OR (pu.email IS NOT NULL))
24. 418.438 672.403 ↓ 1.1 17,629 3 / 3

Nested Loop (cost=413.20..5,924.91 rows=15,465 width=72) (actual time=2.861..672.403 rows=17,629 loops=3)

25. 0.054 0.054 ↑ 1.0 1 3 / 3

Index Scan using boards_pkey on boards b (cost=0.28..8.30 rows=1 width=24) (actual time=0.042..0.054 rows=1 loops=3)

  • Index Cond: (id = '8b3fc46d-e606-47b9-8824-aa464779b812'::uuid)
26. 251.734 253.911 ↓ 1.1 17,629 3 / 3

Bitmap Heap Scan on posts p (cost=412.91..5,761.96 rows=15,465 width=80) (actual time=2.789..253.911 rows=17,629 loops=3)

  • Recheck Cond: ("boardId" = '8b3fc46d-e606-47b9-8824-aa464779b812'::uuid)
  • Filter: ("dateDeleted" IS NULL)
  • Rows Removed by Filter: 473
  • Heap Blocks: exact=3,883
27. 2.177 2.177 ↓ 1.0 18,117 3 / 3

Bitmap Index Scan on posts_board_id (cost=0.00..409.05 rows=17,684 width=0) (actual time=2.172..2.177 rows=18,117 loops=3)

  • Index Cond: ("boardId" = '8b3fc46d-e606-47b9-8824-aa464779b812'::uuid)
28. 9,450.722 19,111.850 ↓ 1.3 708,452 3 / 3

Hash (cost=24,302.18..24,302.18 rows=553,000 width=61) (actual time=19,111.846..19,111.850 rows=708,452 loops=3)

  • Buckets: 65,536 Batches: 16 Memory Usage: 3,086kB
29. 9,661.128 9,661.128 ↓ 1.3 708,452 3 / 3

Seq Scan on users pu (cost=0.00..24,302.18 rows=553,000 width=61) (actual time=0.044..9,661.128 rows=708,452 loops=3)

  • Filter: ("dateDeleted" IS NULL)
  • Rows Removed by Filter: 16
30. 2,190.345 2,190.345 ↑ 1.0 1 345,844 / 3

Index Scan using users_pkey on users u (cost=0.42..0.50 rows=1 width=77) (actual time=0.016..0.019 rows=1 loops=345,844)

  • Index Cond: (id = v."userId")
  • Filter: ("dateDeleted" IS NULL)
Planning time : 1.783 ms
Execution time : 42,477.735 ms