explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RUPv

Settings
# exclusive inclusive rows x rows loops node
1. 2.488 5,426.698 ↑ 1.0 20 1

Limit (cost=65,132.03..65,133.43 rows=20 width=875) (actual time=5,424.062..5,426.698 rows=20 loops=1)

2. 0.166 5,424.210 ↑ 18.4 20 1

Result (cost=65,132.03..65,157.86 rows=369 width=875) (actual time=5,424.060..5,424.210 rows=20 loops=1)

3. 65.321 5,424.044 ↑ 18.4 20 1

Sort (cost=65,132.03..65,132.95 rows=369 width=890) (actual time=5,424.040..5,424.044 rows=20 loops=1)

  • Sort Key: sender.created_at DESC
  • Sort Method: top-N heapsort Memory: 120kB
4. 1,793.713 5,358.723 ↓ 133.6 49,301 1

GroupAggregate (cost=65,080.70..65,122.21 rows=369 width=890) (actual time=3,355.354..5,358.723 rows=49,301 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,173.577 3,565.010 ↓ 381.1 140,629 1

Sort (cost=65,080.70..65,081.62 rows=369 width=930) (actual time=3,355.245..3,565.010 rows=140,629 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: 108632kB
6. 0.000 2,391.433 ↓ 381.1 140,629 1

Gather (cost=24,923.47..65,064.96 rows=369 width=930) (actual time=338.216..2,391.433 rows=140,629 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
7. 300.467 2,433.307 ↓ 295.4 35,157 4

Nested Loop Left Join (cost=23,923.47..64,028.06 rows=119 width=930) (actual time=326.779..2,433.307 rows=35,157 loops=4)

8. 384.089 2,132.833 ↓ 295.4 35,157 4

Nested Loop (cost=23,923.05..63,859.51 rows=119 width=902) (actual time=326.735..2,132.833 rows=35,157 loops=4)

9. 292.723 1,748.721 ↓ 28.0 14,825 4

Nested Loop Left Join (cost=23,922.50..59,923.68 rows=530 width=830) (actual time=326.687..1,748.721 rows=14,825 loops=4)

10. 186.403 1,455.980 ↓ 156.1 14,825 4

Nested Loop (cost=23,921.94..59,689.04 rows=95 width=806) (actual time=326.576..1,455.980 rows=14,825 loops=4)

11. 103.416 1,269.564 ↓ 130.9 12,432 4

Nested Loop Left Join (cost=23,921.38..58,935.82 rows=95 width=770) (actual time=326.540..1,269.564 rows=12,432 loops=4)

12. 175.091 1,166.142 ↓ 130.9 12,432 4

Nested Loop Left Join (cost=23,920.97..58,801.25 rows=95 width=742) (actual time=326.500..1,166.142 rows=12,432 loops=4)

13. 109.435 991.039 ↓ 130.9 12,432 4

Nested Loop Left Join (cost=23,920.55..58,664.17 rows=95 width=574) (actual time=326.405..991.039 rows=12,432 loops=4)

14. 235.449 881.597 ↓ 130.9 12,432 4

Nested Loop (cost=23,920.13..58,529.57 rows=95 width=546) (actual time=326.316..881.597 rows=12,432 loops=4)

15. 239.652 646.131 ↓ 34.9 12,438 4

Nested Loop (cost=23,919.58..55,842.22 rows=356 width=474) (actual time=326.233..646.131 rows=12,438 loops=4)

16. 85.272 406.462 ↓ 2.2 12,598 4

Parallel Bitmap Heap Scan on tasks sender (cost=23,919.02..41,777.63 rows=5,834 width=109) (actual time=326.100..406.462 rows=12,598 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=11702
17. 10.354 321.190 ↓ 0.0 0 1

BitmapAnd (cost=23,919.02..23,919.02 rows=18,086 width=0) (actual time=321.190..321.190 rows=0 loops=1)

18. 35.343 35.343 ↓ 1.4 168,412 1

Bitmap Index Scan on tasks_sender_user_id_index (cost=0.00..1,754.48 rows=122,613 width=0) (actual time=35.342..35.343 rows=168,412 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. 275.493 275.493 ↓ 1.0 1,138,016 1

Bitmap Index Scan on tasks_type_draft_is_active_is_deleted_due_date_index (cost=0.00..22,155.24 rows=1,111,182 width=0) (actual time=275.492..275.493 rows=1,138,016 loops=1)

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

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

  • Index Cond: ((id)::text = (sender.parent_id)::text)
  • Filter: ((NOT is_deleted) AND (document_id IS NOT NULL) AND ((type)::text = 'FOR_RESOLUTION'::text))
  • Rows Removed by Filter: 0
21. 0.017 0.017 ↑ 4.0 1 49,754

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.016..0.017 rows=1 loops=49,754)

  • Index Cond: (((parent_id)::text = (for_resolution.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
22. 0.007 0.007 ↑ 1.0 1 49,729

Index Scan using users_pkey on users sender_user (cost=0.41..1.42 rows=1 width=78) (actual time=0.007..0.007 rows=1 loops=49,729)

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

Index Scan using documents_pkey on documents doc (cost=0.42..1.44 rows=1 width=193) (actual time=0.012..0.012 rows=1 loops=49,729)

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

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

  • Index Cond: ((id)::text = (sibling_of_sender.recipient_user_id)::text)
25. 0.013 0.013 ↑ 14.0 1 49,729

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.012..0.013 rows=1 loops=49,729)

  • Index Cond: ((parent_id)::text = (sibling_of_sender.id)::text)
26. 0.018 0.018 ↑ 1.0 1 59,301

Index Scan using tasks_document_id_db_id_type_is_deleted_index on tasks root_task (cost=0.56..2.46 rows=1 width=49) (actual time=0.017..0.018 rows=1 loops=59,301)

  • Index Cond: (((document_id)::text = (sender.document_id)::text) AND ((db_id)::text = '58db4eaa22e00b0ef61d4496'::text) AND ((type)::text = 'ROOT_TASK'::text) AND (is_deleted = false))
  • Filter: (NOT is_deleted)
27. 0.023 0.023 ↑ 1.5 2 59,301

Index Scan using tasks_parent_id_type_is_deleted_index on tasks "recipientOfsibling_of_sender" (cost=0.56..7.40 rows=3 width=97) (actual time=0.015..0.023 rows=2 loops=59,301)

  • 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.007 0.007 ↑ 1.0 1 140,629

Index Scan using users_pkey on users "usersActionOfsibling_of_sender" (cost=0.41..1.42 rows=1 width=78) (actual time=0.007..0.007 rows=1 loops=140,629)

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

SubPlan (for GroupAggregate)

30. 0.000 0.000 ↑ 1.0 1 49,301

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

Planning time : 13.850 ms
Execution time : 5,454.103 ms