explain.depesz.com

PostgreSQL's explain analyze made readable

Result: v9lu

Settings
# exclusive inclusive rows x rows loops node
1. 2.139 6,135.216 ↑ 1.0 20 1

Limit (cost=83,262.40..83,263.80 rows=20 width=867) (actual time=6,132.934..6,135.216 rows=20 loops=1)

2. 0.166 6,133.077 ↑ 26.1 20 1

Result (cost=83,262.40..83,298.94 rows=522 width=867) (actual time=6,132.931..6,133.077 rows=20 loops=1)

3. 72.866 6,132.911 ↑ 26.1 20 1

Sort (cost=83,262.40..83,263.70 rows=522 width=882) (actual time=6,132.907..6,132.911 rows=20 loops=1)

  • Sort Key: sender.created_at DESC
  • Sort Method: top-N heapsort Memory: 155kB
4. 2,030.562 6,060.045 ↓ 107.9 56,302 1

GroupAggregate (cost=83,189.78..83,248.51 rows=522 width=882) (actual time=3,898.880..6,060.045 rows=56,302 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,338.306 4,029.483 ↓ 323.7 168,996 1

Sort (cost=83,189.78..83,191.09 rows=522 width=922) (actual time=3,898.747..4,029.483 rows=168,996 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: 132,376kB
6. 0.000 2,691.177 ↓ 323.7 168,996 1

Gather (cost=37,992.33..83,166.22 rows=522 width=922) (actual time=480.324..2,691.177 rows=168,996 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
7. 58.464 2,725.950 ↓ 251.5 42,249 4 / 4

Nested Loop Left Join (cost=36,992.33..82,114.02 rows=168 width=922) (actual time=471.718..2,725.950 rows=42,249 loops=4)

8. 53.490 2,371.743 ↓ 251.5 42,249 4 / 4

Nested Loop (cost=36,991.92..81,985.72 rows=168 width=894) (actual time=471.683..2,371.743 rows=42,249 loops=4)

9. 39.080 1,959.447 ↓ 23.2 17,086 4 / 4

Nested Loop Left Join (cost=36,991.36..79,159.84 rows=737 width=822) (actual time=471.655..1,959.447 rows=17,086 loops=4)

10. 30.916 1,664.077 ↓ 141.2 17,086 4 / 4

Nested Loop (cost=36,990.80..78,747.36 rows=121 width=798) (actual time=471.598..1,664.077 rows=17,086 loops=4)

11. 27.778 1,448.613 ↓ 117.3 14,196 4 / 4

Nested Loop Left Join (cost=36,990.24..78,249.91 rows=121 width=762) (actual time=471.577..1,448.613 rows=14,196 loops=4)

12. 31.543 1,335.659 ↓ 117.3 14,196 4 / 4

Nested Loop Left Join (cost=36,989.82..78,157.51 rows=121 width=734) (actual time=471.533..1,335.659 rows=14,196 loops=4)

13. 31.299 1,162.156 ↓ 117.3 14,196 4 / 4

Nested Loop Left Join (cost=36,989.40..78,058.23 rows=121 width=564) (actual time=471.476..1,162.156 rows=14,196 loops=4)

14. 32.259 1,045.681 ↓ 117.3 14,196 4 / 4

Nested Loop (cost=36,988.98..77,965.69 rows=121 width=536) (actual time=471.412..1,045.681 rows=14,196 loops=4)

15. 22.987 800.388 ↓ 32.2 14,202 4 / 4

Nested Loop (cost=36,988.42..76,067.59 rows=441 width=464) (actual time=471.340..800.388 rows=14,202 loops=4)

16. 431.435 547.189 ↓ 2.0 14,388 4 / 4

Parallel Bitmap Heap Scan on tasks sender (cost=36,987.86..58,817.68 rows=7,106 width=109) (actual time=471.262..547.189 rows=14,388 loops=4)

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

BitmapAnd (cost=36,987.86..36,987.86 rows=22,028 width=0) (actual time=463.017..463.017 rows=0 loops=1)

18. 8.366 8.366 ↓ 1.4 195,742 1 / 4

Bitmap Index Scan on tasks_sender_user_id_index (cost=0.00..1,970.16 rows=142,086 width=0) (actual time=33.464..33.465 rows=195,742 loops=1)

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

Bitmap Index Scan on tasks_type_draft_is_active_is_deleted_due_date_created_at_index (cost=0.00..35,006.43 rows=1,634,571 width=0) (actual time=418.308..418.309 rows=1,638,748 loops=1)

  • Index Cond: (((type)::text = 'SENDER'::text) AND (draft = false) AND (is_deleted = false))
20. 230.212 230.212 ↑ 1.0 1 57,553 / 4

Index Scan using tasks_pkey on tasks for_resolution (cost=0.56..2.43 rows=1 width=355) (actual time=0.016..0.016 rows=1 loops=57,553)

  • 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. 213.034 213.034 ↑ 5.0 1 56,809 / 4

Index Scan using tasks_parent_id_type_is_deleted_draft_index on tasks sibling_of_sender (cost=0.56..4.25 rows=5 width=97) (actual time=0.014..0.015 rows=1 loops=56,809)

  • 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. 85.176 85.176 ↑ 1.0 1 56,784 / 4

Index Scan using users_pkey on users sender_user (cost=0.41..0.76 rows=1 width=78) (actual time=0.006..0.006 rows=1 loops=56,784)

  • Index Cond: ((id)::text = (for_resolution.sender_user_id)::text)
23. 141.960 141.960 ↑ 1.0 1 56,784 / 4

Index Scan using documents_pkey on documents doc (cost=0.42..0.82 rows=1 width=195) (actual time=0.010..0.010 rows=1 loops=56,784)

  • Index Cond: ((id)::text = (for_resolution.document_id)::text)
24. 85.176 85.176 ↑ 1.0 1 56,784 / 4

Index Scan using users_pkey on users userssibling_of_sender (cost=0.41..0.76 rows=1 width=78) (actual time=0.006..0.006 rows=1 loops=56,784)

  • Index Cond: ((id)::text = (sibling_of_sender.recipient_user_id)::text)
25. 184.548 184.548 ↑ 16.0 1 56,784 / 4

Index Scan using tasks_parent_id_type_is_deleted_draft_index on tasks "actionOfsibling_of_sender" (cost=0.56..3.95 rows=16 width=61) (actual time=0.012..0.013 rows=1 loops=56,784)

  • Index Cond: ((parent_id)::text = (sibling_of_sender.id)::text)
26. 256.290 256.290 ↑ 2.0 1 68,344 / 4

Index Scan using tasks_document_id_db_id_type_is_deleted_index on tasks root_task (cost=0.56..3.39 rows=2 width=49) (actual time=0.015..0.015 rows=1 loops=68,344)

  • 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. 358.806 358.806 ↑ 2.0 2 68,344 / 4

Index Scan using tasks_parent_id_type_is_deleted_draft_index on tasks "recipientOfsibling_of_sender" (cost=0.56..3.79 rows=4 width=97) (actual time=0.014..0.021 rows=2 loops=68,344)

  • 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. 295.743 295.743 ↑ 1.0 1 168,996 / 4

Index Scan using users_pkey on users "usersActionOfsibling_of_sender" (cost=0.41..0.76 rows=1 width=78) (actual time=0.007..0.007 rows=1 loops=168,996)

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

SubPlan (for GroupAggregate)

30. 0.000 0.000 ↑ 1.0 1 56,302

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

Planning time : 13.027 ms
Execution time : 6,163.451 ms