explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GCTz

Settings
# exclusive inclusive rows x rows loops node
1. 0.308 15,606.023 ↓ 226.0 226 1

Merge Full Join (cost=9,104.36..9,104.46 rows=1 width=81) (actual time=15,411.631..15,606.023 rows=226 loops=1)

  • Merge Cond: ((ch.recipient_db_id)::text = (sub.recipient_db_id)::text)
2. 168.327 13,825.300 ↓ 226.0 226 1

GroupAggregate (cost=2,747.39..2,747.44 rows=1 width=73) (actual time=13,631.394..13,825.300 rows=226 loops=1)

  • Group Key: ch.recipient_db_id
3. 218.664 13,656.973 ↓ 103,853.0 103,853 1

Sort (cost=2,747.39..2,747.39 rows=1 width=88) (actual time=13,623.322..13,656.973 rows=103,853 loops=1)

  • Sort Key: ch.recipient_db_id
  • Sort Method: quicksort Memory: 17677kB
4. 3,735.278 13,438.309 ↓ 103,853.0 103,853 1

Nested Loop Semi Join (cost=2.38..2,747.38 rows=1 width=88) (actual time=0.213..13,438.309 rows=103,853 loops=1)

  • Join Filter: ((ch.recipient_db_id)::text = (org.id)::text)
  • Rows Removed by Join Filter: 9488358
5. 101.841 1,424.423 ↓ 106,136.0 106,136 1

Nested Loop (cost=2.10..2,536.61 rows=1 width=88) (actual time=0.118..1,424.423 rows=106,136 loops=1)

6. 26.686 833.014 ↓ 15,299.0 30,598 1

Nested Loop (cost=1.54..2,533.32 rows=2 width=54) (actual time=0.097..833.014 rows=30,598 loops=1)

7. 21.272 262.920 ↓ 194.4 45,284 1

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

8. 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)
9. 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.013..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
10. 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
11. 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)
12. 8,278.608 8,278.608 ↑ 3.4 90 106,136

Index Scan using organizations_type_id_index on organizations org (cost=0.28..203.97 rows=302 width=50) (actual time=0.005..0.078 rows=90 loops=106,136)

  • 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: 6
13. 0.143 1,780.415 ↓ 48.0 48 1

GroupAggregate (cost=6,356.97..6,356.99 rows=1 width=33) (actual time=1,780.218..1,780.415 rows=48 loops=1)

  • Group Key: sub.recipient_db_id
14. 0.328 1,780.272 ↓ 237.0 237 1

Sort (cost=6,356.97..6,356.97 rows=1 width=50) (actual time=1,780.196..1,780.272 rows=237 loops=1)

  • Sort Key: sub.recipient_db_id
  • Sort Method: quicksort Memory: 58kB
15. 0.084 1,779.944 ↓ 237.0 237 1

Subquery Scan on sub (cost=6,356.92..6,356.96 rows=1 width=50) (actual time=1,773.647..1,779.944 rows=237 loops=1)

16. 4.826 1,779.860 ↓ 237.0 237 1

GroupAggregate (cost=6,356.92..6,356.95 rows=1 width=75) (actual time=1,773.641..1,779.860 rows=237 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: 3328
17. 29.862 1,775.034 ↓ 12,286.0 12,286 1

Sort (cost=6,356.92..6,356.92 rows=1 width=91) (actual time=1,773.147..1,775.034 rows=12,286 loops=1)

  • Sort Key: r.id, c.id
  • Sort Method: quicksort Memory: 2112kB
18. 11.116 1,745.172 ↓ 12,286.0 12,286 1

Nested Loop Left Join (cost=213.98..6,356.91 rows=1 width=91) (actual time=2.285..1,745.172 rows=12,286 loops=1)

19. 30.664 1,668.782 ↓ 5,934.0 5,934 1

Nested Loop (cost=213.42..6,355.42 rows=1 width=83) (actual time=2.265..1,668.782 rows=5,934 loops=1)

20. 42.615 1,338.758 ↓ 29,936.0 29,936 1

Nested Loop (cost=212.86..6,353.92 rows=1 width=75) (actual time=0.846..1,338.758 rows=29,936 loops=1)

21. 67.104 1,050.791 ↓ 30,669.0 30,669 1

Nested Loop (cost=212.30..6,352.30 rows=1 width=75) (actual time=0.821..1,050.791 rows=30,669 loops=1)

22. 44.926 543.650 ↓ 2,716.3 48,893 1

Hash Semi Join (cost=211.75..6,325.72 rows=18 width=75) (actual time=0.784..543.650 rows=48,893 loops=1)

  • Hash Cond: ((r.recipient_db_id)::text = ("ANY_subquery".id)::text)
23. 37.563 498.117 ↓ 157.5 107,252 1

Nested Loop (cost=0.98..6,112.97 rows=681 width=75) (actual time=0.119..498.117 rows=107,252 loops=1)

24. 5.468 5.468 ↓ 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.071..5.468 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)
25. 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
26. 0.088 0.607 ↑ 1.1 286 1

Hash (cost=206.99..206.99 rows=302 width=25) (actual time=0.607..0.607 rows=286 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
27. 0.090 0.519 ↑ 1.1 286 1

Subquery Scan on ANY_subquery (cost=0.28..206.99 rows=302 width=25) (actual time=0.046..0.519 rows=286 loops=1)

28. 0.429 0.429 ↑ 1.1 286 1

Index Scan using organizations_type_id_index on organizations org_1 (cost=0.28..203.97 rows=302 width=50) (actual time=0.042..0.429 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
29. 440.037 440.037 ↑ 1.0 1 48,893

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

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

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

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

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

  • 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))
32. 65.274 65.274 ↓ 2.0 2 5,934

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

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