explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GKz4

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

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

  • Buffers: shared hit=149,364
2. 0.030 188.782 ↑ 632.6 10 1

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

  • Buffers: shared hit=149,364
3. 3.568 184.662 ↑ 632.6 10 1

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

  • Sort Key: ((SubPlan 2)) DESC, hosts_v1_1.id
  • Sort Method: top-N heapsort Memory: 27kB
  • Buffers: shared hit=147,164
4. 23.259 181.094 ↓ 3.0 19,244 1

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

  • Group Key: hosts_v1_1.id
  • Buffers: shared hit=147,164
5. 20.228 23.127 ↓ 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.657..23.127 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
  • Buffers: shared hit=13,210
6. 2.899 2.899 ↓ 1.1 19,984 1

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

  • Index Cond: ((account)::text = '729650'::text)
  • Buffers: shared hit=370
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)

  • Buffers: shared hit=133,954
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))
  • Buffers: shared hit=133,954
10. 95.253 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: 1,174
  • Buffers: shared hit=96,218
11.          

SubPlan (for Index Only Scan)

12. 0.219 0.967 ↑ 1.1 660 1

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

  • Group Key: api_rule.id
  • Buffers: shared hit=248
13. 0.071 0.748 ↑ 1.0 729 1

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

  • Buffers: shared hit=248
14. 0.508 0.508 ↑ 1.0 378 1

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

  • Filter: (NOT active)
  • Rows Removed by Filter: 1,027
  • Buffers: shared hit=236
15. 0.169 0.169 ↑ 1.0 351 1

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

  • Filter: ((branch_id IS NULL) AND ((account)::text = '729650'::text))
  • Rows Removed by Filter: 899
  • Buffers: shared hit=12
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)

  • Buffers: shared hit=37,736
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)
  • Buffers: shared hit=37,736
18.          

SubPlan (for Result)

19. 0.000 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)

  • Buffers: shared hit=532
20. 0.010 1.230 ↓ 0.0 0 10

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

  • Buffers: shared hit=532
21. 0.033 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)

  • Buffers: shared hit=532
22. 0.173 1.110 ↑ 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.111 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
  • Buffers: shared hit=301
23.          

SubPlan (for Index Only Scan)

24. 0.220 0.937 ↑ 1.1 660 1

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

  • Group Key: api_rule_1.id
  • Buffers: shared hit=248
25. 0.074 0.717 ↑ 1.0 729 1

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

  • Buffers: shared hit=248
26. 0.457 0.457 ↑ 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.457 rows=378 loops=1)

  • Filter: (NOT active)
  • Rows Removed by Filter: 1,027
  • Buffers: shared hit=236
27. 0.186 0.186 ↑ 1.0 351 1

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

  • Filter: ((branch_id IS NULL) AND ((account)::text = '729650'::text))
  • Rows Removed by Filter: 899
  • Buffers: shared hit=12
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
  • Buffers: shared hit=231
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)

  • Buffers: shared hit=552
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))
  • Buffers: shared hit=552
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)

  • Buffers: shared hit=532
33. 0.156 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.085..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
  • Buffers: shared hit=301
34.          

SubPlan (for Index Only Scan)

35. 0.217 0.734 ↑ 1.1 660 1

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

  • Group Key: api_rule_2.id
  • Buffers: shared hit=248
36. 0.069 0.517 ↑ 1.0 729 1

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

  • Buffers: shared hit=248
37. 0.305 0.305 ↑ 1.0 378 1

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

  • Filter: (NOT active)
  • Rows Removed by Filter: 1,027
  • Buffers: shared hit=236
38. 0.143 0.143 ↑ 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.143 rows=351 loops=1)

  • Filter: ((branch_id IS NULL) AND ((account)::text = '729650'::text))
  • Rows Removed by Filter: 899
  • Buffers: shared hit=12
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
  • Buffers: shared hit=231
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)

  • Buffers: shared hit=20
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)
  • Buffers: shared hit=20
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)

  • Buffers: shared hit=552
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))
  • Buffers: shared hit=552
44. 0.033 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)

  • Buffers: shared hit=532
45. 0.150 0.770 ↑ 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.077 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
  • Buffers: shared hit=301
46.          

SubPlan (for Index Only Scan)

47. 0.212 0.620 ↑ 1.1 660 1

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

  • Group Key: api_rule_3.id
  • Buffers: shared hit=248
48. 0.064 0.408 ↑ 1.0 729 1

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

  • Buffers: shared hit=248
49. 0.198 0.198 ↑ 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.198 rows=378 loops=1)

  • Filter: (NOT active)
  • Rows Removed by Filter: 1,027
  • Buffers: shared hit=236
50. 0.146 0.146 ↑ 1.0 351 1

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

  • Filter: ((branch_id IS NULL) AND ((account)::text = '729650'::text))
  • Rows Removed by Filter: 899
  • Buffers: shared hit=12
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
  • Buffers: shared hit=231
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)

  • Buffers: shared hit=20
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)
  • Buffers: shared hit=20
54. 0.000 0.920 ↑ 1.0 1 10

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

  • Buffers: shared hit=564
55. 0.008 0.920 ↑ 1.0 2 10

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

  • Buffers: shared hit=564
56. 0.023 0.880 ↑ 1.0 2 10

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

  • Buffers: shared hit=532
57. 0.149 0.780 ↑ 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.075..0.078 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
  • Buffers: shared hit=301
58.          

SubPlan (for Index Only Scan)

59. 0.220 0.631 ↑ 1.1 660 1

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

  • Group Key: api_rule_4.id
  • Buffers: shared hit=248
60. 0.064 0.411 ↑ 1.0 729 1

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

  • Buffers: shared hit=248
61. 0.202 0.202 ↑ 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.202 rows=378 loops=1)

  • Filter: (NOT active)
  • Rows Removed by Filter: 1,027
  • Buffers: shared hit=236
62. 0.145 0.145 ↑ 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.145 rows=351 loops=1)

  • Filter: ((branch_id IS NULL) AND ((account)::text = '729650'::text))
  • Rows Removed by Filter: 899
  • Buffers: shared hit=12
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
  • Buffers: shared hit=231
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
  • Buffers: shared hit=32
Planning time : 1.793 ms
Execution time : 189.028 ms