explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3Stu

Settings
# exclusive inclusive rows x rows loops node
1. 0.304 7,253.170 ↓ 226.0 226 1

Merge Full Join (cost=8,904.77..8,904.87 rows=1 width=81) (actual time=7,052.108..7,253.170 rows=226 loops=1)

  • Merge Cond: ((ch.recipient_db_id)::text = (sub.recipient_db_id)::text)
2.          

CTE test

3. 0.434 0.434 ↑ 1.0 286 1

Index Scan using organizations_type_id_is_deleted_index on organizations org (cost=0.28..203.33 rows=300 width=50) (actual time=0.035..0.434 rows=286 loops=1)

  • Index Cond: (((type_id)::text = ANY ('{58db4dcd22e00b0ef61d4491,58db4dac22e00b0ef61d448f,598d61f32f4c170b091be6b2,598d591b2f4c170b091be6a0,68db4de622e00b0ef61d4493,58db4cc022e00b0ef61d4487,58db4d0c22e00b0ef61d448c,58db4cd722e00b0ef61d4489,58db4dcd22e00b0ef61d4492,58fb14e6809c134656986d2a,58db4cab22e00b0ef61d4486,598d6bc62f4c170b091be6c1,58db4d1622e00b0ef61d448d,58db4ccb22e00b0ef61d4488,58db4db822e00b0ef61d4490,58db4d9622e00b0ef61d448e}'::text[])) AND (is_deleted = false))
  • Filter: (NOT is_deleted)
4. 175.705 5,501.851 ↓ 226.0 226 1

GroupAggregate (cost=2,546.38..2,546.43 rows=1 width=73) (actual time=5,301.278..5,501.851 rows=226 loops=1)

  • Group Key: ch.recipient_db_id
5. 188.144 5,326.146 ↓ 103,853.0 103,853 1

Sort (cost=2,546.38..2,546.39 rows=1 width=88) (actual time=5,293.359..5,326.146 rows=103,853 loops=1)

  • Sort Key: ch.recipient_db_id
  • Sort Method: quicksort Memory: 17677kB
6. 1,829.270 5,138.002 ↓ 103,853.0 103,853 1

Nested Loop Semi Join (cost=2.10..2,546.37 rows=1 width=88) (actual time=0.230..5,138.002 rows=103,853 loops=1)

  • Join Filter: ((ch.recipient_db_id)::text = (test.id)::text)
  • Rows Removed by Join Filter: 9529849
7. 92.403 1,292.148 ↓ 106,136.0 106,136 1

Nested Loop (cost=2.10..2,536.62 rows=1 width=88) (actual time=0.115..1,292.148 rows=106,136 loops=1)

8. 24.914 771.373 ↓ 15,299.0 30,598 1

Nested Loop (cost=1.54..2,533.34 rows=2 width=54) (actual time=0.095..771.373 rows=30,598 loops=1)

9. 20.443 248.324 ↓ 194.4 45,285 1

Nested Loop (cost=0.98..2,220.23 rows=233 width=79) (actual time=0.077..248.324 rows=45,285 loops=1)

10. 5.249 5.249 ↓ 17.0 3,274 1

Index Scan using documents_triple_index on documents d (cost=0.42..202.70 rows=193 width=25) (actual time=0.050..5.249 rows=3,274 loops=1)

  • Index Cond: (((db_id)::text = '58fb1512809c134656986d2b'::text) AND ((type_id)::text = ANY ('{58f0be6d53aa2e226ea922e2,58f0be6d53aa2e226ea922e1,58f0be6d53aa2e226ea922e3,58f0be6d53aa2e226ea922fd}'::text[])) AND (is_deleted = false))
  • Filter: (NOT is_deleted)
11. 222.632 222.632 ↓ 2.0 14 3,274

Index Scan using tasks_document_id_db_id_type_is_deleted_index on tasks t (cost=0.56..10.38 rows=7 width=79) (actual time=0.013..0.068 rows=14 loops=3,274)

  • Index Cond: (((document_id)::text = (d.id)::text) AND ((type)::text = 'FOR_EXECUTION'::text) AND (is_deleted = false))
  • Filter: (is_active AND (NOT is_deleted) AND (NOT draft))
  • Rows Removed by Filter: 0
12. 498.135 498.135 ↑ 1.0 1 45,285

Index Scan using tasks_pkey on tasks p (cost=0.56..1.34 rows=1 width=25) (actual time=0.011..0.011 rows=1 loops=45,285)

  • Index Cond: ((id)::text = (t.parent_id)::text)
  • Filter: ((NOT is_deleted) AND ((type)::text = 'CONTROL_PLAN'::text))
  • Rows Removed by Filter: 0
13. 428.372 428.372 ↑ 1.0 3 30,598

Index Scan using tasks_parent_id_type_is_deleted_index on tasks ch (cost=0.56..1.61 rows=3 width=59) (actual time=0.011..0.014 rows=3 loops=30,598)

  • Index Cond: (((parent_id)::text = (t.id)::text) AND ((type)::text = 'RECIPIENT'::text) AND (is_deleted = false))
  • Filter: (NOT is_deleted)
14. 2,016.584 2,016.584 ↑ 3.3 91 106,136

CTE Scan on test (cost=0.00..6.00 rows=300 width=90) (actual time=0.000..0.019 rows=91 loops=106,136)

15. 0.143 1,751.015 ↓ 48.0 48 1

GroupAggregate (cost=6,155.06..6,155.08 rows=1 width=33) (actual time=1,750.817..1,751.015 rows=48 loops=1)

  • Group Key: sub.recipient_db_id
16. 0.282 1,750.872 ↓ 231.0 231 1

Sort (cost=6,155.06..6,155.06 rows=1 width=50) (actual time=1,750.800..1,750.872 rows=231 loops=1)

  • Sort Key: sub.recipient_db_id
  • Sort Method: quicksort Memory: 57kB
17. 0.082 1,750.590 ↓ 231.0 231 1

Subquery Scan on sub (cost=6,155.01..6,155.05 rows=1 width=50) (actual time=1,744.239..1,750.590 rows=231 loops=1)

18. 4.859 1,750.508 ↓ 231.0 231 1

GroupAggregate (cost=6,155.01..6,155.04 rows=1 width=75) (actual time=1,744.236..1,750.508 rows=231 loops=1)

  • Group Key: r.id, c.id
  • Filter: ((max(a.created_at) IS NULL) OR (max(dr.created_at) > max(a.created_at)))
  • Rows Removed by Filter: 3327
19. 30.526 1,745.649 ↓ 12,137.0 12,137 1

Sort (cost=6,155.01..6,155.01 rows=1 width=91) (actual time=1,743.709..1,745.649 rows=12,137 loops=1)

  • Sort Key: r.id, c.id
  • Sort Method: quicksort Memory: 2091kB
20. 9.576 1,715.123 ↓ 12,137.0 12,137 1

Nested Loop Left Join (cost=12.96..6,155.00 rows=1 width=91) (actual time=1.814..1,715.123 rows=12,137 loops=1)

21. 20.138 1,640.856 ↓ 5,881.0 5,881 1

Nested Loop (cost=12.40..6,153.51 rows=1 width=83) (actual time=1.798..1,640.856 rows=5,881 loops=1)

22. 36.752 1,321.458 ↓ 29,926.0 29,926 1

Nested Loop (cost=11.84..6,152.02 rows=1 width=75) (actual time=0.355..1,321.458 rows=29,926 loops=1)

23. 54.362 1,039.426 ↓ 30,660.0 30,660 1

Nested Loop (cost=11.29..6,150.39 rows=1 width=75) (actual time=0.337..1,039.426 rows=30,660 loops=1)

24. 44.809 545.108 ↓ 2,875.5 48,884 1

Hash Semi Join (cost=10.73..6,125.29 rows=17 width=75) (actual time=0.308..545.108 rows=48,884 loops=1)

  • Hash Cond: ((r.recipient_db_id)::text = (test_1.id)::text)
25. 39.808 500.147 ↓ 157.5 107,243 1

Nested Loop (cost=0.98..6,112.97 rows=681 width=75) (actual time=0.107..500.147 rows=107,243 loops=1)

26. 5.253 5.253 ↓ 17.0 3,274 1

Index Scan using documents_triple_index on documents d_1 (cost=0.42..202.70 rows=193 width=25) (actual time=0.069..5.253 rows=3,274 loops=1)

  • Index Cond: (((db_id)::text = '58fb1512809c134656986d2b'::text) AND ((type_id)::text = ANY ('{58f0be6d53aa2e226ea922e2,58f0be6d53aa2e226ea922e1,58f0be6d53aa2e226ea922e3,58f0be6d53aa2e226ea922fd}'::text[])) AND (is_deleted = false))
  • Filter: (NOT is_deleted)
27. 455.086 455.086 ↓ 1.6 33 3,274

Index Scan using tasks_document_id_db_id_type_is_deleted_index on tasks r (cost=0.56..30.42 rows=20 width=100) (actual time=0.014..0.139 rows=33 loops=3,274)

  • Index Cond: (((document_id)::text = (d_1.id)::text) AND ((type)::text = 'RECIPIENT'::text) AND (is_deleted = false))
  • Filter: ((NOT is_deleted) AND (task_done IS NOT TRUE))
  • Rows Removed by Filter: 35
28. 0.082 0.152 ↑ 1.0 286 1

Hash (cost=6.00..6.00 rows=300 width=90) (actual time=0.151..0.152 rows=286 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
29. 0.070 0.070 ↑ 1.0 286 1

CTE Scan on test test_1 (cost=0.00..6.00 rows=300 width=90) (actual time=0.002..0.070 rows=286 loops=1)

30. 439.956 439.956 ↑ 1.0 1 48,884

Index Scan using tasks_pkey on tasks p_1 (cost=0.56..1.48 rows=1 width=50) (actual time=0.009..0.009 rows=1 loops=48,884)

  • Index Cond: ((id)::text = (r.parent_id)::text)
  • Filter: ((NOT is_deleted) AND ((type)::text = 'FOR_EXECUTION'::text))
  • Rows Removed by Filter: 0
31. 245.280 245.280 ↑ 1.0 1 30,660

Index Scan using tasks_pkey on tasks c (cost=0.56..1.63 rows=1 width=25) (actual time=0.008..0.008 rows=1 loops=30,660)

  • Index Cond: ((id)::text = (p_1.parent_id)::text)
  • Filter: ((NOT is_deleted) AND ((type)::text = 'CONTROL_PLAN'::text))
  • Rows Removed by Filter: 0
32. 299.260 299.260 ↓ 0.0 0 29,926

Index Scan using tasks_parent_id_type_is_deleted_index on tasks dr (cost=0.56..1.48 rows=1 width=33) (actual time=0.010..0.010 rows=0 loops=29,926)

  • Index Cond: (((parent_id)::text = (r.id)::text) AND ((type)::text = 'DONE_REQUEST'::text) AND (is_deleted = false))
  • Filter: ((NOT is_deleted) AND (accepted IS NOT TRUE))
33. 64.691 64.691 ↓ 2.0 2 5,881

Index Scan using tasks_parent_id_type_is_deleted_index on tasks a (cost=0.56..1.48 rows=1 width=33) (actual time=0.010..0.011 rows=2 loops=5,881)

  • Index Cond: (((r.id)::text = (parent_id)::text) AND ((type)::text = 'ADDITIONAL_INFORM'::text) AND (is_deleted = false))
  • Filter: (NOT is_deleted)
Planning time : 6.823 ms
Execution time : 7,254.664 ms