explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nL3x

Settings
# exclusive inclusive rows x rows loops node
1. 27.157 3,765.453 ↓ 5.0 3,528 1

Hash Left Join (cost=250,010.59..258,274.17 rows=703 width=113) (actual time=3,612.870..3,765.453 rows=3,528 loops=1)

  • Hash Cond: (kudos_period_rel.from_bo_instance_id = person_kudos_giver_rel.to_bo_instance_id)
2. 8.235 125.723 ↓ 11.6 3,528 1

Nested Loop Left Join (cost=1.85..8,257.60 rows=303 width=89) (actual time=0.096..125.723 rows=3,528 loops=1)

3. 10.643 43.400 ↓ 11.6 3,528 1

Nested Loop Left Join (cost=1.28..3,208.11 rows=303 width=65) (actual time=0.043..43.400 rows=3,528 loops=1)

4. 6.862 22.173 ↓ 11.6 3,528 1

Nested Loop Left Join (cost=0.86..3,069.67 rows=303 width=57) (actual time=0.033..22.173 rows=3,528 loops=1)

5. 4.727 4.727 ↓ 11.6 3,528 1

Index Scan using boi_rel_is_deleted_to_bo_instance_id_boc_rel_type_id_idx on boi_rel kudos_period_rel (cost=0.43..550.83 rows=303 width=16) (actual time=0.020..4.727 rows=3,528 loops=1)

  • Index Cond: ((is_deleted = false) AND (to_bo_instance_id = 6612983) AND (boc_rel_type_id = 15))
  • Filter: (NOT is_deleted)
6. 10.584 10.584 ↑ 1.0 1 3,528

Index Scan using bo_instance_pkey on bo_instance kudos_i (cost=0.43..8.31 rows=1 width=41) (actual time=0.003..0.003 rows=1 loops=3,528)

  • Index Cond: (id = kudos_period_rel.from_bo_instance_id)
  • Filter: (NOT is_deleted)
7. 10.584 10.584 ↑ 1.0 1 3,528

Index Scan using idx_attrib_kudos_bo_instance_id on attrib_kudos attr_kudos (cost=0.42..0.45 rows=1 width=16) (actual time=0.002..0.003 rows=1 loops=3,528)

  • Index Cond: (bo_instance_id = kudos_i.id)
8. 3.528 74.088 ↑ 1.0 1 3,528

Subquery Scan on period_qry (cost=0.57..16.66 rows=1 width=40) (actual time=0.021..0.021 rows=1 loops=3,528)

  • Filter: (period_qry.id = kudos_period_rel.to_bo_instance_id)
9. 17.640 70.560 ↑ 1.0 1 3,528

GroupAggregate (cost=0.57..16.64 rows=1 width=40) (actual time=0.020..0.020 rows=1 loops=3,528)

  • Group Key: period_i.id
10. 10.584 52.920 ↑ 1.0 1 3,528

Result (cost=0.57..16.62 rows=1 width=204) (actual time=0.009..0.015 rows=1 loops=3,528)

  • One-Time Filter: (kudos_period_rel.to_bo_instance_id = 6612983)
11. 14.112 42.336 ↑ 1.0 1 3,528

Nested Loop Left Join (cost=0.57..16.62 rows=1 width=204) (actual time=0.008..0.012 rows=1 loops=3,528)

  • Join Filter: (attr_period.bo_instance_id = period_i.id)
12. 10.584 10.584 ↑ 1.0 1 3,528

Index Scan using bo_instance_pkey on bo_instance period_i (cost=0.43..8.45 rows=1 width=167) (actual time=0.003..0.003 rows=1 loops=3,528)

  • Index Cond: (id = 6612983)
  • Filter: (NOT is_deleted)
13. 10.584 17.640 ↑ 1.0 1 3,528

Result (cost=0.14..8.16 rows=1 width=45) (actual time=0.003..0.005 rows=1 loops=3,528)

  • One-Time Filter: (kudos_period_rel.to_bo_instance_id = 6612983)
14. 7.056 7.056 ↑ 1.0 1 3,528

Index Scan using idx_attrib_period_bo_instance_id on attrib_period attr_period (cost=0.14..8.16 rows=1 width=45) (actual time=0.002..0.002 rows=1 loops=3,528)

  • Index Cond: (bo_instance_id = 6612983)
15. 228.248 3,612.573 ↓ 3.2 256,025 1

Hash (cost=248,995.13..248,995.13 rows=81,089 width=40) (actual time=3,612.572..3,612.573 rows=256,025 loops=1)

  • Buckets: 262144 (originally 131072) Batches: 2 (originally 1) Memory Usage: 16214kB
16. 754.302 3,384.325 ↓ 3.2 256,025 1

GroupAggregate (cost=244,634.94..248,184.24 rows=81,089 width=40) (actual time=2,418.984..3,384.325 rows=256,025 loops=1)

  • Group Key: person_kudos_giver_rel.to_bo_instance_id
17. 413.083 2,630.023 ↓ 1.0 256,025 1

Sort (cost=244,634.94..245,268.86 rows=253,569 width=200) (actual time=2,418.950..2,630.023 rows=256,025 loops=1)

  • Sort Key: person_kudos_giver_rel.to_bo_instance_id
  • Sort Method: external merge Disk: 51152kB
18. 302.677 2,216.940 ↓ 1.0 256,025 1

Gather (cost=78,018.60..197,605.56 rows=253,569 width=200) (actual time=1,625.477..2,216.940 rows=256,025 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
19. 389.643 1,914.263 ↑ 1.2 85,342 3

Parallel Hash Left Join (cost=77,018.60..171,248.66 rows=105,654 width=200) (actual time=1,615.419..1,914.263 rows=85,342 loops=3)

  • Hash Cond: (person_kudos_giver_rel.from_bo_instance_id = giver_i.id)
20. 95.049 109.834 ↑ 1.2 85,342 3

Parallel Bitmap Heap Scan on boi_rel person_kudos_giver_rel (cost=4,753.65..78,438.36 rows=105,654 width=16) (actual time=17.574..109.834 rows=85,342 loops=3)

  • Recheck Cond: (boc_rel_type_id = 32)
  • Filter: (NOT is_deleted)
  • Heap Blocks: exact=4782
21. 14.785 14.785 ↓ 1.0 256,025 1

Bitmap Index Scan on idx_boi_rel_boc_rel_type_id (cost=0.00..4,690.26 rows=253,577 width=0) (actual time=14.784..14.785 rows=256,025 loops=1)

  • Index Cond: (boc_rel_type_id = 32)
22. 672.321 1,414.786 ↑ 1.2 583,654 3

Parallel Hash (cost=43,910.09..43,910.09 rows=729,509 width=192) (actual time=1,414.785..1,414.786 rows=583,654 loops=3)

  • Buckets: 131072 Batches: 32 Memory Usage: 13792kB
23. 742.465 742.465 ↑ 1.2 583,654 3

Parallel Seq Scan on bo_instance giver_i (cost=0.00..43,910.09 rows=729,509 width=192) (actual time=0.091..742.465 rows=583,654 loops=3)

Planning time : 1.230 ms
Execution time : 3,882.595 ms