explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vAnf

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 3,997.677 ↑ 1.0 10 1

Limit (cost=2,036,934.32..2,057,519.65 rows=10 width=98) (actual time=3,996.996..3,997.677 rows=10 loops=1)

2. 0.024 3,997.675 ↑ 632.6 10 1

Result (cost=2,036,934.32..15,059,213.35 rows=6,326 width=98) (actual time=3,996.994..3,997.675 rows=10 loops=1)

3. 3.162 3,993.611 ↑ 632.6 10 1

Sort (cost=2,036,934.32..2,036,950.13 rows=6,326 width=66) (actual time=3,993.610..3,993.611 rows=10 loops=1)

  • Sort Key: ((SubPlan 2)) DESC, hosts_v1_1.id
  • Sort Method: top-N heapsort Memory: 27kB
4. 27.923 3,990.449 ↓ 3.0 19,244 1

HashAggregate (cost=26,458.08..2,036,797.62 rows=6,326 width=66) (actual time=3,842.742..3,990.449 rows=19,244 loops=1)

  • Group Key: hosts_v1_1.id
5. 3,689.529 3,827.818 ↓ 3.0 19,244 1

Bitmap Heap Scan on hosts_v1_1 (cost=1,304.33..26,442.26 rows=6,326 width=58) (actual time=140.087..3,827.818 rows=19,244 loops=1)

  • Recheck Cond: ((account)::text = '729650'::text)
  • Filter: ((stale_timestamp + '7 days'::interval) > '2020-10-11 18:11:14.915261+00'::timestamp with time zone)
  • Rows Removed by Filter: 121
  • Heap Blocks: exact=12,811
6. 138.289 138.289 ↓ 1.1 19,983 1

Bitmap Index Scan on hosts_v1_1_account_index (cost=0.00..1,302.75 rows=18,977 width=0) (actual time=138.289..138.289 rows=19,983 loops=1)

  • Index Cond: ((account)::text = '729650'::text)
7.          

SubPlan (for HashAggregate)

8. 0.000 134.708 ↑ 1.0 1 19,244

Aggregate (cost=317.77..317.78 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=19,244)

9. 38.488 134.708 ↑ 7.7 3 19,244

Nested Loop Anti Join (cost=300.37..317.71 rows=23 width=16) (actual time=0.005..0.007 rows=3 loops=19,244)

  • Join Filter: ((u0.system_uuid = v0.system_uuid) AND (u0.rule_id = v0.rule_id))
10. 95.285 96.220 ↑ 7.7 3 19,244

Index Only Scan using curreport_system_uuid_rule_id on api_currentreport v0 (cost=300.09..309.01 rows=23 width=20) (actual time=0.004..0.005 rows=3 loops=19,244)

  • Index Cond: (system_uuid = hosts_v1_1.id)
  • Filter: (NOT (hashed SubPlan 1))
  • Rows Removed by Filter: 7
  • Heap Fetches: 370
11.          

SubPlan (for Index Only Scan)

12. 0.216 0.935 ↑ 1.1 660 1

HashAggregate (cost=290.42..297.71 rows=729 width=4) (actual time=0.873..0.935 rows=660 loops=1)

  • Group Key: api_rule.id
13. 0.062 0.719 ↑ 1.0 729 1

Append (cost=0.00..288.60 rows=729 width=4) (actual time=0.005..0.719 rows=729 loops=1)

14. 0.487 0.487 ↑ 1.0 378 1

Seq Scan on api_rule (cost=0.00..250.05 rows=378 width=4) (actual time=0.005..0.487 rows=378 loops=1)

  • Filter: (NOT active)
  • Rows Removed by Filter: 1,027
15. 0.170 0.170 ↑ 1.0 351 1

Seq Scan on api_ack (cost=0.00..27.61 rows=351 width=4) (actual time=0.008..0.170 rows=351 loops=1)

  • Filter: ((branch_id IS NULL) AND ((account)::text = '729650'::text))
  • Rows Removed by Filter: 899
16. 0.000 0.000 ↓ 0.0 0 48,674

Materialize (cost=0.28..8.30 rows=1 width=20) (actual time=0.000..0.000 rows=0 loops=48,674)

17. 18.868 18.868 ↓ 0.0 0 18,868

Index Scan using hostack_system_uuid_rule_id on api_hostack u0 (cost=0.28..8.30 rows=1 width=20) (actual time=0.001..0.001 rows=0 loops=18,868)

  • Index Cond: (system_uuid = hosts_v1_1.id)
  • Filter: ((account)::text = '729650'::text)
18.          

SubPlan (for Result)

19. 0.010 1.230 ↑ 1.0 1 10

Aggregate (cost=433.11..433.12 rows=1 width=8) (actual time=0.123..0.123 rows=1 loops=10)

20. 0.000 1.220 ↓ 0.0 0 10

Nested Loop Anti Join (cost=300.64..433.11 rows=1 width=16) (actual time=0.122..0.122 rows=0 loops=10)

21. 0.043 1.220 ↓ 0.0 0 10

Nested Loop (cost=300.37..428.60 rows=1 width=20) (actual time=0.122..0.122 rows=0 loops=10)

22. 0.170 1.100 ↑ 2.9 8 10

Index Only Scan using curreport_system_uuid_rule_id on api_currentreport v0_1 (cost=300.09..309.01 rows=23 width=20) (actual time=0.107..0.110 rows=8 loops=10)

  • Index Cond: (system_uuid = hosts_v1_1.id)
  • Filter: (NOT (hashed SubPlan 3))
  • Rows Removed by Filter: 13
  • Heap Fetches: 0
23.          

SubPlan (for Index Only Scan)

24. 0.228 0.930 ↑ 1.1 660 1

HashAggregate (cost=290.42..297.71 rows=729 width=4) (actual time=0.866..0.930 rows=660 loops=1)

  • Group Key: api_rule_1.id
25. 0.062 0.702 ↑ 1.0 729 1

Append (cost=0.00..288.60 rows=729 width=4) (actual time=0.005..0.702 rows=729 loops=1)

26. 0.468 0.468 ↑ 1.0 378 1

Seq Scan on api_rule api_rule_1 (cost=0.00..250.05 rows=378 width=4) (actual time=0.005..0.468 rows=378 loops=1)

  • Filter: (NOT active)
  • Rows Removed by Filter: 1,027
27. 0.172 0.172 ↑ 1.0 351 1

Seq Scan on api_ack api_ack_1 (cost=0.00..27.61 rows=351 width=4) (actual time=0.006..0.172 rows=351 loops=1)

  • Filter: ((branch_id IS NULL) AND ((account)::text = '729650'::text))
  • Rows Removed by Filter: 899
28. 0.077 0.077 ↓ 0.0 0 77

Index Scan using api_rule_pkey on api_rule v2 (cost=0.28..5.17 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=77)

  • Index Cond: (id = v0_1.rule_id)
  • Filter: (total_risk = 4)
  • Rows Removed by Filter: 1
29. 0.000 0.000 ↓ 0.0 0

Index Only Scan using api_hostack_rule_id_account_system_uuid_uindex on api_hostack u0_1 (cost=0.28..2.39 rows=1 width=20) (never executed)

  • Index Cond: ((rule_id = v0_1.rule_id) AND (account = '729650'::text) AND (system_uuid = v0_1.system_uuid) AND (system_uuid = hosts_v1_1.id))
  • Heap Fetches: 0
30. 0.010 1.020 ↑ 1.0 1 10

Aggregate (cost=436.35..436.36 rows=1 width=8) (actual time=0.102..0.102 rows=1 loops=10)

31. 0.000 1.010 ↑ 9.0 1 10

Nested Loop Anti Join (cost=300.64..436.33 rows=9 width=16) (actual time=0.097..0.101 rows=1 loops=10)

  • Join Filter: ((u0_2.system_uuid = v0_2.system_uuid) AND (u0_2.rule_id = v0_2.rule_id))
32. 0.023 0.990 ↑ 9.0 1 10

Nested Loop (cost=300.37..427.87 rows=9 width=20) (actual time=0.095..0.099 rows=1 loops=10)

33. 0.150 0.890 ↑ 2.9 8 10

Index Only Scan using curreport_system_uuid_rule_id on api_currentreport v0_2 (cost=300.09..309.01 rows=23 width=20) (actual time=0.086..0.089 rows=8 loops=10)

  • Index Cond: (system_uuid = hosts_v1_1.id)
  • Filter: (NOT (hashed SubPlan 5))
  • Rows Removed by Filter: 13
  • Heap Fetches: 0
34.          

SubPlan (for Index Only Scan)

35. 0.215 0.740 ↑ 1.1 660 1

HashAggregate (cost=290.42..297.71 rows=729 width=4) (actual time=0.678..0.740 rows=660 loops=1)

  • Group Key: api_rule_2.id
36. 0.062 0.525 ↑ 1.0 729 1

Append (cost=0.00..288.60 rows=729 width=4) (actual time=0.003..0.525 rows=729 loops=1)

37. 0.321 0.321 ↑ 1.0 378 1

Seq Scan on api_rule api_rule_2 (cost=0.00..250.05 rows=378 width=4) (actual time=0.002..0.321 rows=378 loops=1)

  • Filter: (NOT active)
  • Rows Removed by Filter: 1,027
38. 0.142 0.142 ↑ 1.0 351 1

Seq Scan on api_ack api_ack_2 (cost=0.00..27.61 rows=351 width=4) (actual time=0.003..0.142 rows=351 loops=1)

  • Filter: ((branch_id IS NULL) AND ((account)::text = '729650'::text))
  • Rows Removed by Filter: 899
39. 0.077 0.077 ↓ 0.0 0 77

Index Scan using api_rule_pkey on api_rule v2_1 (cost=0.28..5.17 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=77)

  • Index Cond: (id = v0_2.rule_id)
  • Filter: (total_risk = 3)
  • Rows Removed by Filter: 1
40. 0.010 0.020 ↓ 0.0 0 10

Materialize (cost=0.28..8.30 rows=1 width=20) (actual time=0.002..0.002 rows=0 loops=10)

41. 0.010 0.010 ↓ 0.0 0 10

Index Scan using hostack_system_uuid_rule_id on api_hostack u0_2 (cost=0.28..8.30 rows=1 width=20) (actual time=0.001..0.001 rows=0 loops=10)

  • Index Cond: (system_uuid = hosts_v1_1.id)
  • Filter: ((account)::text = '729650'::text)
42. 0.010 0.900 ↑ 1.0 1 10

Aggregate (cost=436.41..436.42 rows=1 width=8) (actual time=0.090..0.090 rows=1 loops=10)

43. 0.030 0.890 ↑ 2.4 5 10

Nested Loop Anti Join (cost=300.64..436.38 rows=12 width=16) (actual time=0.077..0.089 rows=5 loops=10)

  • Join Filter: ((u0_3.system_uuid = v0_3.system_uuid) AND (u0_3.rule_id = v0_3.rule_id))
44. 0.023 0.860 ↑ 2.4 5 10

Nested Loop (cost=300.37..427.87 rows=12 width=20) (actual time=0.075..0.086 rows=5 loops=10)

45. 0.144 0.760 ↑ 2.9 8 10

Index Only Scan using curreport_system_uuid_rule_id on api_currentreport v0_3 (cost=300.09..309.01 rows=23 width=20) (actual time=0.073..0.076 rows=8 loops=10)

  • Index Cond: (system_uuid = hosts_v1_1.id)
  • Filter: (NOT (hashed SubPlan 7))
  • Rows Removed by Filter: 13
  • Heap Fetches: 0
46.          

SubPlan (for Index Only Scan)

47. 0.209 0.616 ↑ 1.1 660 1

HashAggregate (cost=290.42..297.71 rows=729 width=4) (actual time=0.555..0.616 rows=660 loops=1)

  • Group Key: api_rule_3.id
48. 0.058 0.407 ↑ 1.0 729 1

Append (cost=0.00..288.60 rows=729 width=4) (actual time=0.002..0.407 rows=729 loops=1)

49. 0.207 0.207 ↑ 1.0 378 1

Seq Scan on api_rule api_rule_3 (cost=0.00..250.05 rows=378 width=4) (actual time=0.002..0.207 rows=378 loops=1)

  • Filter: (NOT active)
  • Rows Removed by Filter: 1,027
50. 0.142 0.142 ↑ 1.0 351 1

Seq Scan on api_ack api_ack_3 (cost=0.00..27.61 rows=351 width=4) (actual time=0.003..0.142 rows=351 loops=1)

  • Filter: ((branch_id IS NULL) AND ((account)::text = '729650'::text))
  • Rows Removed by Filter: 899
51. 0.077 0.077 ↑ 1.0 1 77

Index Scan using api_rule_pkey on api_rule v2_2 (cost=0.28..5.17 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=77)

  • Index Cond: (id = v0_3.rule_id)
  • Filter: (total_risk = 2)
  • Rows Removed by Filter: 0
52. 0.000 0.000 ↓ 0.0 0 51

Materialize (cost=0.28..8.30 rows=1 width=20) (actual time=0.000..0.000 rows=0 loops=51)

53. 0.010 0.010 ↓ 0.0 0 10

Index Scan using hostack_system_uuid_rule_id on api_hostack u0_3 (cost=0.28..8.30 rows=1 width=20) (actual time=0.001..0.001 rows=0 loops=10)

  • Index Cond: (system_uuid = hosts_v1_1.id)
  • Filter: ((account)::text = '729650'::text)
54. 0.010 0.890 ↑ 1.0 1 10

Aggregate (cost=434.84..434.85 rows=1 width=8) (actual time=0.089..0.089 rows=1 loops=10)

55. 0.000 0.880 ↑ 1.0 2 10

Nested Loop Anti Join (cost=300.64..434.83 rows=2 width=16) (actual time=0.078..0.088 rows=2 loops=10)

56. 0.023 0.850 ↑ 1.0 2 10

Nested Loop (cost=300.37..427.94 rows=2 width=20) (actual time=0.076..0.085 rows=2 loops=10)

57. 0.141 0.750 ↑ 2.9 8 10

Index Only Scan using curreport_system_uuid_rule_id on api_currentreport v0_4 (cost=300.09..309.01 rows=23 width=20) (actual time=0.072..0.075 rows=8 loops=10)

  • Index Cond: (system_uuid = hosts_v1_1.id)
  • Filter: (NOT (hashed SubPlan 9))
  • Rows Removed by Filter: 13
  • Heap Fetches: 0
58.          

SubPlan (for Index Only Scan)

59. 0.206 0.609 ↑ 1.1 660 1

HashAggregate (cost=290.42..297.71 rows=729 width=4) (actual time=0.548..0.609 rows=660 loops=1)

  • Group Key: api_rule_4.id
60. 0.061 0.403 ↑ 1.0 729 1

Append (cost=0.00..288.60 rows=729 width=4) (actual time=0.002..0.403 rows=729 loops=1)

61. 0.199 0.199 ↑ 1.0 378 1

Seq Scan on api_rule api_rule_4 (cost=0.00..250.05 rows=378 width=4) (actual time=0.002..0.199 rows=378 loops=1)

  • Filter: (NOT active)
  • Rows Removed by Filter: 1,027
62. 0.143 0.143 ↑ 1.0 351 1

Seq Scan on api_ack api_ack_4 (cost=0.00..27.61 rows=351 width=4) (actual time=0.003..0.143 rows=351 loops=1)

  • Filter: ((branch_id IS NULL) AND ((account)::text = '729650'::text))
  • Rows Removed by Filter: 899
63. 0.077 0.077 ↓ 0.0 0 77

Index Scan using api_rule_pkey on api_rule v2_3 (cost=0.28..5.17 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=77)

  • Index Cond: (id = v0_4.rule_id)
  • Filter: (total_risk = 1)
  • Rows Removed by Filter: 1
64. 0.032 0.032 ↓ 0.0 0 16

Index Only Scan using api_hostack_rule_id_account_system_uuid_uindex on api_hostack u0_4 (cost=0.28..2.39 rows=1 width=20) (actual time=0.002..0.002 rows=0 loops=16)

  • Index Cond: ((rule_id = v0_4.rule_id) AND (account = '729650'::text) AND (system_uuid = v0_4.system_uuid) AND (system_uuid = hosts_v1_1.id))
  • Heap Fetches: 0
Planning time : 2.001 ms
Execution time : 3,997.926 ms