explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vX6Y

Settings
# exclusive inclusive rows x rows loops node
1. 1.975 4,902.351 ↑ 1.0 20 1

Limit (cost=63,351.39..63,352.79 rows=20 width=874) (actual time=4,900.220..4,902.351 rows=20 loops=1)

2. 0.169 4,900.376 ↑ 16.9 20 1

Result (cost=63,351.39..63,375.05 rows=338 width=874) (actual time=4,900.216..4,900.376 rows=20 loops=1)

3. 60.492 4,900.207 ↑ 16.9 20 1

Sort (cost=63,351.39..63,352.23 rows=338 width=889) (actual time=4,900.191..4,900.207 rows=20 loops=1)

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

GroupAggregate (cost=63,304.37..63,342.39 rows=338 width=889) (actual time=3,006.532..4,839.715 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,072.859 3,172.801 ↓ 408.7 138,127 1

Sort (cost=63,304.37..63,305.21 rows=338 width=929) (actual time=3,006.424..3,172.801 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,099.942 ↓ 408.7 138,127 1

Gather (cost=25,041.70..63,290.17 rows=338 width=929) (actual time=309.084..2,099.942 rows=138,127 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
7. 269.055 2,150.431 ↓ 316.8 34,532 4

Nested Loop Left Join (cost=24,041.70..62,256.37 rows=109 width=929) (actual time=300.392..2,150.431 rows=34,532 loops=4)

8. 334.697 1,881.370 ↓ 316.8 34,532 4

Nested Loop (cost=24,041.28..62,102.21 rows=109 width=901) (actual time=300.354..1,881.370 rows=34,532 loops=4)

9. 249.935 1,546.653 ↓ 29.8 14,580 4

Nested Loop Left Join (cost=24,040.73..58,707.62 rows=490 width=829) (actual time=300.323..1,546.653 rows=14,580 loops=4)

10. 174.423 1,296.703 ↓ 162.0 14,580 4

Nested Loop (cost=24,040.17..58,406.15 rows=90 width=805) (actual time=300.254..1,296.703 rows=14,580 loops=4)

11. 91.310 1,122.268 ↓ 136.2 12,261 4

Nested Loop Left Join (cost=24,039.61..57,737.69 rows=90 width=769) (actual time=300.221..1,122.268 rows=12,261 loops=4)

12. 151.301 1,030.952 ↓ 136.2 12,261 4

Nested Loop Left Join (cost=24,039.20..57,610.40 rows=90 width=741) (actual time=300.186..1,030.952 rows=12,261 loops=4)

13. 97.894 879.641 ↓ 136.2 12,261 4

Nested Loop Left Join (cost=24,038.77..57,480.88 rows=90 width=575) (actual time=300.111..879.641 rows=12,261 loops=4)

14. 208.503 781.741 ↓ 136.2 12,261 4

Nested Loop (cost=24,038.36..57,353.55 rows=90 width=547) (actual time=299.997..781.741 rows=12,261 loops=4)

15. 207.867 573.223 ↓ 36.3 12,267 4

Nested Loop (cost=24,037.80..54,967.68 rows=338 width=475) (actual time=299.930..573.223 rows=12,267 loops=4)

16. 71.198 365.341 ↓ 2.2 12,424 4

Parallel Bitmap Heap Scan on tasks sender (cost=24,037.24..41,350.96 rows=5,653 width=109) (actual time=299.824..365.341 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 is_deleted) AND (NOT draft))
  • Heap Blocks: exact=11749
17. 8.881 294.143 ↓ 0.0 0 1

BitmapAnd (cost=24,037.24..24,037.24 rows=17,524 width=0) (actual time=294.142..294.143 rows=0 loops=1)

18. 29.123 29.123 ↓ 1.5 167,638 1

Bitmap Index Scan on tasks_sender_user_id_index (cost=0.00..1,610.80 rows=113,986 width=0) (actual time=29.123..29.123 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. 256.139 256.139 ↑ 1.0 1,125,703 1

Bitmap Index Scan on tasks_type_draft_is_active_is_deleted_due_date_index (cost=0.00..22,417.43 rows=1,142,527 width=0) (actual time=256.139..256.139 rows=1,125,703 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.474 ms
Execution time : 4,923.183 ms