explain.depesz.com

PostgreSQL's explain analyze made readable

Result: r1ZD

Settings
# exclusive inclusive rows x rows loops node
1. 1.985 4,897.884 ↑ 1.0 20 1

Limit (cost=63,357.02..63,358.42 rows=20 width=874) (actual time=4,895.755..4,897.884 rows=20 loops=1)

2. 0.170 4,895.899 ↑ 16.9 20 1

Result (cost=63,357.02..63,380.68 rows=338 width=874) (actual time=4,895.752..4,895.899 rows=20 loops=1)

3. 59.600 4,895.729 ↑ 16.9 20 1

Sort (cost=63,357.02..63,357.86 rows=338 width=889) (actual time=4,895.725..4,895.729 rows=20 loops=1)

  • Sort Key: sender.created_at DESC
  • Sort Method: top-N heapsort Memory: 124kB
4. 1,658.596 4,836.129 ↓ 143.9 48,622 1

GroupAggregate (cost=63,310.00..63,348.03 rows=338 width=889) (actual time=3,020.722..4,836.129 rows=48,622 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,058.504 3,177.533 ↓ 408.7 138,127 1

Sort (cost=63,310.00..63,310.85 rows=338 width=929) (actual time=3,020.574..3,177.533 rows=138,127 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: 106360kB
6. 0.000 2,119.029 ↓ 408.7 138,127 1

Gather (cost=25,042.78..63,295.80 rows=338 width=929) (actual time=306.452..2,119.029 rows=138,127 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
7. 274.082 2,151.982 ↓ 316.8 34,532 4

Nested Loop Left Join (cost=24,042.78..62,262.00 rows=109 width=929) (actual time=296.629..2,151.982 rows=34,532 loops=4)

8. 334.329 1,877.894 ↓ 316.8 34,532 4

Nested Loop (cost=24,042.37..62,107.84 rows=109 width=901) (actual time=296.594..1,877.894 rows=34,532 loops=4)

9. 250.121 1,543.545 ↓ 29.8 14,580 4

Nested Loop Left Join (cost=24,041.81..58,713.07 rows=490 width=829) (actual time=296.563..1,543.545 rows=14,580 loops=4)

10. 172.296 1,293.409 ↓ 162.0 14,580 4

Nested Loop (cost=24,041.25..58,411.61 rows=90 width=805) (actual time=296.490..1,293.409 rows=14,580 loops=4)

11. 92.879 1,121.101 ↓ 136.2 12,261 4

Nested Loop Left Join (cost=24,040.70..57,743.11 rows=90 width=769) (actual time=296.459..1,121.101 rows=12,261 loops=4)

12. 149.692 1,028.216 ↓ 136.2 12,261 4

Nested Loop Left Join (cost=24,040.28..57,615.82 rows=90 width=741) (actual time=296.426..1,028.216 rows=12,261 loops=4)

13. 98.761 878.514 ↓ 136.2 12,261 4

Nested Loop Left Join (cost=24,039.86..57,486.29 rows=90 width=575) (actual time=296.341..878.514 rows=12,261 loops=4)

14. 208.950 779.747 ↓ 136.2 12,261 4

Nested Loop (cost=24,039.44..57,358.97 rows=90 width=547) (actual time=296.255..779.747 rows=12,261 loops=4)

15. 208.073 570.782 ↓ 36.3 12,267 4

Nested Loop (cost=24,038.89..54,972.98 rows=338 width=475) (actual time=296.177..570.782 rows=12,267 loops=4)

16. 72.913 362.694 ↓ 2.2 12,424 4

Parallel Bitmap Heap Scan on tasks sender (cost=24,038.33..41,354.07 rows=5,654 width=109) (actual time=296.062..362.694 rows=12,424 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 draft) AND (NOT is_deleted))
  • Heap Blocks: exact=10937
17. 8.338 289.781 ↓ 0.0 0 1

BitmapAnd (cost=24,038.33..24,038.33 rows=17,526 width=0) (actual time=289.781..289.781 rows=0 loops=1)

18. 29.719 29.719 ↓ 1.5 167,638 1

Bitmap Index Scan on tasks_sender_user_id_index (cost=0.00..1,610.92 rows=113,993 width=0) (actual time=29.719..29.719 rows=167,638 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. 251.724 251.724 ↑ 1.0 1,125,757 1

Bitmap Index Scan on tasks_type_draft_is_active_is_deleted_due_date_index (cost=0.00..22,418.40 rows=1,142,599 width=0) (actual time=251.723..251.724 rows=1,125,757 loops=1)

  • Index Cond: (((type)::text = 'SENDER'::text) AND (draft = false) AND (is_deleted = false))
20. 0.015 0.015 ↑ 1.0 1 49,694

Index Scan using tasks_pkey on tasks for_resolution (cost=0.56..2.41 rows=1 width=366) (actual time=0.015..0.015 rows=1 loops=49,694)

  • 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. 0.015 0.015 ↑ 4.0 1 49,069

Index Scan using tasks_parent_id_type_is_deleted_index on tasks sibling_of_sender (cost=0.56..7.02 rows=4 width=97) (actual time=0.014..0.015 rows=1 loops=49,069)

  • Index Cond: (((parent_id)::text = (for_resolution.id)::text) AND (is_deleted = false))
  • Filter: ((recipient_user_id IS NOT NULL) AND (NOT is_deleted) AND ((type)::text <> 'SENDER'::text))
  • Rows Removed by Filter: 1
22. 0.006 0.006 ↑ 1.0 1 49,044

Index Scan using users_pkey on users sender_user (cost=0.41..1.41 rows=1 width=78) (actual time=0.006..0.006 rows=1 loops=49,044)

  • Index Cond: ((id)::text = (for_resolution.sender_user_id)::text)
23. 0.010 0.010 ↑ 1.0 1 49,044

Index Scan using documents_pkey on documents doc (cost=0.42..1.44 rows=1 width=191) (actual time=0.010..0.010 rows=1 loops=49,044)

  • Index Cond: ((id)::text = (for_resolution.document_id)::text)
24. 0.006 0.006 ↑ 1.0 1 49,044

Index Scan using users_pkey on users userssibling_of_sender (cost=0.41..1.41 rows=1 width=78) (actual time=0.006..0.006 rows=1 loops=49,044)

  • Index Cond: ((id)::text = (sibling_of_sender.recipient_user_id)::text)
25. 0.012 0.012 ↑ 13.0 1 49,044

Index Scan using tasks_parent_id_type_is_deleted_index on tasks "actionOfsibling_of_sender" (cost=0.56..7.30 rows=13 width=61) (actual time=0.011..0.012 rows=1 loops=49,044)

  • Index Cond: ((parent_id)::text = (sibling_of_sender.id)::text)
26. 0.015 0.015 ↑ 1.0 1 58,318

Index Scan using tasks_document_id_db_id_type_is_deleted_index on tasks root_task (cost=0.56..3.34 rows=1 width=49) (actual time=0.015..0.015 rows=1 loops=58,318)

  • 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)
27. 0.020 0.020 ↑ 1.5 2 58,318

Index Scan using tasks_parent_id_type_is_deleted_index on tasks "recipientOfsibling_of_sender" (cost=0.56..6.90 rows=3 width=97) (actual time=0.014..0.020 rows=2 loops=58,318)

  • Index Cond: (((parent_id)::text = ("actionOfsibling_of_sender".id)::text) AND (is_deleted = false))
  • Filter: ((recipient_user_id IS NOT NULL) AND (NOT is_deleted) AND ((type)::text <> 'SENDER'::text) AND ((type)::text <> 'SENDER'::text))
  • Rows Removed by Filter: 2
28. 0.006 0.006 ↑ 1.0 1 138,127

Index Scan using users_pkey on users "usersActionOfsibling_of_sender" (cost=0.41..1.41 rows=1 width=78) (actual time=0.006..0.006 rows=1 loops=138,127)

  • Index Cond: ((id)::text = ("recipientOfsibling_of_sender".recipient_user_id)::text)
29.          

SubPlan (forGroupAggregate)

30. 0.000 0.000 ↑ 1.0 1 48,622

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

Planning time : 13.198 ms
Execution time : 4,918.532 ms