explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fGgy

Settings
# exclusive inclusive rows x rows loops node
1. 5.220 5,293.799 ↑ 1.0 20 1

Limit (cost=475,956.17..475,957.57 rows=20 width=875) (actual time=5,288.434..5,293.799 rows=20 loops=1)

2. 0.175 5,288.579 ↑ 5,019.8 20 1

Result (cost=475,956.17..482,983.82 rows=100,395 width=875) (actual time=5,288.430..5,288.579 rows=20 loops=1)

3. 63.903 5,288.404 ↑ 5,019.8 20 1

Sort (cost=475,956.17..476,207.16 rows=100,395 width=890) (actual time=5,288.400..5,288.404 rows=20 loops=1)

  • Sort Key: sender.created_at DESC
  • Sort Method: top-N heapsort Memory: 107kB
4. 1,753.652 5,224.501 ↑ 2.0 50,437 1

GroupAggregate (cost=461,990.26..473,284.69 rows=100,395 width=890) (actual time=3,279.182..5,224.501 rows=50,437 loops=1)

  • Group Key: sender.id, for_resolution.id, sender_user.first_name, sender_user.middle_name, sender_user.last_name, doc.brief_content, sibling_of_sender.id, userssibling_of_sender.first_name, userssibling_of_sender.middle_name, userssibling_of_sender.last_name, root_task.document_id, root_task.document_recipient_reg_number, root_task.document_recipient_reg_date, root_task.document_sender_reg_number, root_task.document_sender_reg_date
5. 1,211.832 3,470.849 ↓ 1.4 142,076 1

Sort (cost=461,990.26..462,241.24 rows=100,395 width=930) (actual time=3,279.075..3,470.849 rows=142,076 loops=1)

  • Sort Key: sender.id, for_resolution.id, sender_user.first_name, sender_user.middle_name, sender_user.last_name, doc.brief_content, sibling_of_sender.id, userssibling_of_sender.first_name, userssibling_of_sender.middle_name, userssibling_of_sender.last_name, root_task.document_id, root_task.document_recipient_reg_number, root_task.document_recipient_reg_date, root_task.document_sender_reg_number, root_task.document_sender_reg_date
  • Sort Method: external merge Disk: 109208kB
6. 0.000 2,259.017 ↓ 1.4 142,076 1

Gather (cost=29,908.38..430,117.78 rows=100,395 width=930) (actual time=445.493..2,259.017 rows=142,076 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
7. 53.214 2,292.676 ↓ 1.1 35,519 4

Parallel Hash Left Join (cost=28,908.38..419,078.28 rows=32,385 width=930) (actual time=432.556..2,292.676 rows=35,519 loops=4)

  • Hash Cond: (("recipientOfsibling_of_sender".recipient_user_id)::text = ("usersActionOfsibling_of_sender".id)::text)
8. 416.839 2,225.646 ↓ 1.1 35,519 4

Nested Loop Left Join (cost=27,261.32..417,346.21 rows=32,385 width=902) (actual time=418.439..2,225.646 rows=35,519 loops=4)

9. 325.969 1,808.783 ↑ 2.2 15,014 4

Nested Loop Left Join (cost=27,260.77..177,569.90 rows=32,385 width=830) (actual time=418.401..1,808.783 rows=15,014 loops=4)

10. 207.436 1,482.795 ↓ 2.6 15,014 4

Nested Loop Left Join (cost=27,260.21..158,084.71 rows=5,841 width=806) (actual time=418.303..1,482.795 rows=15,014 loops=4)

11. 21.791 1,275.345 ↓ 2.2 12,609 4

Parallel Hash Left Join (cost=27,259.65..111,771.62 rows=5,841 width=770) (actual time=418.269..1,275.345 rows=12,609 loops=4)

  • Hash Cond: ((sibling_of_sender.recipient_user_id)::text = (userssibling_of_sender.id)::text)
12. 262.199 1,238.435 ↓ 2.2 12,609 4

Nested Loop Left Join (cost=25,612.59..110,109.23 rows=5,841 width=742) (actual time=403.037..1,238.435 rows=12,609 loops=4)

13. 197.684 976.217 ↓ 2.2 12,608 4

Nested Loop Left Join (cost=25,612.03..66,015.80 rows=5,841 width=645) (actual time=402.965..976.217 rows=12,608 loops=4)

14. 19.208 778.520 ↓ 2.2 12,608 4

Parallel Hash Left Join (cost=25,611.61..57,587.07 rows=5,841 width=477) (actual time=402.877..778.520 rows=12,608 loops=4)

  • Hash Cond: ((for_resolution.sender_user_id)::text = (sender_user.id)::text)
15. 268.416 745.143 ↓ 2.2 12,608 4

Nested Loop Left Join (cost=23,964.55..55,924.68 rows=5,841 width=449) (actual time=388.585..745.143 rows=12,608 loops=4)

16. 102.134 476.708 ↓ 2.2 12,608 4

Parallel Bitmap Heap Scan on tasks sender (cost=23,964.00..41,841.82 rows=5,841 width=109) (actual time=388.478..476.708 rows=12,608 loops=4)

  • Recheck Cond: (((sender_user_id)::text = ANY ('{5b375c3dab11dd5071c5d559,5b84f24c40332645ceee8cde,5b84f65e40332645ceee8d53,593a79f903212a74286cfb6f,5af3e184f2268c668f9e0099,593a7d4903212a74286cfc30,5b37598cab11dd5071c5d529,5b375821ab11dd5071c5d51a,5be0360c799e564b02efeedc,5d1c9bc36d5e0f19076caffd,59eeeb353fc30f5cb7a46f4b,5b84e8e040332645ceee8bd7,5b84ee7a40332645ceee8c4a,5ba4d3ac1de0eb92ad025150,5ba8ba441de0eb92ad025192,5b84f80440332645ceee8d7d}'::text[])) AND ((type)::text = 'SENDER'::text))
  • Filter: ((NOT is_deleted) AND (NOT draft))
  • Heap Blocks: exact=13302
17. 11.139 374.574 ↓ 0.0 0 1

BitmapAnd (cost=23,964.00..23,964.00 rows=18,106 width=0) (actual time=374.574..374.574 rows=0 loops=1)

18. 37.804 37.804 ↓ 1.4 168,609 1

Bitmap Index Scan on tasks_sender_user_id_index (cost=0.00..1,755.56 rows=122,750 width=0) (actual time=37.804..37.804 rows=168,609 loops=1)

  • Index Cond: ((sender_user_id)::text = ANY ('{5b375c3dab11dd5071c5d559,5b84f24c40332645ceee8cde,5b84f65e40332645ceee8d53,593a79f903212a74286cfb6f,5af3e184f2268c668f9e0099,593a7d4903212a74286cfc30,5b37598cab11dd5071c5d529,5b375821ab11dd5071c5d51a,5be0360c799e564b02efeedc,5d1c9bc36d5e0f19076caffd,59eeeb353fc30f5cb7a46f4b,5b84e8e040332645ceee8bd7,5b84ee7a40332645ceee8c4a,5ba4d3ac1de0eb92ad025150,5ba8ba441de0eb92ad025192,5b84f80440332645ceee8d7d}'::text[]))
19. 325.631 325.631 ↓ 1.0 1,140,470 1

Bitmap Index Scan on tasks_type_draft_is_active_is_deleted_due_date_index (cost=0.00..22,199.13 rows=1,112,431 width=0) (actual time=325.631..325.631 rows=1,140,470 loops=1)

  • Index Cond: (((type)::text = 'SENDER'::text) AND (draft = false) AND (is_deleted = false))
20. 0.019 0.019 ↑ 1.0 1 50,431

Index Scan using tasks_pkey on tasks for_resolution (cost=0.56..2.41 rows=1 width=365) (actual time=0.019..0.019 rows=1 loops=50,431)

  • Index Cond: ((id)::text = (sender.parent_id)::text)
  • Filter: ((is_deleted IS NOT TRUE) AND (document_id IS NOT NULL) AND ((type)::text = 'FOR_RESOLUTION'::text))
  • Rows Removed by Filter: 0
21. 7.768 14.169 ↑ 2.3 8,067 4

Parallel Hash (cost=1,410.36..1,410.36 rows=18,936 width=78) (actual time=14.169..14.169 rows=8,067 loops=4)

  • Buckets: 32768 Batches: 1 Memory Usage: 3968kB
22. 6.401 6.401 ↑ 2.3 8,067 4

Parallel Seq Scan on users sender_user (cost=0.00..1,410.36 rows=18,936 width=78) (actual time=0.040..6.401 rows=8,067 loops=4)

23. 0.013 0.013 ↑ 1.0 1 50,431

Index Scan using documents_pkey on documents doc (cost=0.42..1.44 rows=1 width=193) (actual time=0.013..0.013 rows=1 loops=50,431)

  • Index Cond: ((id)::text = (for_resolution.document_id)::text)
24. 0.019 0.019 ↑ 4.0 1 50,431

Index Scan using tasks_parent_id_type_is_deleted_index on tasks sibling_of_sender (cost=0.56..7.51 rows=4 width=97) (actual time=0.017..0.019 rows=1 loops=50,431)

  • Index Cond: (((for_resolution.id)::text = (parent_id)::text) AND (is_deleted = false))
  • Filter: ((NOT is_deleted) AND (recipient_user_id IS NOT NULL) AND ((type)::text <> 'SENDER'::text))
  • Rows Removed by Filter: 1
25. 7.605 15.119 ↑ 2.3 8,067 4

Parallel Hash (cost=1,410.36..1,410.36 rows=18,936 width=78) (actual time=15.119..15.119 rows=8,067 loops=4)

  • Buckets: 32768 Batches: 1 Memory Usage: 3936kB
26. 7.514 7.514 ↑ 2.3 8,067 4

Parallel Seq Scan on users userssibling_of_sender (cost=0.00..1,410.36 rows=18,936 width=78) (actual time=0.021..7.514 rows=8,067 loops=4)

27. 0.014 0.014 ↑ 14.0 1 50,437

Index Scan using tasks_parent_id_type_is_deleted_index on tasks "actionOfsibling_of_sender" (cost=0.56..7.79 rows=14 width=61) (actual time=0.013..0.014 rows=1 loops=50,437)

  • Index Cond: ((parent_id)::text = (sibling_of_sender.id)::text)
28. 0.019 0.019 ↑ 1.0 1 60,058

Index Scan using tasks_document_id_db_id_type_is_deleted_index on tasks root_task (cost=0.56..3.33 rows=1 width=49) (actual time=0.019..0.019 rows=1 loops=60,058)

  • Index Cond: (((document_id)::text = (sender.document_id)::text) AND ((db_id)::text = '58db4eaa22e00b0ef61d4496'::text) AND ((type)::text = 'ROOT_TASK'::text))
  • Filter: (is_deleted IS NOT TRUE)
29. 0.024 0.024 ↑ 2.0 2 60,058

Index Scan using tasks_parent_id_type_is_deleted_index on tasks "recipientOfsibling_of_sender" (cost=0.56..7.36 rows=4 width=97) (actual time=0.016..0.024 rows=2 loops=60,058)

  • Index Cond: (((parent_id)::text = ("actionOfsibling_of_sender".id)::text) AND (is_deleted = false))
  • Filter: ((NOT is_deleted) AND (recipient_user_id IS NOT NULL) AND ((type)::text <> 'SENDER'::text))
  • Rows Removed by Filter: 2
30. 6.795 13.816 ↑ 2.3 8,067 4

Parallel Hash (cost=1,410.36..1,410.36 rows=18,936 width=78) (actual time=13.816..13.816 rows=8,067 loops=4)

  • Buckets: 32768 Batches: 1 Memory Usage: 3904kB
31. 7.021 7.021 ↑ 2.3 8,067 4

Parallel Seq Scan on users "usersActionOfsibling_of_sender" (cost=0.00..1,410.36 rows=18,936 width=78) (actual time=0.024..7.021 rows=8,067 loops=4)

32.          

SubPlan (for GroupAggregate)

33. 0.000 0.000 ↑ 1.0 1 50,437

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=50,437)

Planning time : 11.534 ms
Execution time : 5,318.131 ms