explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0wsH

Settings
# exclusive inclusive rows x rows loops node
1. 1,299.433 1,779.448 ↑ 1.0 10 1

Limit (cost=1,999,783.13..2,016,955.22 rows=10 width=98) (actual time=1,778.553..1,779.448 rows=10 loops=1)

  • Buffers: shared hit=100,507 read=29,067
  • Functions: 232
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 21.217 ms, Inlining 24.092 ms, Optimization 884.919 ms, Emission 554.376 ms, Total 1484.604 ms
2. 0.052 480.015 ↑ 647.2 10 1

Result (cost=1,999,783.13..13,113,558.37 rows=6,472 width=98) (actual time=479.121..480.015 rows=10 loops=1)

  • Buffers: shared hit=100,507 read=29,067
3. 5.362 340.993 ↑ 647.2 10 1

Sort (cost=1,999,783.13..1,999,799.31 rows=6,472 width=66) (actual time=340.989..340.993 rows=10 loops=1)

  • Sort Key: ((SubPlan 2)) DESC, hosts_v1_1.id
  • Sort Method: top-N heapsort Memory: 27kB
  • Buffers: shared hit=98,395 read=29,067
4. 30.285 335.631 ↓ 3.0 19,163 1

HashAggregate (cost=34,307.21..1,999,643.27 rows=6,472 width=66) (actual time=100.751..335.631 rows=19,163 loops=1)

  • Group Key: hosts_v1_1.id
  • Buffers: shared hit=98,395 read=29,067
5. 53.856 56.227 ↓ 3.0 19,163 1

Bitmap Heap Scan on hosts_v1_1 (cost=651.67..34,291.03 rows=6,472 width=58) (actual time=4.060..56.227 rows=19,163 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: 367
  • Heap Blocks: exact=12,016
  • Buffers: shared hit=1 read=12,071
6. 2.371 2.371 ↓ 1.0 19,531 1

Bitmap Index Scan on hosts_v1_1_account_index (cost=0.00..650.05 rows=19,417 width=0) (actual time=2.371..2.371 rows=19,531 loops=1)

  • Index Cond: ((account)::text = '729650'::text)
  • Buffers: shared read=55
7.          

SubPlan (for HashAggregate)

8. 19.163 249.119 ↑ 1.0 1 19,163

Aggregate (cost=303.65..303.66 rows=1 width=8) (actual time=0.013..0.013 rows=1 loops=19,163)

  • Buffers: shared hit=98,394 read=16,996
9. 8.887 229.956 ↑ 2.3 3 19,163

Nested Loop Anti Join (cost=291.22..303.63 rows=7 width=16) (actual time=0.010..0.012 rows=3 loops=19,163)

  • Join Filter: ((u0.system_uuid = v0.system_uuid) AND (u0.rule_id = v0.rule_id))
  • Buffers: shared hit=98,394 read=16,996
10. 138.554 172.467 ↑ 2.3 3 19,163

Index Only Scan using curreport_system_uuid_rule_id on api_currentreport v0 (cost=290.95..295.21 rows=7 width=20) (actual time=0.008..0.009 rows=3 loops=19,163)

  • Index Cond: (system_uuid = hosts_v1_1.id)
  • Filter: (NOT (hashed SubPlan 1))
  • Rows Removed by Filter: 7
  • Heap Fetches: 1
  • Buffers: shared hit=60,830 read=16,996
11.          

SubPlan (for Index Only Scan)

12. 0.247 33.913 ↑ 1.1 660 1

HashAggregate (cost=281.29..288.57 rows=728 width=4) (actual time=33.833..33.913 rows=660 loops=1)

  • Group Key: api_rule.id
  • Buffers: shared hit=239
13. 0.087 33.666 ↑ 1.0 728 1

Append (cost=0.00..279.47 rows=728 width=4) (actual time=33.066..33.666 rows=728 loops=1)

  • Buffers: shared hit=239
14. 33.428 33.428 ↑ 1.0 377 1

Seq Scan on api_rule (cost=0.00..241.05 rows=377 width=4) (actual time=33.065..33.428 rows=377 loops=1)

  • Filter: (NOT active)
  • Rows Removed by Filter: 1,028
  • Buffers: shared hit=227
15. 0.151 0.151 ↑ 1.0 351 1

Seq Scan on api_ack (cost=0.00..27.50 rows=351 width=4) (actual time=0.009..0.151 rows=351 loops=1)

  • Filter: ((branch_id IS NULL) AND ((account)::text = '729650'::text))
  • Rows Removed by Filter: 889
  • Buffers: shared hit=12
16. 29.820 48.602 ↓ 0.0 0 48,602

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

  • Buffers: shared hit=37,564
17. 18.782 18.782 ↓ 0.0 0 18,782

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

  • Index Cond: (system_uuid = hosts_v1_1.id)
  • Filter: ((account)::text = '729650'::text)
  • Buffers: shared hit=37,564
18.          

SubPlan (for Result)

19. 0.010 36.660 ↑ 1.0 1 10

Aggregate (cost=353.54..353.55 rows=1 width=8) (actual time=3.666..3.666 rows=1 loops=10)

  • Buffers: shared hit=509
20. 0.000 36.650 ↓ 0.0 0 10

Nested Loop Anti Join (cost=291.50..353.53 rows=1 width=16) (actual time=3.665..3.665 rows=0 loops=10)

  • Buffers: shared hit=509
21. 0.105 36.650 ↓ 0.0 0 10

Nested Loop (cost=291.22..346.35 rows=1 width=20) (actual time=3.665..3.665 rows=0 loops=10)

  • Buffers: shared hit=509
22. 0.792 36.470 ↓ 1.1 8 10

Index Only Scan using curreport_system_uuid_rule_id on api_currentreport v0_1 (cost=290.95..295.21 rows=7 width=20) (actual time=3.643..3.647 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=284
23.          

SubPlan (for Index Only Scan)

24. 0.277 35.678 ↑ 1.1 660 1

HashAggregate (cost=281.29..288.57 rows=728 width=4) (actual time=35.584..35.678 rows=660 loops=1)

  • Group Key: api_rule_1.id
  • Buffers: shared hit=239
25. 0.084 35.401 ↑ 1.0 728 1

Append (cost=0.00..279.47 rows=728 width=4) (actual time=34.770..35.401 rows=728 loops=1)

  • Buffers: shared hit=239
26. 35.139 35.139 ↑ 1.0 377 1

Seq Scan on api_rule api_rule_1 (cost=0.00..241.05 rows=377 width=4) (actual time=34.769..35.139 rows=377 loops=1)

  • Filter: (NOT active)
  • Rows Removed by Filter: 1,028
  • Buffers: shared hit=227
27. 0.178 0.178 ↑ 1.0 351 1

Seq Scan on api_ack api_ack_1 (cost=0.00..27.50 rows=351 width=4) (actual time=0.012..0.178 rows=351 loops=1)

  • Filter: ((branch_id IS NULL) AND ((account)::text = '729650'::text))
  • Rows Removed by Filter: 889
  • Buffers: shared hit=12
28. 0.075 0.075 ↓ 0.0 0 75

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

  • Index Cond: (id = v0_1.rule_id)
  • Filter: (total_risk = 4)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=225
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..3.73 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.020 34.100 ↑ 1.0 1 10

Aggregate (cost=353.67..353.68 rows=1 width=8) (actual time=3.409..3.410 rows=1 loops=10)

  • Buffers: shared hit=527
31. 0.013 34.080 ↑ 3.0 1 10

Nested Loop Anti Join (cost=291.50..353.67 rows=3 width=16) (actual time=3.402..3.408 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=527
32. 0.055 34.040 ↑ 3.0 1 10

Nested Loop (cost=291.22..345.31 rows=3 width=20) (actual time=3.399..3.404 rows=1 loops=10)

  • Buffers: shared hit=509
33. 0.715 33.910 ↓ 1.1 8 10

Index Only Scan using curreport_system_uuid_rule_id on api_currentreport v0_2 (cost=290.95..295.21 rows=7 width=20) (actual time=3.387..3.391 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=284
34.          

SubPlan (for Index Only Scan)

35. 0.240 33.195 ↑ 1.1 660 1

HashAggregate (cost=281.29..288.57 rows=728 width=4) (actual time=33.116..33.195 rows=660 loops=1)

  • Group Key: api_rule_2.id
  • Buffers: shared hit=239
36. 0.066 32.955 ↑ 1.0 728 1

Append (cost=0.00..279.47 rows=728 width=4) (actual time=32.355..32.955 rows=728 loops=1)

  • Buffers: shared hit=239
37. 32.754 32.754 ↑ 1.0 377 1

Seq Scan on api_rule api_rule_2 (cost=0.00..241.05 rows=377 width=4) (actual time=32.354..32.754 rows=377 loops=1)

  • Filter: (NOT active)
  • Rows Removed by Filter: 1,028
  • Buffers: shared hit=227
38. 0.135 0.135 ↑ 1.0 351 1

Seq Scan on api_ack api_ack_2 (cost=0.00..27.50 rows=351 width=4) (actual time=0.010..0.135 rows=351 loops=1)

  • Filter: ((branch_id IS NULL) AND ((account)::text = '729650'::text))
  • Rows Removed by Filter: 889
  • Buffers: shared hit=12
39. 0.075 0.075 ↓ 0.0 0 75

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

  • Index Cond: (id = v0_2.rule_id)
  • Filter: (total_risk = 3)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=225
40. 0.009 0.027 ↓ 0.0 0 9

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

  • Buffers: shared hit=18
41. 0.018 0.018 ↓ 0.0 0 9

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.002..0.002 rows=0 loops=9)

  • Index Cond: (system_uuid = hosts_v1_1.id)
  • Filter: ((account)::text = '729650'::text)
  • Buffers: shared hit=18
42. 0.010 32.950 ↑ 1.0 1 10

Aggregate (cost=353.69..353.70 rows=1 width=8) (actual time=3.295..3.295 rows=1 loops=10)

  • Buffers: shared hit=529
43. 0.040 32.940 ↓ 1.2 5 10

Nested Loop Anti Join (cost=291.50..353.68 rows=4 width=16) (actual time=3.281..3.294 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=529
44. 0.045 32.900 ↓ 1.2 5 10

Nested Loop (cost=291.22..345.31 rows=4 width=20) (actual time=3.278..3.290 rows=5 loops=10)

  • Buffers: shared hit=509
45. 0.679 32.780 ↓ 1.1 8 10

Index Only Scan using curreport_system_uuid_rule_id on api_currentreport v0_3 (cost=290.95..295.21 rows=7 width=20) (actual time=3.273..3.278 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=284
46.          

SubPlan (for Index Only Scan)

47. 0.238 32.101 ↑ 1.1 660 1

HashAggregate (cost=281.29..288.57 rows=728 width=4) (actual time=32.023..32.101 rows=660 loops=1)

  • Group Key: api_rule_3.id
  • Buffers: shared hit=239
48. 0.068 31.863 ↑ 1.0 728 1

Append (cost=0.00..279.47 rows=728 width=4) (actual time=31.248..31.863 rows=728 loops=1)

  • Buffers: shared hit=239
49. 31.648 31.648 ↑ 1.0 377 1

Seq Scan on api_rule api_rule_3 (cost=0.00..241.05 rows=377 width=4) (actual time=31.247..31.648 rows=377 loops=1)

  • Filter: (NOT active)
  • Rows Removed by Filter: 1,028
  • Buffers: shared hit=227
50. 0.147 0.147 ↑ 1.0 351 1

Seq Scan on api_ack api_ack_3 (cost=0.00..27.50 rows=351 width=4) (actual time=0.010..0.147 rows=351 loops=1)

  • Filter: ((branch_id IS NULL) AND ((account)::text = '729650'::text))
  • Rows Removed by Filter: 889
  • Buffers: shared hit=12
51. 0.075 0.075 ↑ 1.0 1 75

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

  • Index Cond: (id = v0_3.rule_id)
  • Filter: (total_risk = 2)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=225
52. 0.000 0.000 ↓ 0.0 0 47

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

  • 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.010 35.260 ↑ 1.0 1 10

Aggregate (cost=352.60..352.61 rows=1 width=8) (actual time=3.526..3.526 rows=1 loops=10)

  • Buffers: shared hit=547
55. 0.023 35.250 ↓ 2.0 2 10

Nested Loop Anti Join (cost=291.50..352.60 rows=1 width=16) (actual time=3.512..3.525 rows=2 loops=10)

  • Buffers: shared hit=547
56. 0.045 35.170 ↓ 2.0 2 10

Nested Loop (cost=291.22..345.41 rows=1 width=20) (actual time=3.506..3.517 rows=2 loops=10)

  • Buffers: shared hit=509
57. 0.672 35.050 ↓ 1.1 8 10

Index Only Scan using curreport_system_uuid_rule_id on api_currentreport v0_4 (cost=290.95..295.21 rows=7 width=20) (actual time=3.501..3.505 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=284
58.          

SubPlan (for Index Only Scan)

59. 0.319 34.378 ↑ 1.1 660 1

HashAggregate (cost=281.29..288.57 rows=728 width=4) (actual time=34.298..34.378 rows=660 loops=1)

  • Group Key: api_rule_4.id
  • Buffers: shared hit=239
60. 0.069 34.059 ↑ 1.0 728 1

Append (cost=0.00..279.47 rows=728 width=4) (actual time=33.393..34.059 rows=728 loops=1)

  • Buffers: shared hit=239
61. 33.850 33.850 ↑ 1.0 377 1

Seq Scan on api_rule api_rule_4 (cost=0.00..241.05 rows=377 width=4) (actual time=33.392..33.850 rows=377 loops=1)

  • Filter: (NOT active)
  • Rows Removed by Filter: 1,028
  • Buffers: shared hit=227
62. 0.140 0.140 ↑ 1.0 351 1

Seq Scan on api_ack api_ack_4 (cost=0.00..27.50 rows=351 width=4) (actual time=0.011..0.140 rows=351 loops=1)

  • Filter: ((branch_id IS NULL) AND ((account)::text = '729650'::text))
  • Rows Removed by Filter: 889
  • Buffers: shared hit=12
63. 0.075 0.075 ↓ 0.0 0 75

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

  • Index Cond: (id = v0_4.rule_id)
  • Filter: (total_risk = 1)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=225
64. 0.057 0.057 ↓ 0.0 0 19

Index Only Scan using api_hostack_rule_id_account_system_uuid_uindex on api_hostack u0_4 (cost=0.28..3.73 rows=1 width=20) (actual time=0.003..0.003 rows=0 loops=19)

  • 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=38
Planning time : 3.559 ms
Execution time : 1,798.795 ms