explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kr6O : with limit

Settings
# exclusive inclusive rows x rows loops node
1. 0.012 61.054 ↑ 1.7 104 1

Limit (cost=11,340.76..11,341.19 rows=174 width=198) (actual time=61.034..61.054 rows=104 loops=1)

2. 0.473 61.042 ↑ 1.7 104 1

Sort (cost=11,340.76..11,341.19 rows=174 width=198) (actual time=61.033..61.042 rows=104 loops=1)

  • Sort Key: c.cached_display_name, c.id, (lower((b.display_name)::text)), a.id
  • Sort Method: quicksort Memory: 45kB
3. 0.099 60.569 ↑ 1.7 104 1

Hash Left Join (cost=11,090.87..11,334.28 rows=174 width=198) (actual time=59.299..60.569 rows=104 loops=1)

  • Hash Cond: (a.project_id = c.id)
4. 0.074 60.390 ↑ 1.7 104 1

Merge Right Join (cost=11,068.52..11,310.06 rows=174 width=162) (actual time=59.202..60.390 rows=104 loops=1)

  • Merge Cond: (b.entity_id = a.project_id)
5. 2.711 2.711 ↑ 43.5 55 1

Index Scan using dnc_entity_id on display_name_caches b (cost=0.43..192,035.07 rows=2,394 width=42) (actual time=1.423..2.711 rows=55 loops=1)

  • Filter: ((entity_type)::text = 'Project'::text)
  • Rows Removed by Filter: 2672
6. 0.098 57.605 ↑ 1.7 104 1

Sort (cost=11,068.10..11,068.53 rows=174 width=124) (actual time=57.593..57.605 rows=104 loops=1)

  • Sort Key: a.project_id
  • Sort Method: quicksort Memory: 39kB
7. 0.038 57.507 ↑ 1.7 104 1

Nested Loop (cost=9,537.07..11,061.62 rows=174 width=124) (actual time=57.028..57.507 rows=104 loops=1)

8. 0.031 57.053 ↑ 1.9 104 1

Unique (cost=9,536.65..9,537.64 rows=199 width=4) (actual time=57.016..57.053 rows=104 loops=1)

9. 0.039 57.022 ↑ 1.9 104 1

Sort (cost=9,536.65..9,537.14 rows=199 width=4) (actual time=57.014..57.022 rows=104 loops=1)

  • Sort Key: t.id
  • Sort Method: quicksort Memory: 29kB
10. 0.078 56.983 ↑ 1.9 104 1

Nested Loop (cost=8,016.44..9,529.05 rows=199 width=4) (actual time=56.477..56.983 rows=104 loops=1)

11. 0.016 56.489 ↑ 1.9 104 1

Limit (cost=8,016.02..8,018.01 rows=199 width=4) (actual time=56.451..56.489 rows=104 loops=1)

12. 0.055 56.473 ↑ 18.3 104 1

HashAggregate (cost=8,016.02..8,035.04 rows=1,902 width=4) (actual time=56.451..56.473 rows=104 loops=1)

  • Group Key: a_1.addressable_id
13. 0.006 56.418 ↑ 18.3 104 1

Append (cost=7,390.46..8,011.26 rows=1,902 width=4) (actual time=55.737..56.418 rows=104 loops=1)

14. 0.011 55.738 ↑ 1,845.0 1 1

HashAggregate (cost=7,390.46..7,408.91 rows=1,845 width=4) (actual time=55.737..55.738 rows=1 loops=1)

  • Group Key: a_1.addressable_id
15. 0.039 55.727 ↑ 1,845.0 1 1

Hash Join (cost=7,023.43..7,385.85 rows=1,845 width=4) (actual time=55.699..55.727 rows=1 loops=1)

  • Hash Cond: (gu.group_id = a_1.entity_id)
16. 0.032 0.046 ↓ 1.2 33 1

Bitmap Heap Scan on group_user_connections gu (cost=4.50..78.17 rows=28 width=4) (actual time=0.022..0.046 rows=33 loops=1)

  • Recheck Cond: (user_id = 153)
  • Filter: (retirement_date IS NULL)
  • Heap Blocks: exact=10
17. 0.014 0.014 ↓ 1.2 33 1

Bitmap Index Scan on group_user_connections_user_id_group_id (cost=0.00..4.50 rows=28 width=0) (actual time=0.014..0.014 rows=33 loops=1)

  • Index Cond: (user_id = 153)
18. 10.895 55.642 ↓ 2.2 61,671 1

Hash (cost=6,661.77..6,661.77 rows=28,573 width=8) (actual time=55.642..55.642 rows=61,671 loops=1)

  • Buckets: 65536 (originally 32768) Batches: 1 (originally 1) Memory Usage: 2922kB
19. 36.441 44.747 ↓ 2.2 61,671 1

Bitmap Heap Scan on addressings a_1 (cost=1,907.83..6,661.77 rows=28,573 width=8) (actual time=8.767..44.747 rows=61,671 loops=1)

  • Recheck Cond: ((entity_type)::text = 'Group'::text)
  • Filter: ((retirement_date IS NULL) AND ((addressing_type)::text = 'to'::text) AND ((addressable_type)::text = 'Task'::text))
  • Rows Removed by Filter: 14853
  • Heap Blocks: exact=2834
20. 8.306 8.306 ↓ 1.2 80,183 1

Bitmap Index Scan on addressings_entity_type (cost=0.00..1,900.68 rows=66,168 width=0) (actual time=8.306..8.306 rows=80,183 loops=1)

  • Index Cond: ((entity_type)::text = 'Group'::text)
21. 0.022 0.674 ↓ 1.8 103 1

Unique (cost=583.04..583.33 rows=57 width=4) (actual time=0.639..0.674 rows=103 loops=1)

22. 0.042 0.652 ↓ 1.8 103 1

Sort (cost=583.04..583.19 rows=57 width=4) (actual time=0.638..0.652 rows=103 loops=1)

  • Sort Key: a_2.addressable_id
  • Sort Method: quicksort Memory: 29kB
23. 0.551 0.610 ↓ 1.8 103 1

Bitmap Heap Scan on addressings a_2 (cost=5.75..581.38 rows=57 width=4) (actual time=0.100..0.610 rows=103 loops=1)

  • Recheck Cond: (entity_id = 153)
  • Filter: ((retirement_date IS NULL) AND ((addressing_type)::text = 'to'::text) AND ((addressable_type)::text = 'Task'::text) AND ((entity_type)::text = 'HumanUser'::text))
  • Rows Removed by Filter: 259
  • Heap Blocks: exact=289
24. 0.059 0.059 ↓ 2.1 362 1

Bitmap Index Scan on addressings_entity_id (cost=0.00..5.74 rows=175 width=0) (actual time=0.059..0.059 rows=362 loops=1)

  • Index Cond: (entity_id = 153)
25. 0.416 0.416 ↑ 1.0 1 104

Index Only Scan using tasks_pkey on tasks t (cost=0.42..7.57 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=104)

  • Index Cond: (id = a_1.addressable_id)
  • Heap Fetches: 104
26. 0.416 0.416 ↑ 1.0 1 104

Index Scan using tasks_pkey on tasks a (cost=0.42..7.64 rows=1 width=124) (actual time=0.004..0.004 rows=1 loops=104)

  • Index Cond: (id = t.id)
  • Filter: ((NOT sg_disabled) AND (task_template_id IS NULL) AND (retirement_date IS NULL) AND ((project_id = ANY ('{91,88,130,82,83,70,105,86,81,103,94,85,113,131,104,92,111,80,97,77,114,98,75,69,99,100,71,122,118,95,108,84,96,110,106,126,109,90,116,123,119,93,78,89,76,117,120,115,102,79,107,101}'::integer[])) OR (project_id IS NULL)))
27. 0.010 0.080 ↑ 1.2 59 1

Hash (cost=21.50..21.50 rows=68 width=10) (actual time=0.080..0.080 rows=59 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
28. 0.070 0.070 ↑ 1.2 59 1

Seq Scan on projects c (cost=0.00..21.50 rows=68 width=10) (actual time=0.016..0.070 rows=59 loops=1)

  • Filter: (retirement_date IS NULL)
  • Rows Removed by Filter: 72