explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FQm

Settings
# exclusive inclusive rows x rows loops node
1. 0.267 7,276.035 ↓ 226.0 226 1

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

  • Merge Cond: ((ch.recipient_db_id)::text = (sub.recipient_db_id)::text)
2. 1,792.963 1,792.963 ↓ 47.0 47 1

GroupAggregate (cost=6,155.06..6,155.08 rows=1 width=33) (actual time=1,792.815..1,792.963 rows=47 loops=1)

3. 5,482.390 5,482.805 ↓ 226.0 226 1

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

4. 0.000 0.415 ↑ 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.034..0.415 rows=286 loops=1)

  • Group Key: sub.recipient_db_id
  • Group Key: ch.recipient_db_id
5. 1,792.847 1,792.847 ↓ 235.0 235 1

Sort (cost=6,155.06..6,155.06 rows=1 width=50) (actual time=1,792.800..1,792.847 rows=235 loops=1)

6. 0.000 5,322.880 ↓ 103,853.0 103,853 1

Sort (cost=2,546.38..2,546.39 rows=1 width=88) (actual time=5,299.018..5,322.880 rows=103,853 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)
  • Sort Method: quicksort Memory: 58kB
  • Sort Method: quicksort Memory: 17677kB
  • Sort Key: sub.recipient_db_id
  • Sort Key: ch.recipient_db_id
7. 1,792.584 1,792.584 ↓ 235.0 235 1

Subquery Scan on sub (cost=6,155.01..6,155.05 rows=1 width=50) (actual time=1,786.132..1,792.584 rows=235 loops=1)

8. 50.316 5,148.100 ↓ 103,853.0 103,853 1

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

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

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

10. 1,792.506 1,792.506 ↓ 235.0 235 1

GroupAggregate (cost=6,155.01..6,155.04 rows=1 width=75) (actual time=1,786.129..1,792.506 rows=235 loops=1)

11. 0.000 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)

  • Rows Removed by Filter: 3327
  • Group Key: r.id, c.id
  • Filter: ((max(a.created_at) IS NULL) OR (max(dr.created_at) > max(a.created_at)))
12. 1,787.440 1,787.440 ↓ 12,283.0 12,283 1

Sort (cost=6,155.01..6,155.01 rows=1 width=91) (actual time=1,785.623..1,787.440 rows=12,283 loops=1)

13. 766.871 766.871 ↓ 15,299.0 30,598 1

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

14. 0.000 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)

  • Sort Method: quicksort Memory: 2112kB
  • Sort Key: r.id, c.id
  • Index Cond: (((parent_id)::text = (t.id)::text) AND ((type)::text = 'RECIPIENT'::text) AND (is_deleted = false))
  • Filter: (NOT is_deleted)
15. 1,758.228 1,758.228 ↓ 12,283.0 12,283 1

Nested Loop Left Join (cost=12.96..6,155.00 rows=1 width=91) (actual time=1.838..1,758.228 rows=12,283 loops=1)

16. 245.995 245.995 ↓ 194.4 45,284 1

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

17. 0.000 498.124 ↑ 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.011..0.011 rows=1 loops=45,284)

  • Rows Removed by Filter: 0
  • Index Cond: ((id)::text = (t.parent_id)::text)
  • Filter: ((NOT is_deleted) AND ((type)::text = 'CONTROL_PLAN'::text))
18. 1,682.933 1,682.933 ↓ 5,931.0 5,931 1

Nested Loop (cost=12.40..6,153.51 rows=1 width=83) (actual time=1.819..1,682.933 rows=5,931 loops=1)

19. 65.241 65.241 ↓ 2.0 2 5,931

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

20. 219.358 219.358 ↓ 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.067 rows=14 loops=3,274)

21. 0.000 5.391 ↓ 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.048..5.391 rows=3,274 loops=1)

  • Rows Removed by Filter: 0
  • Index Cond: (((r.id)::text = (parent_id)::text) AND ((type)::text = 'ADDITIONAL_INFORM'::text) AND (is_deleted = false))
  • Index Cond: (((document_id)::text = (d.id)::text) AND ((type)::text = 'FOR_EXECUTION'::text) AND (is_deleted = false))
  • Index Cond: (((db_id)::text = '58fb1512809c134656986d2b'::text) AND ((type_id)::text = ANY ('{58f0be6d53aa2e226ea922e2,58f0be6d53aa2e226ea922e1,58f0be6d53aa2e226ea922e3,58f0be6d53aa2e226ea922fd}'::text[])) AND (is_deleted = false))
  • Filter: (is_active AND (NOT is_deleted) AND (NOT draft))
  • Filter: (NOT is_deleted)
  • Filter: (NOT is_deleted)
22. 1,356.068 1,356.068 ↓ 29,931.0 29,931 1

Nested Loop (cost=11.84..6,152.02 rows=1 width=75) (actual time=0.350..1,356.068 rows=29,931 loops=1)

23. 0.000 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))
24. 1,063.868 1,063.868 ↓ 30,664.0 30,664 1

Nested Loop (cost=11.29..6,150.39 rows=1 width=75) (actual time=0.333..1,063.868 rows=30,664 loops=1)

25. 0.000 275.976 ↑ 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.009..0.009 rows=1 loops=30,664)

  • Rows Removed by Filter: 0
  • Index Cond: ((id)::text = (p_1.parent_id)::text)
  • Filter: ((NOT is_deleted) AND ((type)::text = 'CONTROL_PLAN'::text))
26. 488.880 488.880 ↑ 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.010..0.010 rows=1 loops=48,888)

27. 45.691 551.800 ↓ 2,875.8 48,888 1

Hash Semi Join (cost=10.73..6,125.29 rows=17 width=75) (actual time=0.304..551.800 rows=48,888 loops=1)

  • Rows Removed by Filter: 0
  • Index Cond: ((id)::text = (r.parent_id)::text)
  • Hash Cond: ((r.recipient_db_id)::text = (test_1.id)::text)
  • Filter: ((NOT is_deleted) AND ((type)::text = 'FOR_EXECUTION'::text))
28. 505.958 505.958 ↓ 157.5 107,247 1

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

29. 0.000 0.151 ↑ 1.0 286 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
30. 458.360 458.360 ↓ 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.015..0.140 rows=33 loops=3,274)

31. 5.550 5.550 ↓ 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.550 rows=3,274 loops=1)

32. 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.004..0.070 rows=286 loops=1)

  • Rows Removed by Filter: 35
  • Index Cond: (((document_id)::text = (d_1.id)::text) AND ((type)::text = 'RECIPIENT'::text) AND (is_deleted = false))
  • 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)
  • Filter: ((NOT is_deleted) AND (task_done IS NOT TRUE))
Planning time : 6.595 ms
Execution time : 7,277.365 ms