explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LAOT

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 185.940 ↑ 1.0 10 1

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

2. 0.027 185.938 ↑ 632.6 10 1

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

3. 3.323 181.871 ↑ 632.6 10 1

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

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

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

  • Group Key: hosts_v1_1.id
5. 19.851 22.722 ↓ 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=4.659..22.722 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. 2.871 2.871 ↓ 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=2.871..2.871 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.288 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.205 0.932 ↑ 1.1 660 1

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

  • Group Key: api_rule.id
13. 0.081 0.727 ↑ 1.0 729 1

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

14. 0.478 0.478 ↑ 1.0 378 1

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

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

Seq Scan on api_ack (cost=0.00..27.61 rows=351 width=4) (actual time=0.005..0.168 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.000 1.210 ↑ 1.0 1 10

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

20. 0.000 1.210 ↓ 0.0 0 10

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

21. 0.043 1.210 ↓ 0.0 0 10

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

22. 0.166 1.090 ↑ 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.105..0.109 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.214 0.924 ↑ 1.1 660 1

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

  • Group Key: api_rule_1.id
25. 0.075 0.710 ↑ 1.0 729 1

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

26. 0.469 0.469 ↑ 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.469 rows=378 loops=1)

  • Filter: (NOT active)
  • Rows Removed by Filter: 1,027
27. 0.166 0.166 ↑ 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.166 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.010 ↑ 1.0 1 10

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

31. 0.010 1.000 ↑ 9.0 1 10

Nested Loop Anti Join (cost=300.64..436.33 rows=9 width=16) (actual time=0.096..0.100 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.970 ↑ 9.0 1 10

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

33. 0.150 0.870 ↑ 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.084..0.087 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.212 0.720 ↑ 1.1 660 1

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

  • Group Key: api_rule_2.id
36. 0.071 0.508 ↑ 1.0 729 1

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

37. 0.293 0.293 ↑ 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.293 rows=378 loops=1)

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

Seq Scan on api_ack api_ack_2 (cost=0.00..27.61 rows=351 width=4) (actual time=0.004..0.144 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.920 ↑ 1.0 1 10

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

43. 0.030 0.910 ↑ 2.4 5 10

Nested Loop Anti Join (cost=300.64..436.38 rows=12 width=16) (actual time=0.079..0.091 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.880 ↑ 2.4 5 10

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

45. 0.154 0.780 ↑ 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.074..0.078 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.205 0.626 ↑ 1.1 660 1

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

  • Group Key: api_rule_3.id
48. 0.069 0.421 ↑ 1.0 729 1

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

49. 0.210 0.210 ↑ 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.210 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.000 0.900 ↑ 1.0 1 10

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

55. 0.008 0.900 ↑ 1.0 2 10

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

56. 0.023 0.860 ↑ 1.0 2 10

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

57. 0.143 0.760 ↑ 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.073..0.076 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.204 0.617 ↑ 1.1 660 1

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

  • Group Key: api_rule_4.id
60. 0.073 0.413 ↑ 1.0 729 1

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

61. 0.198 0.198 ↑ 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.198 rows=378 loops=1)

  • Filter: (NOT active)
  • Rows Removed by Filter: 1,027
62. 0.142 0.142 ↑ 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.142 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 : 1.912 ms
Execution time : 186.185 ms