explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KQIQ

Settings
# exclusive inclusive rows x rows loops node
1. 0.354 7,785.185 ↓ 226.0 226 1

Merge Full Join (cost=8,904.79..8,904.89 rows=1 width=81) (actual time=7,579.881..7,785.185 rows=226 loops=1)

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

CTE test

3. 0.436 0.436 ↑ 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.436 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. 178.673 5,746.679 ↓ 226.0 226 1

GroupAggregate (cost=2,546.40..2,546.45 rows=1 width=73) (actual time=5,541.961..5,746.679 rows=226 loops=1)

  • Group Key: ch.recipient_db_id
5. 200.030 5,568.006 ↓ 103,853.0 103,853 1

Sort (cost=2,546.40..2,546.41 rows=1 width=88) (actual time=5,533.924..5,568.006 rows=103,853 loops=1)

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

Nested Loop Semi Join (cost=2.10..2,546.39 rows=1 width=88) (actual time=0.239..5,367.976 rows=103,853 loops=1)

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

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

8. 55.885 815.552 ↓ 15,299.0 30,598 1

Nested Loop (cost=1.54..2,533.35 rows=2 width=54) (actual time=0.098..815.552 rows=30,598 loops=1)

9. 19.883 261.521 ↓ 194.4 45,286 1

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

10. 5.910 5.910 ↓ 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.910 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. 235.728 235.728 ↓ 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.072 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.146 498.146 ↑ 1.0 1 45,286

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

  • 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.3 91 106,136

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

15. 0.171 2,038.152 ↓ 48.0 48 1

GroupAggregate (cost=6,155.06..6,155.08 rows=1 width=33) (actual time=2,037.909..2,038.152 rows=48 loops=1)

  • Group Key: sub.recipient_db_id
16. 0.320 2,037.981 ↓ 233.0 233 1

Sort (cost=6,155.06..6,155.06 rows=1 width=50) (actual time=2,037.896..2,037.981 rows=233 loops=1)

  • Sort Key: sub.recipient_db_id
  • Sort Method: quicksort Memory: 57kB
17. 0.079 2,037.661 ↓ 233.0 233 1

Subquery Scan on sub (cost=6,155.01..6,155.05 rows=1 width=50) (actual time=2,031.540..2,037.661 rows=233 loops=1)

18. 4.914 2,037.582 ↓ 233.0 233 1

GroupAggregate (cost=6,155.01..6,155.04 rows=1 width=75) (actual time=2,031.538..2,037.582 rows=233 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: 3326
19. 33.080 2,032.668 ↓ 12,123.0 12,123 1

Sort (cost=6,155.01..6,155.01 rows=1 width=91) (actual time=2,030.566..2,032.668 rows=12,123 loops=1)

  • Sort Key: r.id, c.id
  • Sort Method: quicksort Memory: 2089kB
20. 12.442 1,999.588 ↓ 12,123.0 12,123 1

Nested Loop Left Join (cost=12.96..6,155.00 rows=1 width=91) (actual time=1.847..1,999.588 rows=12,123 loops=1)

21. 44.377 1,899.006 ↓ 5,876.0 5,876 1

Nested Loop (cost=12.40..6,153.51 rows=1 width=83) (actual time=1.834..1,899.006 rows=5,876 loops=1)

22. 26.183 1,525.487 ↓ 29,922.0 29,922 1

Nested Loop (cost=11.84..6,152.02 rows=1 width=75) (actual time=0.323..1,525.487 rows=29,922 loops=1)

23. 42.124 1,192.734 ↓ 30,657.0 30,657 1

Nested Loop (cost=11.29..6,150.39 rows=1 width=75) (actual time=0.309..1,192.734 rows=30,657 loops=1)

24. 52.387 612.919 ↓ 2,875.4 48,881 1

Hash Semi Join (cost=10.73..6,125.29 rows=17 width=75) (actual time=0.283..612.919 rows=48,881 loops=1)

  • Hash Cond: ((r.recipient_db_id)::text = (test_1.id)::text)
25. 44.123 560.380 ↓ 157.5 107,241 1

Nested Loop (cost=0.98..6,112.97 rows=681 width=75) (actual time=0.099..560.380 rows=107,241 loops=1)

26. 5.513 5.513 ↓ 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.063..5.513 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. 510.744 510.744 ↓ 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.156 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.152..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.003..0.070 rows=286 loops=1)

30. 537.691 537.691 ↑ 1.0 1 48,881

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

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

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

  • 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. 329.142 329.142 ↓ 0.0 0 29,922

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.011..0.011 rows=0 loops=29,922)

  • 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. 88.140 88.140 ↓ 2.0 2 5,876

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.013..0.015 rows=2 loops=5,876)

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