explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Bdld

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 4,109.256 ↑ 1.0 10 1

Limit (cost=150,288,555.72..150,288,555.75 rows=10 width=2,334) (actual time=4,109.254..4,109.256 rows=10 loops=1)

2.          

CTE permitted_activities

3. 1.492 82.028 ↓ 7.6 1,519 1

Unique (cost=150,285,941.52..150,286,428.69 rows=200 width=9,625) (actual time=77.107..82.028 rows=1,519 loops=1)

4.          

CTE trusted_company_ids

5. 0.023 1.660 ↓ 6.0 12 1

Unique (cost=1,415.20..1,415.21 rows=2 width=4) (actual time=1.622..1.660 rows=12 loops=1)

6. 0.046 1.637 ↓ 39.5 79 1

Sort (cost=1,415.20..1,415.20 rows=2 width=4) (actual time=1.622..1.637 rows=79 loops=1)

  • Sort Key: companies.id
  • Sort Method: quicksort Memory: 28kB
7. 0.071 1.591 ↓ 39.5 79 1

Nested Loop (cost=50.20..1,415.19 rows=2 width=4) (actual time=0.197..1.591 rows=79 loops=1)

8. 0.110 1.120 ↓ 1.9 200 1

Nested Loop (cost=49.91..1,380.67 rows=105 width=8) (actual time=0.105..1.120 rows=200 loops=1)

9. 0.056 0.110 ↓ 2.8 36 1

Bitmap Heap Scan on users (cost=15.58..28.92 rows=13 width=8) (actual time=0.061..0.110 rows=36 loops=1)

  • Recheck Cond: ((id = 5,314) OR (company_id = ANY ('{29138,30499,73382,73381,47701,30498,47700,71282,72817,30497,71283}'::integer[])))
  • Heap Blocks: exact=36
10. 0.002 0.054 ↓ 0.0 0 1

BitmapOr (cost=15.58..15.58 rows=13 width=0) (actual time=0.054..0.054 rows=0 loops=1)

11. 0.005 0.005 ↑ 1.0 1 1

Bitmap Index Scan on users_pkey (cost=0.00..1.30 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=1)

  • Index Cond: (id = 5,314)
12. 0.047 0.047 ↓ 3.1 37 1

Bitmap Index Scan on index_users_on_company_id (cost=0.00..14.27 rows=12 width=0) (actual time=0.047..0.047 rows=37 loops=1)

  • Index Cond: (company_id = ANY ('{29138,30499,73382,73381,47701,30498,47700,71282,72817,30497,71283}'::integer[]))
13. 0.252 0.900 ↑ 8.7 6 36

Bitmap Heap Scan on relationships (cost=34.33..103.46 rows=52 width=12) (actual time=0.021..0.025 rows=6 loops=36)

  • Recheck Cond: (((users.id = user_id) AND (role_id = ANY ('{-1,101,118,126,133,138,146,104,103,106,175,154}'::integer[]))) OR ((users.extends_user_id = user_id) AND (role_id = ANY ('{-1,101,118,126,133,138,146,104,103,106,175,154}'::integer[]))))
  • Filter: ((NOT disabled) AND toggled AND (role_id = ANY ('{-1,101,118,126,133,138,146,104,103,106,175,154}'::integer[])))
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=132
14. 0.108 0.648 ↓ 0.0 0 36

BitmapOr (cost=34.33..34.33 rows=71 width=0) (actual time=0.018..0.018 rows=0 loops=36)

15. 0.540 0.540 ↑ 5.0 7 36

Bitmap Index Scan on index_relationships_on_user_id_and_role_id (cost=0.00..17.15 rows=35 width=0) (actual time=0.015..0.015 rows=7 loops=36)

  • Index Cond: ((users.id = user_id) AND (role_id = ANY ('{-1,101,118,126,133,138,146,104,103,106,175,154}'::integer[])))
16. 0.000 0.000 ↓ 0.0 0 36

Bitmap Index Scan on index_relationships_on_user_id_and_role_id (cost=0.00..17.15 rows=35 width=0) (actual time=0.000..0.000 rows=0 loops=36)

  • Index Cond: ((users.extends_user_id = user_id) AND (role_id = ANY ('{-1,101,118,126,133,138,146,104,103,106,175,154}'::integer[])))
17. 0.400 0.400 ↓ 0.0 0 200

Index Only Scan using companies_pkey on companies (cost=0.29..0.32 rows=1 width=4) (actual time=0.001..0.002 rows=0 loops=200)

  • Index Cond: (id = relationships.company_id)
  • Filter: ((id = 29,138) OR (relationships.role_id = ANY ('{154,138,160,103}'::integer[])))
  • Rows Removed by Filter: 1
  • Heap Fetches: 200
18.          

CTE untrusted_company_ids

19. 0.055 1.330 ↑ 8.6 12 1

HashAggregate (cost=1,418.50..1,419.53 rows=103 width=4) (actual time=1.328..1.330 rows=12 loops=1)

  • Group Key: companies_1.id
20. 0.055 1.275 ↓ 1.3 130 1

Nested Loop (cost=50.20..1,418.25 rows=103 width=4) (actual time=0.075..1.275 rows=130 loops=1)

21. 0.107 0.960 ↓ 1.3 130 1

Nested Loop (cost=49.91..1,385.14 rows=103 width=4) (actual time=0.067..0.960 rows=130 loops=1)

22. 0.038 0.061 ↓ 2.8 36 1

Bitmap Heap Scan on users users_1 (cost=15.58..28.92 rows=13 width=8) (actual time=0.030..0.061 rows=36 loops=1)

  • Recheck Cond: ((id = 5,314) OR (company_id = ANY ('{29138,30499,73382,73381,47701,30498,47700,71282,72817,30497,71283}'::integer[])))
  • Heap Blocks: exact=36
23. 0.001 0.023 ↓ 0.0 0 1

BitmapOr (cost=15.58..15.58 rows=13 width=0) (actual time=0.023..0.023 rows=0 loops=1)

24. 0.003 0.003 ↑ 1.0 1 1

Bitmap Index Scan on users_pkey (cost=0.00..1.30 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1)

  • Index Cond: (id = 5,314)
25. 0.019 0.019 ↓ 3.1 37 1

Bitmap Index Scan on index_users_on_company_id (cost=0.00..14.27 rows=12 width=0) (actual time=0.019..0.019 rows=37 loops=1)

  • Index Cond: (company_id = ANY ('{29138,30499,73382,73381,47701,30498,47700,71282,72817,30497,71283}'::integer[]))
26. 0.180 0.792 ↑ 12.8 4 36

Bitmap Heap Scan on relationships relationships_1 (cost=34.33..103.82 rows=51 width=8) (actual time=0.019..0.022 rows=4 loops=36)

  • Recheck Cond: (((users_1.id = user_id) AND (role_id = ANY ('{-1,101,118,126,133,138,146,104,103,106,175,154}'::integer[]))) OR ((users_1.extends_user_id = user_id) AND (role_id = ANY ('{-1,101,118,126,133,138,146,104,103,106,175,154}'::integer[]))))
  • Filter: ((NOT disabled) AND toggled AND (role_id <> ALL ('{154,138,160,103}'::integer[])) AND (role_id = ANY ('{-1,101,118,126,133,138,146,104,103,106,175,154}'::integer[])))
  • Rows Removed by Filter: 3
  • Heap Blocks: exact=132
27. 0.108 0.612 ↓ 0.0 0 36

BitmapOr (cost=34.33..34.33 rows=71 width=0) (actual time=0.017..0.017 rows=0 loops=36)

28. 0.504 0.504 ↑ 5.0 7 36

Bitmap Index Scan on index_relationships_on_user_id_and_role_id (cost=0.00..17.15 rows=35 width=0) (actual time=0.014..0.014 rows=7 loops=36)

  • Index Cond: ((users_1.id = user_id) AND (role_id = ANY ('{-1,101,118,126,133,138,146,104,103,106,175,154}'::integer[])))
29. 0.000 0.000 ↓ 0.0 0 36

Bitmap Index Scan on index_relationships_on_user_id_and_role_id (cost=0.00..17.15 rows=35 width=0) (actual time=0.000..0.000 rows=0 loops=36)

  • Index Cond: ((users_1.extends_user_id = user_id) AND (role_id = ANY ('{-1,101,118,126,133,138,146,104,103,106,175,154}'::integer[])))
30. 0.260 0.260 ↑ 1.0 1 130

Index Only Scan using companies_pkey on companies companies_1 (cost=0.29..0.31 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=130)

  • Index Cond: (id = relationships_1.company_id)
  • Heap Fetches: 130
31.          

CTE activities

32. 46.029 59.883 ↑ 17.1 1,519 1

Bitmap Heap Scan on activities (cost=1,400.67..63,558.02 rows=25,929 width=1,418) (actual time=19.039..59.883 rows=1,519 loops=1)

  • Recheck Cond: ((author_company_id = ANY ('{29138,30499,73382,73381,47701,30498,47700,71282,72817,30497,71283}'::integer[])) OR (company_id = ANY ('{29138,30499,73382,73381,47701,30498,47700,71282,72817,30497,71283}'::integer[])))
  • Filter: ((NOT completed) AND CASE completed WHEN CASE_TEST_EXPR THEN (completed_at > '2019-08-30 00:00:00'::timestamp without time zone) ELSE (due_at > '2019-08-30 00:00:00'::timestamp without time zone) END)
  • Rows Removed by Filter: 66,397
  • Heap Blocks: exact=28,360
33. 0.002 13.854 ↓ 0.0 0 1

BitmapOr (cost=1,400.67..1,400.67 rows=133,514 width=0) (actual time=13.854..13.854 rows=0 loops=1)

34. 8.213 8.213 ↓ 1.0 67,794 1

Bitmap Index Scan on index_activities_on_author_company_id (cost=0.00..673.54 rows=64,782 width=0) (actual time=8.213..8.213 rows=67,794 loops=1)

  • Index Cond: (author_company_id = ANY ('{29138,30499,73382,73381,47701,30498,47700,71282,72817,30497,71283}'::integer[]))
35. 5.639 5.639 ↑ 1.0 68,302 1

Bitmap Index Scan on index_activities_on_company_id (cost=0.00..714.16 rows=68,732 width=0) (actual time=5.639..5.639 rows=68,302 loops=1)

  • Index Cond: (company_id = ANY ('{29138,30499,73382,73381,47701,30498,47700,71282,72817,30497,71283}'::integer[]))
36. 1.968 80.536 ↑ 8.5 1,519 1

Merge Join (cost=150,219,548.76..150,220,003.52 rows=12,964 width=9,625) (actual time=77.102..80.536 rows=1,519 loops=1)

  • Merge Cond: (activities_2.id = activities_1.id)
37. 0.908 76.263 ↓ 7.6 1,519 1

Sort (cost=150,094,877.29..150,094,877.79 rows=200 width=4) (actual time=75.898..76.263 rows=1,519 loops=1)

  • Sort Key: activities_2.id
  • Sort Method: quicksort Memory: 120kB
38. 2.169 75.355 ↓ 7.6 1,519 1

HashAggregate (cost=150,094,867.65..150,094,869.65 rows=200 width=4) (actual time=74.977..75.355 rows=1,519 loops=1)

  • Group Key: activities_2.id
39. 1.845 73.186 ↑ 3.8 5,088 1

Append (cost=0.00..150,094,818.97 rows=19,469 width=4) (actual time=19.046..73.186 rows=5,088 loops=1)

40. 61.367 61.367 ↓ 11.5 1,496 1

CTE Scan on activities activities_2 (cost=0.00..583.40 rows=130 width=4) (actual time=19.046..61.367 rows=1,496 loops=1)

  • Filter: (author_user_id = 5,314)
  • Rows Removed by Filter: 23
41. 0.068 0.591 ↓ 0.0 0 1

Hash Join (cost=681.06..1,074.05 rows=7 width=4) (actual time=0.591..0.591 rows=0 loops=1)

  • Hash Cond: (activity_operator_users.activity_id = activities_3.id)
42. 0.010 0.010 ↑ 83.0 2 1

Index Scan using index_activity_operator_users_on_user_id on activity_operator_users (cost=0.42..124.02 rows=166 width=4) (actual time=0.008..0.010 rows=2 loops=1)

  • Index Cond: (user_id = 5,314)
43. 0.092 0.513 ↑ 44.2 293 1

Hash (cost=518.58..518.58 rows=12,964 width=4) (actual time=0.513..0.513 rows=293 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 139kB
44. 0.421 0.421 ↑ 44.2 293 1

CTE Scan on activities activities_3 (cost=0.00..518.58 rows=12,964 width=4) (actual time=0.004..0.421 rows=293 loops=1)

  • Filter: approved
  • Rows Removed by Filter: 1,226
45. 0.361 0.361 ↑ 2.7 24 1

CTE Scan on activities activities_4 (cost=0.00..583.40 rows=65 width=4) (actual time=0.209..0.361 rows=24 loops=1)

  • Filter: (approved AND (operator_company_id = 29,138))
  • Rows Removed by Filter: 1,495
46. 0.539 0.539 ↓ 1.1 1,519 1

CTE Scan on activities activities_5 (cost=0.00..875.10 rows=1,426 width=4) (actual time=0.005..0.539 rows=1,519 loops=1)

  • Filter: (author_company_id = ANY ('{29138,30499,73382,73381,47701,30498,47700,71282,72817,30497,71283}'::integer[]))
47. 0.796 2.932 ↑ 8.5 1,519 1

Hash Join (cost=0.08..618.49 rows=12,964 width=4) (actual time=1.702..2.932 rows=1,519 loops=1)

  • Hash Cond: (activities_6.author_company_id = trusted_company_ids.id)
48. 0.445 0.445 ↑ 17.1 1,519 1

CTE Scan on activities activities_6 (cost=0.00..518.58 rows=25,929 width=8) (actual time=0.001..0.445 rows=1,519 loops=1)

49. 0.007 1.691 ↓ 6.0 12 1

Hash (cost=0.06..0.06 rows=2 width=4) (actual time=1.691..1.691 rows=12 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
50. 0.006 1.684 ↓ 6.0 12 1

Unique (cost=0.05..0.06 rows=2 width=4) (actual time=1.677..1.684 rows=12 loops=1)

51. 0.011 1.678 ↓ 6.0 12 1

Sort (cost=0.05..0.06 rows=2 width=4) (actual time=1.676..1.678 rows=12 loops=1)

  • Sort Key: trusted_company_ids.id
  • Sort Method: quicksort Memory: 25kB
52. 1.667 1.667 ↓ 6.0 12 1

CTE Scan on trusted_company_ids (cost=0.00..0.04 rows=2 width=4) (actual time=1.624..1.667 rows=12 loops=1)

53. 0.136 0.605 ↑ 12.2 265 1

Hash Join (cost=0.08..543.62 rows=3,241 width=4) (actual time=0.091..0.605 rows=265 loops=1)

  • Hash Cond: (activities_7.company_id = trusted_company_ids_1.id)
54. 0.443 0.443 ↑ 24.5 265 1

CTE Scan on activities activities_7 (cost=0.00..518.58 rows=6,482 width=8) (actual time=0.055..0.443 rows=265 loops=1)

  • Filter: (approved AND (NOT template))
  • Rows Removed by Filter: 1,254
55. 0.007 0.026 ↓ 6.0 12 1

Hash (cost=0.06..0.06 rows=2 width=4) (actual time=0.026..0.026 rows=12 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
56. 0.007 0.019 ↓ 6.0 12 1

Unique (cost=0.05..0.06 rows=2 width=4) (actual time=0.011..0.019 rows=12 loops=1)

57. 0.008 0.012 ↓ 6.0 12 1

Sort (cost=0.05..0.06 rows=2 width=4) (actual time=0.011..0.012 rows=12 loops=1)

  • Sort Key: trusted_company_ids_1.id
  • Sort Method: quicksort Memory: 25kB
58. 0.004 0.004 ↓ 6.0 12 1

CTE Scan on trusted_company_ids trusted_company_ids_1 (cost=0.00..0.04 rows=2 width=4) (actual time=0.000..0.004 rows=12 loops=1)

59. 0.490 4.586 ↑ 6.1 265 1

Nested Loop Semi Join (cost=0.00..150,089,759.20 rows=1,620 width=4) (actual time=1.411..4.586 rows=265 loops=1)

  • Join Filter: (activities_8.company_id = untrusted_company_ids.id)
  • Rows Removed by Join Filter: 1,727
60. 0.651 2.241 ↑ 12.2 265 1

CTE Scan on activities activities_8 (cost=0.00..150,082,302.62 rows=3,241 width=8) (actual time=0.066..2.241 rows=265 loops=1)

  • Filter: (approved AND (NOT template) AND (SubPlan 15))
  • Rows Removed by Filter: 1,254
61.          

SubPlan (for CTE Scan)

62. 0.000 1.590 ↑ 40,934.0 1 265

Append (cost=4,842.85..6,631.18 rows=40,934 width=4) (actual time=0.001..0.006 rows=1 loops=265)

63. 0.230 1.590 ↑ 39,893.0 1 265

Append (cost=4,842.85..5,640.71 rows=39,893 width=4) (actual time=0.001..0.006 rows=1 loops=265)

64.          

CTE owns

65. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=1)

66.          

CTE ups

67. 0.243 0.243 ↓ 0.0 0 1

CTE Scan on group_company_ids (cost=529.11..549.91 rows=1,040 width=4) (actual time=0.243..0.243 rows=0 loops=1)

68.          

CTE group_company_ids

69. 0.001 0.242 ↓ 0.0 0 1

Recursive Union (cost=0.02..529.11 rows=1,040 width=4) (actual time=0.242..0.242 rows=0 loops=1)

70. 0.232 0.233 ↓ 0.0 0 1

Seq Scan on company_relationships (cost=0.02..55.73 rows=10 width=4) (actual time=0.233..0.233 rows=0 loops=1)

  • Filter: (permission AND (member_company_id = ANY ($12)))
  • Rows Removed by Filter: 1,676
71.          

Initplan (for Seq Scan)

72. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on owns (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

73. 0.004 0.008 ↓ 0.0 0 1

Hash Join (cost=3.25..45.26 rows=103 width=4) (actual time=0.008..0.008 rows=0 loops=1)

  • Hash Cond: (r.member_company_id = g.group_company_id)
74. 0.002 0.002 ↑ 1,658.0 1 1

Seq Scan on company_relationships r (cost=0.00..34.76 rows=1,658 width=8) (actual time=0.002..0.002 rows=1 loops=1)

  • Filter: permission
75. 0.000 0.002 ↓ 0.0 0 1

Hash (cost=2.00..2.00 rows=100 width=4) (actual time=0.002..0.002 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
76. 0.002 0.002 ↓ 0.0 0 1

WorkTable Scan on group_company_ids g (cost=0.00..2.00 rows=100 width=4) (actual time=0.002..0.002 rows=0 loops=1)

77.          

CTE downs

78. 1.094 1.094 ↑ 4,316.9 9 1

CTE Scan on member_company_ids (cost=3,116.97..3,894.01 rows=38,852 width=4) (actual time=0.021..1.094 rows=9 loops=1)

79.          

CTE member_company_ids

80. 0.006 1.088 ↑ 4,316.9 9 1

Recursive Union (cost=20.82..3,116.97 rows=38,852 width=4) (actual time=0.020..1.088 rows=9 loops=1)

81. 0.206 0.208 ↑ 20.7 3 1

Seq Scan on company_relationships company_relationships_1 (cost=20.82..76.53 rows=62 width=4) (actual time=0.018..0.208 rows=3 loops=1)

  • Filter: (permission AND (group_company_id = ANY ($16)))
  • Rows Removed by Filter: 1,673
82.          

Initplan (for Seq Scan)

83. 0.000 0.002 ↑ 1,041.0 1 1

Append (cost=0.00..20.82 rows=1,041 width=4) (actual time=0.001..0.002 rows=1 loops=1)

84. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on owns owns_1 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1)

85. 0.000 0.000 ↓ 0.0 0 1

CTE Scan on ups (cost=0.00..20.80 rows=1,040 width=4) (actual time=0.000..0.000 rows=0 loops=1)

86. 0.380 0.874 ↑ 646.5 6 1

Hash Join (cost=20.15..226.34 rows=3,879 width=4) (actual time=0.860..0.874 rows=6 loops=1)

  • Hash Cond: (r_1.group_company_id = g_1.member_company_id)
87. 0.489 0.489 ↑ 1.0 1,623 1

Seq Scan on company_relationships r_1 (cost=0.00..34.76 rows=1,658 width=8) (actual time=0.002..0.489 rows=1,623 loops=1)

  • Filter: permission
  • Rows Removed by Filter: 18
88. 0.005 0.005 ↑ 206.7 3 1

Hash (cost=12.40..12.40 rows=620 width=4) (actual time=0.005..0.005 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
89. 0.000 0.000 ↑ 206.7 3 1

WorkTable Scan on member_company_ids g_1 (cost=0.00..12.40 rows=620 width=4) (actual time=0.000..0.000 rows=3 loops=1)

90. 0.000 0.000 ↑ 1.0 1 265

CTE Scan on owns owns_3 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=265)

91. 0.260 0.260 ↓ 0.0 0 20

CTE Scan on ups ups_1 (cost=0.00..20.80 rows=1,040 width=4) (actual time=0.013..0.013 rows=0 loops=20)

92. 1.100 1.100 ↑ 9,713.0 4 20

CTE Scan on downs (cost=0.00..777.04 rows=38,852 width=4) (actual time=0.001..0.055 rows=4 loops=20)

93. 0.000 0.000 ↓ 0.0 0

Append (cost=560.32..581.14 rows=1,041 width=4) (never executed)

94.          

CTE owns

95. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.01 rows=1 width=0) (never executed)

96.          

CTE ups

97. 0.000 0.000 ↓ 0.0 0

CTE Scan on group_company_ids group_company_ids_1 (cost=529.11..549.91 rows=1,040 width=4) (never executed)

98.          

CTE group_company_ids

99. 0.000 0.000 ↓ 0.0 0

Recursive Union (cost=0.02..529.11 rows=1,040 width=4) (never executed)

100. 0.000 0.000 ↓ 0.0 0

Seq Scan on company_relationships company_relationships_2 (cost=0.02..55.73 rows=10 width=4) (never executed)

  • Filter: (permission AND (member_company_id = ANY ($22)))
101.          

Initplan (for Seq Scan)

102. 0.000 0.000 ↓ 0.0 0

CTE Scan on owns owns_2 (cost=0.00..0.02 rows=1 width=4) (never executed)

103. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=3.25..45.26 rows=103 width=4) (never executed)

  • Hash Cond: (r_2.member_company_id = g_2.group_company_id)
104. 0.000 0.000 ↓ 0.0 0

Seq Scan on company_relationships r_2 (cost=0.00..34.76 rows=1,658 width=8) (never executed)

  • Filter: permission
105. 0.000 0.000 ↓ 0.0 0

Hash (cost=2.00..2.00 rows=100 width=4) (never executed)

106. 0.000 0.000 ↓ 0.0 0

WorkTable Scan on group_company_ids g_2 (cost=0.00..2.00 rows=100 width=4) (never executed)

107. 0.000 0.000 ↓ 0.0 0

CTE Scan on owns owns_4 (cost=0.00..0.02 rows=1 width=4) (never executed)

108. 0.000 0.000 ↓ 0.0 0

CTE Scan on ups ups_2 (cost=0.00..20.80 rows=1,040 width=4) (never executed)

109. 1.855 1.855 ↑ 12.9 8 265

CTE Scan on untrusted_company_ids (cost=0.00..2.06 rows=103 width=4) (actual time=0.005..0.007 rows=8 loops=265)

110. 0.002 0.360 ↓ 0.0 0 1

Hash Semi Join (cost=3.35..587.01 rows=16 width=4) (actual time=0.360..0.360 rows=0 loops=1)

  • Hash Cond: (activities_9.company_id = untrusted_company_ids_1.id)
111. 0.358 0.358 ↓ 0.0 0 1

CTE Scan on activities activities_9 (cost=0.00..583.40 rows=32 width=8) (actual time=0.358..0.358 rows=0 loops=1)

  • Filter: (approved AND (NOT template) AND ((type)::text = 'ActualActivity'::text))
  • Rows Removed by Filter: 1,519
112. 0.000 0.000 ↓ 0.0 0

Hash (cost=2.06..2.06 rows=103 width=4) (never executed)

113. 0.000 0.000 ↓ 0.0 0

CTE Scan on untrusted_company_ids untrusted_company_ids_1 (cost=0.00..2.06 rows=103 width=4) (never executed)

114. 0.624 2.305 ↑ 17.1 1,519 1

Materialize (cost=124,671.47..124,801.12 rows=25,929 width=9,625) (actual time=1.199..2.305 rows=1,519 loops=1)

115. 1.295 1.681 ↑ 17.1 1,519 1

Sort (cost=124,671.47..124,736.29 rows=25,929 width=9,625) (actual time=1.195..1.681 rows=1,519 loops=1)

  • Sort Key: activities_1.id
  • Sort Method: quicksort Memory: 837kB
116. 0.386 0.386 ↑ 17.1 1,519 1

CTE Scan on activities activities_1 (cost=0.00..518.58 rows=25,929 width=9,625) (actual time=0.001..0.386 rows=1,519 loops=1)

117.          

CTE job_stage_orders

118. 0.012 0.012 ↑ 25.0 4 1

Function Scan on unnest (cost=0.00..1.00 rows=100 width=40) (actual time=0.009..0.012 rows=4 loops=1)

119.          

CTE latest_job_stages

120. 1.180 13.143 ↓ 14.6 1,455 1

HashAggregate (cost=252.00..253.00 rows=100 width=12) (actual time=12.729..13.143 rows=1,455 loops=1)

  • Group Key: ja.job_id
121. 1.780 11.963 ↓ 14.9 1,488 1

Nested Loop (cost=3.68..251.50 rows=100 width=12) (actual time=0.081..11.963 rows=1,488 loops=1)

122. 1.096 7.145 ↓ 15.2 1,519 1

Hash Join (cost=3.25..9.00 rows=100 width=12) (actual time=0.044..7.145 rows=1,519 loops=1)

  • Hash Cond: ((pa.type)::text = jso.activity_type)
123. 6.017 6.017 ↓ 7.6 1,519 1

CTE Scan on permitted_activities pa (cost=0.00..4.00 rows=200 width=520) (actual time=0.000..6.017 rows=1,519 loops=1)

124. 0.006 0.032 ↑ 25.0 4 1

Hash (cost=2.00..2.00 rows=100 width=40) (actual time=0.032..0.032 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
125. 0.026 0.026 ↑ 25.0 4 1

CTE Scan on job_stage_orders jso (cost=0.00..2.00 rows=100 width=40) (actual time=0.010..0.026 rows=4 loops=1)

126. 3.038 3.038 ↑ 1.0 1 1,519

Index Scan using index_job_activities_on_activity_id on job_activities ja (cost=0.43..2.42 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1,519)

  • Index Cond: (activity_id = pa.id)
127.          

CTE latest_stage_activities

128. 0.095 4,009.441 ↓ 96.0 96 1

HashAggregate (cost=256.84..256.85 rows=1 width=4) (actual time=4,009.418..4,009.441 rows=96 loops=1)

  • Group Key: pa_1.id
129. 0.243 4,009.346 ↓ 96.0 96 1

Nested Loop (cost=7.35..256.84 rows=1 width=4) (actual time=2,706.442..4,009.346 rows=96 loops=1)

130. 367.894 4,004.711 ↓ 1,464.0 1,464 1

Nested Loop (cost=6.93..256.38 rows=1 width=12) (actual time=28.535..4,004.711 rows=1,464 loops=1)

  • Join Filter: (ljs.job_id = ja_1.job_id)
  • Rows Removed by Join Filter: 1,540,627
131. 460.307 476.871 ↓ 15,799.7 1,579,973 1

Hash Join (cost=6.50..13.62 rows=100 width=8) (actual time=14.372..476.871 rows=1,579,973 loops=1)

  • Hash Cond: (jso_1.ordering = ljs.latest_job_stage)
132. 1.388 2.226 ↓ 15.2 1,519 1

Hash Join (cost=3.25..9.00 rows=100 width=12) (actual time=0.023..2.226 rows=1,519 loops=1)

  • Hash Cond: ((pa_1.type)::text = jso_1.activity_type)
133. 0.831 0.831 ↓ 7.6 1,519 1

CTE Scan on permitted_activities pa_1 (cost=0.00..4.00 rows=200 width=520) (actual time=0.000..0.831 rows=1,519 loops=1)

134. 0.005 0.007 ↑ 25.0 4 1

Hash (cost=2.00..2.00 rows=100 width=40) (actual time=0.007..0.007 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
135. 0.002 0.002 ↑ 25.0 4 1

CTE Scan on job_stage_orders jso_1 (cost=0.00..2.00 rows=100 width=40) (actual time=0.000..0.002 rows=4 loops=1)

136. 0.425 14.338 ↓ 14.6 1,455 1

Hash (cost=2.00..2.00 rows=100 width=12) (actual time=14.338..14.338 rows=1,455 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 85kB
137. 13.913 13.913 ↓ 14.6 1,455 1

CTE Scan on latest_job_stages ljs (cost=0.00..2.00 rows=100 width=12) (actual time=12.731..13.913 rows=1,455 loops=1)

138. 3,159.946 3,159.946 ↑ 1.0 1 1,579,973

Index Scan using index_job_activities_on_activity_id on job_activities ja_1 (cost=0.43..2.42 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=1,579,973)

  • Index Cond: (activity_id = pa_1.id)
139. 4.392 4.392 ↓ 0.0 0 1,464

Index Scan using issues_pkey on jobs j (cost=0.42..0.45 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1,464)

  • Index Cond: (id = ja_1.job_id)
  • Filter: (NOT closed)
  • Rows Removed by Filter: 1
140.          

CTE prepared_activities

141. 0.543 4,087.841 ↑ 1.0 96 1

Hash Left Join (cost=0.03..4.42 rows=100 width=9,625) (actual time=4,087.334..4,087.841 rows=96 loops=1)

  • Hash Cond: (pa_2.id = lsa.id)
  • Filter: ((lsa.id IS NOT NULL) OR ((pa_2.type)::text = 'ActualActivity'::text))
  • Rows Removed by Filter: 1,392
142. 77.768 77.768 ↓ 14.9 1,488 1

CTE Scan on permitted_activities pa_2 (cost=0.00..4.00 rows=100 width=9,625) (actual time=77.143..77.768 rows=1,488 loops=1)

  • Filter: (NOT template)
  • Rows Removed by Filter: 31
143. 0.028 4,009.530 ↓ 96.0 96 1

Hash (cost=0.02..0.02 rows=1 width=4) (actual time=4,009.530..4,009.530 rows=96 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
144. 4,009.502 4,009.502 ↓ 96.0 96 1

CTE Scan on latest_stage_activities lsa (cost=0.00..0.02 rows=1 width=4) (actual time=4,009.419..4,009.502 rows=96 loops=1)

145.          

CTE prepared_activity_inputs

146. 0.224 0.720 ↑ 4.2 193 1

Nested Loop (cost=2.68..584.79 rows=819 width=175) (actual time=0.096..0.720 rows=193 loops=1)

147. 0.083 0.112 ↑ 1.0 96 1

HashAggregate (cost=2.25..3.25 rows=100 width=4) (actual time=0.084..0.112 rows=96 loops=1)

  • Group Key: prepared_activities.id
148. 0.029 0.029 ↑ 1.0 96 1

CTE Scan on prepared_activities (cost=0.00..2.00 rows=100 width=4) (actual time=0.001..0.029 rows=96 loops=1)

149. 0.384 0.384 ↑ 4.0 2 96

Index Scan using index_activity_inputs_on_activity_id on activity_inputs ai_1 (cost=0.43..5.74 rows=8 width=175) (actual time=0.003..0.004 rows=2 loops=96)

  • Index Cond: (activity_id = prepared_activities.id)
150.          

CTE prepared_activity_paddocks

151. 0.337 4,089.032 ↑ 1.3 542 1

Nested Loop (cost=2.68..830.45 rows=709 width=120) (actual time=4,088.103..4,089.032 rows=542 loops=1)

152. 0.094 4,088.119 ↑ 1.0 96 1

HashAggregate (cost=2.25..3.25 rows=100 width=4) (actual time=4,088.090..4,088.119 rows=96 loops=1)

  • Group Key: prepared_activities_1.id
153. 4,088.025 4,088.025 ↑ 1.0 96 1

CTE Scan on prepared_activities prepared_activities_1 (cost=0.00..2.00 rows=100 width=4) (actual time=4,087.338..4,088.025 rows=96 loops=1)

154. 0.576 0.576 ↑ 1.2 6 96

Index Scan using index_activity_paddocks_on_activity_id on activity_paddocks ap_1 (cost=0.43..8.20 rows=7 width=120) (actual time=0.003..0.006 rows=6 loops=96)

  • Index Cond: (activity_id = prepared_activities_1.id)
155. 1.296 4,109.253 ↑ 1.2 10 1

Sort (cost=194.53..194.56 rows=12 width=2,334) (actual time=4,109.253..4,109.253 rows=10 loops=1)

  • Sort Key: (((((('AIID'::text || (ai.id)::text) || 'APID'::text) || (ap.id)::text) || 'AID'::text) || (a.id)::text))
  • Sort Method: top-N heapsort Memory: 29kB
156. 4.028 4,107.957 ↓ 103.4 1,241 1

HashAggregate (cost=193.77..194.31 rows=12 width=2,334) (actual time=4,106.427..4,107.957 rows=1,241 loops=1)

  • Group Key: a.id, ai.id, ap.id, a.company_id, pad.property_id, ap.paddock_id, a.name, a.completed, a.completed_at, a.due_at, a.updated_at, a.created_at, p.name, cv.name, p.id, cv.id, sg.id, ca.id, sup.name, p.acvm_registration_number, ai.rate_user_scalar, ai.rate_user_units, ai.mix_method, p.substance, ap.area, aic.id, apc.id, ai.product_id, ai.crop_variety_id, ai.cost_activity_id
157. 1.338 4,103.929 ↓ 103.4 1,241 1

Nested Loop Left Join (cost=121.67..192.81 rows=12 width=2,334) (actual time=4,094.134..4,103.929 rows=1,241 loops=1)

158. 1.293 4,100.109 ↓ 103.4 1,241 1

Nested Loop Left Join (cost=121.25..166.61 rows=12 width=2,330) (actual time=4,094.124..4,100.109 rows=1,241 loops=1)

159. 0.847 4,096.334 ↓ 103.4 1,241 1

Hash Join (cost=120.82..137.80 rows=12 width=2,318) (actual time=4,094.114..4,096.334 rows=1,241 loops=1)

  • Hash Cond: (ap.activity_id = a.id)
160. 4,089.498 4,089.498 ↑ 1.3 542 1

CTE Scan on prepared_activity_paddocks ap (cost=0.00..14.18 rows=709 width=20) (actual time=4,088.104..4,089.498 rows=542 loops=1)

161. 0.170 5.989 ↓ 43.8 175 1

Hash (cost=120.77..120.77 rows=4 width=2,306) (actual time=5.989..5.989 rows=175 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 46kB
162. 0.122 5.819 ↓ 43.8 175 1

Nested Loop Left Join (cost=37.38..120.77 rows=4 width=2,306) (actual time=0.824..5.819 rows=175 loops=1)

163. 0.154 4.822 ↓ 58.3 175 1

Hash Join (cost=36.96..113.46 rows=3 width=2,297) (actual time=0.812..4.822 rows=175 loops=1)

  • Hash Cond: (ai.activity_id = a.id)
164. 0.142 4.582 ↓ 29.2 175 1

Nested Loop Left Join (cost=33.71..110.16 rows=6 width=1,740) (actual time=0.714..4.582 rows=175 loops=1)

165. 0.208 3.915 ↓ 29.2 175 1

Nested Loop Left Join (cost=33.28..96.72 rows=6 width=1,731) (actual time=0.701..3.915 rows=175 loops=1)

166. 0.207 3.707 ↓ 29.2 175 1

Nested Loop Left Join (cost=33.00..94.84 rows=6 width=1,731) (actual time=0.696..3.707 rows=175 loops=1)

167. 0.164 3.500 ↓ 29.2 175 1

Nested Loop Left Join (cost=32.72..89.88 rows=6 width=1,712) (actual time=0.693..3.500 rows=175 loops=1)

168. 0.226 2.986 ↓ 29.2 175 1

Nested Loop Left Join (cost=32.43..87.65 rows=6 width=1,698) (actual time=0.684..2.986 rows=175 loops=1)

169. 0.208 2.410 ↓ 29.2 175 1

Nested Loop (cost=32.14..82.77 rows=6 width=1,144) (actual time=0.674..2.410 rows=175 loops=1)

170. 0.171 1.677 ↓ 21.9 175 1

Hash Left Join (cost=31.71..63.45 rows=8 width=1,100) (actual time=0.664..1.677 rows=175 loops=1)

  • Hash Cond: (ai.cost_activity_id = ca.id)
  • Filter: ((ca.id IS NULL) OR (lower((ai.mix_method)::text) = ANY ('{volume,volume_per_area}'::text[])))
  • Rows Removed by Filter: 18
171. 0.950 0.950 ↑ 4.2 193 1

CTE Scan on prepared_activity_inputs ai (cost=0.00..16.38 rows=819 width=1,060) (actual time=0.098..0.950 rows=193 loops=1)

172. 0.281 0.556 ↑ 1.0 876 1

Hash (cost=20.76..20.76 rows=876 width=40) (actual time=0.556..0.556 rows=876 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 71kB
173. 0.275 0.275 ↑ 1.0 876 1

Seq Scan on cost_activities ca (cost=0.00..20.76 rows=876 width=40) (actual time=0.005..0.275 rows=876 loops=1)

174. 0.525 0.525 ↑ 1.0 1 175

Index Scan using index_activity_input_calculations_on_activity_input_id on activity_input_calculations aic (cost=0.43..2.41 rows=1 width=48) (actual time=0.003..0.003 rows=1 loops=175)

  • Index Cond: (activity_input_id = ai.id)
  • Filter: ((total_area IS NOT NULL) OR (total_count IS NOT NULL) OR (total_time IS NOT NULL) OR (total_volume IS NOT NULL) OR (total_weight IS NOT NULL))
175. 0.350 0.350 ↑ 1.0 1 175

Index Scan using products_pkey on products p (cost=0.29..0.80 rows=1 width=554) (actual time=0.002..0.002 rows=1 loops=175)

  • Index Cond: (ai.product_id = id)
176. 0.350 0.350 ↑ 1.0 1 175

Index Scan using companies_pkey on companies sup (cost=0.29..0.36 rows=1 width=22) (actual time=0.002..0.002 rows=1 loops=175)

  • Index Cond: (p.supplier_id = id)
177. 0.000 0.000 ↓ 0.0 0 175

Index Scan using crop_varieties_pkey on crop_varieties cv (cost=0.29..0.82 rows=1 width=19) (actual time=0.000..0.000 rows=0 loops=175)

  • Index Cond: (ai.crop_variety_id = id)
178. 0.000 0.000 ↓ 0.0 0 175

Index Only Scan using segments_pkey on segments sg (cost=0.28..0.30 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=175)

  • Index Cond: (id = cv.segment_id)
  • Heap Fetches: 1
179. 0.525 0.525 ↓ 0.0 0 175

Index Scan using index_tags_on_tag_aware_type_and_tag_aware_id on tags ait (cost=0.42..2.23 rows=1 width=13) (actual time=0.003..0.003 rows=0 loops=175)

  • Index Cond: (((tag_aware_type)::text = 'ActivityInput'::text) AND (tag_aware_id = ai.id))
180. 0.044 0.086 ↑ 1.0 96 1

Hash (cost=2.00..2.00 rows=100 width=557) (actual time=0.086..0.086 rows=96 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 17kB
181. 0.042 0.042 ↑ 1.0 96 1

CTE Scan on prepared_activities a (cost=0.00..2.00 rows=100 width=557) (actual time=0.001..0.042 rows=96 loops=1)

182. 0.875 0.875 ↑ 1.0 1 175

Index Scan using index_tags_on_tag_aware_type_and_tag_aware_id on tags at (cost=0.42..2.42 rows=1 width=13) (actual time=0.004..0.005 rows=1 loops=175)

  • Index Cond: (((tag_aware_type)::text = 'Activity'::text) AND (tag_aware_id = a.id))
183. 2.482 2.482 ↑ 1.0 1 1,241

Index Scan using index_activity_paddock_calculations_on_activity_paddock_id on activity_paddock_calculations apc (cost=0.43..2.39 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=1,241)

  • Index Cond: (activity_paddock_id = ap.id)
184. 2.482 2.482 ↑ 1.0 1 1,241

Index Scan using paddocks_pkey on paddocks pad (cost=0.42..2.17 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1,241)

  • Index Cond: (ap.paddock_id = id)
Planning time : 10.045 ms
Execution time : 4,111.333 ms