explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pjFt

Settings
# exclusive inclusive rows x rows loops node
1. 1,319.611 1,749.542 ↑ 1.0 10 1

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

  • Buffers: shared hit=129,574
  • Functions: 232
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 26.408 ms, Inlining 23.291 ms, Optimization 887.059 ms, Emission 578.414 ms, Total 1515.172 ms
2. 0.052 429.931 ↑ 647.2 10 1

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

  • Buffers: shared hit=129,574
3. 5.746 286.909 ↑ 647.2 10 1

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

  • Sort Key: ((SubPlan 2)) DESC, hosts_v1_1.id
  • Sort Method: top-N heapsort Memory: 27kB
  • Buffers: shared hit=127,462
4. 30.722 281.163 ↓ 3.0 19,163 1

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

  • Group Key: hosts_v1_1.id
  • Buffers: shared hit=127,462
5. 18.237 20.485 ↓ 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.047..20.485 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=12,072
6. 2.248 2.248 ↓ 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.248..2.248 rows=19,531 loops=1)

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

SubPlan (for HashAggregate)

8. 19.163 229.956 ↑ 1.0 1 19,163

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

  • Buffers: shared hit=115,390
9. 8.887 210.793 ↑ 2.3 3 19,163

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

  • Join Filter: ((u0.system_uuid = v0.system_uuid) AND (u0.rule_id = v0.rule_id))
  • Buffers: shared hit=115,390
10. 118.024 153.304 ↑ 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.006..0.008 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=77,826
11.          

SubPlan (for Index Only Scan)

12. 0.254 35.280 ↑ 1.1 660 1

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

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

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

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

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

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

Seq Scan on api_ack (cost=0.00..27.50 rows=351 width=4) (actual time=0.010..0.139 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.000 36.540 ↑ 1.0 1 10

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

  • Buffers: shared hit=509
20. 0.010 36.540 ↓ 0.0 0 10

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

  • Buffers: shared hit=509
21. 0.045 36.530 ↓ 0.0 0 10

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

  • Buffers: shared hit=509
22. 0.771 36.410 ↓ 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.637..3.641 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.250 35.639 ↑ 1.1 660 1

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

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

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

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

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

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

Seq Scan on api_ack api_ack_1 (cost=0.00..27.50 rows=351 width=4) (actual time=0.010..0.146 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.010 36.300 ↑ 1.0 1 10

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

  • Buffers: shared hit=527
31. 0.022 36.290 ↑ 3.0 1 10

Nested Loop Anti Join (cost=291.50..353.67 rows=3 width=16) (actual time=3.623..3.629 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.045 36.250 ↑ 3.0 1 10

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

  • Buffers: shared hit=509
33. 0.663 36.130 ↓ 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.609..3.613 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.258 35.467 ↑ 1.1 660 1

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

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

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

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

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

  • Filter: (NOT active)
  • Rows Removed by Filter: 1,028
  • Buffers: shared hit=227
38. 0.145 0.145 ↑ 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.145 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.000 0.018 ↓ 0.0 0 9

Materialize (cost=0.28..8.30 rows=1 width=20) (actual time=0.002..0.002 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 34.080 ↑ 1.0 1 10

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

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

Nested Loop Anti Join (cost=291.50..353.68 rows=4 width=16) (actual time=3.392..3.407 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.000 34.030 ↓ 1.2 5 10

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

  • Buffers: shared hit=509
45. 0.654 33.880 ↓ 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.384..3.388 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.249 33.226 ↑ 1.1 660 1

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

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

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

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

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

  • Filter: (NOT active)
  • Rows Removed by Filter: 1,028
  • Buffers: shared hit=227
50. 0.148 0.148 ↑ 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.148 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.150 0.150 ↑ 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.002..0.002 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 36.050 ↑ 1.0 1 10

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

  • Buffers: shared hit=547
55. 0.012 36.040 ↓ 2.0 2 10

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

  • Buffers: shared hit=547
56. 0.075 35.990 ↓ 2.0 2 10

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

  • Buffers: shared hit=509
57. 0.669 35.840 ↓ 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.580..3.584 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.249 35.171 ↑ 1.1 660 1

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

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

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

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

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

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

Seq Scan on api_ack api_ack_4 (cost=0.00..27.50 rows=351 width=4) (actual time=0.039..0.180 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.038 0.038 ↓ 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.002..0.002 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 : 2.928 ms
Execution time : 1,774.391 ms