explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Jjhk

Settings
# exclusive inclusive rows x rows loops node
1. 0.270 7,567.121 ↓ 226.0 226 1

Merge Full Join (cost=8,905.54..8,905.65 rows=1 width=81) (actual time=7,386.774..7,567.121 rows=226 loops=1)

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

CTE test

3. 0.481 0.481 ↑ 1.1 286 1

Index Scan using organizations_type_id_index on organizations org (cost=0.28..203.97 rows=302 width=50) (actual time=0.034..0.481 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[]))
  • Filter: (NOT is_deleted)
  • Rows Removed by Filter: 21
4. 162.457 5,802.919 ↓ 226.0 226 1

GroupAggregate (cost=2,546.45..2,546.49 rows=1 width=73) (actual time=5,622.990..5,802.919 rows=226 loops=1)

  • Group Key: ch.recipient_db_id
5. 190.833 5,640.462 ↓ 103,853.0 103,853 1

Sort (cost=2,546.45..2,546.45 rows=1 width=88) (actual time=5,615.186..5,640.462 rows=103,853 loops=1)

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

Nested Loop Semi Join (cost=2.10..2,546.44 rows=1 width=88) (actual time=0.244..5,449.629 rows=103,853 loops=1)

  • Join Filter: ((ch.recipient_db_id)::text = (test.id)::text)
  • Rows Removed by Join Filter: 9488358
7. 82.165 1,410.774 ↓ 106,136.0 106,136 1

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

8. 25.246 839.041 ↓ 15,299.0 30,598 1

Nested Loop (cost=1.54..2,533.33 rows=2 width=54) (actual time=0.099..839.041 rows=30,598 loops=1)

9. 18.917 270.387 ↓ 194.4 45,284 1

Nested Loop (cost=0.98..2,220.23 rows=233 width=79) (actual time=0.079..270.387 rows=45,284 loops=1)

10. 5.920 5.920 ↓ 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.051..5.920 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. 245.550 245.550 ↓ 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.014..0.075 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. 543.408 543.408 ↑ 1.0 1 45,284

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

  • Index Cond: ((id)::text = (t.parent_id)::text)
  • Filter: ((NOT is_deleted) AND ((type)::text = 'CONTROL_PLAN'::text))
  • Rows Removed by Filter: 0
13. 489.568 489.568 ↑ 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.012..0.016 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,122.720 2,122.720 ↑ 3.4 90 106,136

CTE Scan on test (cost=0.00..6.04 rows=302 width=90) (actual time=0.000..0.020 rows=90 loops=106,136)

15. 0.122 1,763.932 ↓ 47.0 47 1

GroupAggregate (cost=6,155.12..6,155.14 rows=1 width=33) (actual time=1,763.771..1,763.932 rows=47 loops=1)

  • Group Key: sub.recipient_db_id
16. 0.273 1,763.810 ↓ 235.0 235 1

Sort (cost=6,155.12..6,155.13 rows=1 width=50) (actual time=1,763.756..1,763.810 rows=235 loops=1)

  • Sort Key: sub.recipient_db_id
  • Sort Method: quicksort Memory: 58kB
17. 0.079 1,763.537 ↓ 235.0 235 1

Subquery Scan on sub (cost=6,155.07..6,155.11 rows=1 width=50) (actual time=1,757.346..1,763.537 rows=235 loops=1)

18. 4.768 1,763.458 ↓ 235.0 235 1

GroupAggregate (cost=6,155.07..6,155.10 rows=1 width=75) (actual time=1,757.343..1,763.458 rows=235 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.538 1,758.690 ↓ 12,282.0 12,282 1

Sort (cost=6,155.07..6,155.08 rows=1 width=91) (actual time=1,756.862..1,758.690 rows=12,282 loops=1)

  • Sort Key: r.id, c.id
  • Sort Method: quicksort Memory: 2112kB
20. 9.726 1,728.152 ↓ 12,282.0 12,282 1

Nested Loop Left Join (cost=13.02..6,155.06 rows=1 width=91) (actual time=1.807..1,728.152 rows=12,282 loops=1)

21. 22.639 1,653.196 ↓ 5,930.0 5,930 1

Nested Loop (cost=12.47..6,153.57 rows=1 width=83) (actual time=1.790..1,653.196 rows=5,930 loops=1)

22. 37.353 1,331.247 ↓ 29,931.0 29,931 1

Nested Loop (cost=11.91..6,152.08 rows=1 width=75) (actual time=0.348..1,331.247 rows=29,931 loops=1)

23. 57.028 1,048.582 ↓ 30,664.0 30,664 1

Nested Loop (cost=11.35..6,150.45 rows=1 width=75) (actual time=0.331..1,048.582 rows=30,664 loops=1)

24. 52.144 551.562 ↓ 2,875.8 48,888 1

Hash Semi Join (cost=10.79..6,125.35 rows=17 width=75) (actual time=0.302..551.562 rows=48,888 loops=1)

  • Hash Cond: ((r.recipient_db_id)::text = (test_1.id)::text)
25. 38.756 499.269 ↓ 157.5 107,247 1

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

26. 5.427 5.427 ↓ 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.068..5.427 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.079 0.149 ↑ 1.1 286 1

Hash (cost=6.04..6.04 rows=302 width=90) (actual time=0.149..0.149 rows=286 loops=1)

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

CTE Scan on test test_1 (cost=0.00..6.04 rows=302 width=90) (actual time=0.004..0.070 rows=286 loops=1)

30. 439.992 439.992 ↑ 1.0 1 48,888

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,888)

  • 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.312 245.312 ↑ 1.0 1 30,664

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,664)

  • 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.310 299.310 ↓ 0.0 0 29,931

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,931)

  • 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. 65.230 65.230 ↓ 2.0 2 5,930

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.009..0.011 rows=2 loops=5,930)

  • 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.979 ms
Execution time : 7,568.641 ms