explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6lPs : app_log

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.030 2,918.599 ↑ 1.0 10 1

Limit (cost=177,203.95..177,204.45 rows=10 width=164) (actual time=2,918.558..2,918.599 rows=10 loops=1)

2. 0.215 2,918.569 ↑ 7,398.0 70 1

GroupAggregate (cost=177,200.95..197,915.27 rows=517,858 width=164) (actual time=2,918.340..2,918.569 rows=70 loops=1)

  • Group Key: j1.username, t.""timestamp"", t.ip, t.backend_url, t.backend_body, t.backend_params, t.backend_query
3. 1,894.051 2,918.354 ↑ 6,813.9 76 1

Sort (cost=177,200.95..178,495.60 rows=517,858 width=180) (actual time=2,918.311..2,918.354 rows=76 loops=1)

  • Sort Key: j1.username, t.""timestamp"", t.ip, t.backend_url, t.backend_body, t.backend_params, t.backend_query
  • Sort Method: external merge Disk: 77184kB
4. 158.393 1,024.303 ↑ 1.2 431,573 1

Hash Left Join (cost=31,561.27..101,095.09 rows=517,858 width=180) (actual time=511.502..1,024.303 rows=431,573 loops=1)

  • Hash Cond: ((t.object_id = j2.id) AND ((t.object_type)::text = (j2.object_type)::text))
5. 245.114 354.474 ↑ 1.2 431,573 1

Hash Left Join (cost=1.27..20,314.33 rows=517,858 width=152) (actual time=0.056..354.474 rows=431,573 loops=1)

  • Hash Cond: (t.userid = j1.id)
6. 109.337 109.337 ↑ 1.2 431,573 1

Seq Scan on app_log t (cost=0.00..18,473.58 rows=517,858 width=148) (actual time=0.021..109.337 rows=431,573 loops=1)

7. 0.010 0.023 ↑ 1.0 12 1

Hash (cost=1.12..1.12 rows=12 width=12) (actual time=0.023..0.023 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
8. 0.013 0.013 ↑ 1.0 12 1

Seq Scan on nms_user j1 (cost=0.00..1.12 rows=12 width=12) (actual time=0.007..0.013 rows=12 loops=1)

9. 0.174 511.436 ↓ 1.3 454 1

Hash (cost=31,554.66..31,554.66 rows=356 width=154) (actual time=511.436..511.436 rows=454 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
10. 0.116 511.262 ↓ 1.3 454 1

Subquery Scan on j2 (cost=31,529.37..31,554.66 rows=356 width=154) (actual time=510.693..511.262 rows=454 loops=1)

11. 0.178 511.146 ↓ 1.3 454 1

Append (cost=31,529.37..31,551.10 rows=356 width=154) (actual time=510.692..511.146 rows=454 loops=1)

12. 0.019 510.699 ↑ 3.0 2 1

Hash Join (cost=31,529.37..31,530.52 rows=6 width=136) (actual time=510.692..510.699 rows=2 loops=1)

  • Hash Cond: (j3.field_id = j2_1.id)
13. 0.009 510.660 ↑ 3.0 2 1

Hash Left Join (cost=31,528.10..31,529.23 rows=6 width=22) (actual time=510.654..510.660 rows=2 loops=1)

  • Hash Cond: (j3.id = foo.entity_id)
14. 0.016 0.016 ↑ 3.0 2 1

Seq Scan on device j3 (cost=0.00..1.10 rows=6 width=22) (actual time=0.011..0.016 rows=2 loops=1)

  • Filter: (device_name IS NOT NULL)
  • Rows Removed by Filter: 9
15. 0.013 510.635 ↓ 2.0 2 1

Hash (cost=31,528.09..31,528.09 rows=1 width=4) (actual time=510.635..510.635 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
16. 0.003 510.622 ↓ 2.0 2 1

Subquery Scan on foo (cost=31,526.97..31,528.09 rows=1 width=4) (actual time=510.594..510.622 rows=2 loops=1)

  • Filter: (foo.rank = 1)
17. 0.036 510.619 ↑ 16.0 2 1

WindowAgg (cost=31,526.97..31,527.69 rows=32 width=61) (actual time=510.592..510.619 rows=2 loops=1)

18. 0.009 510.583 ↑ 16.0 2 1

Sort (cost=31,526.97..31,527.05 rows=32 width=21) (actual time=510.582..510.583 rows=2 loops=1)

  • Sort Key: faa.entity_id, faa.""time"" DESC
  • Sort Method: quicksort Memory: 25kB
19. 0.002 510.574 ↑ 16.0 2 1

Subquery Scan on faa (cost=31,525.13..31,526.17 rows=32 width=21) (actual time=510.570..510.574 rows=2 loops=1)

20. 0.011 510.572 ↑ 16.0 2 1

GroupAggregate (cost=31,525.13..31,525.85 rows=32 width=53) (actual time=510.569..510.572 rows=2 loops=1)

  • Group Key: metric_log.entity_id, metric_log.entity_type, (time_bucket('00:01:00'::interval, metric_log.""time""))
21. 0.017 510.561 ↑ 5.3 6 1

Sort (cost=31,525.13..31,525.21 rows=32 width=21) (actual time=510.560..510.561 rows=6 loops=1)

  • Sort Key: metric_log.entity_id, (time_bucket('00:01:00'::interval, metric_log.""time""))
  • Sort Method: quicksort Memory: 25kB
22. 509.632 510.544 ↑ 5.3 6 1

Bitmap Heap Scan on metric_log (cost=4.25..31,524.33 rows=32 width=21) (actual time=510.499..510.544 rows=6 loops=1)

  • Recheck Cond: ((entity_type)::text = 'DEVICE'::text)
  • Rows Removed by Index Recheck: 2167220
  • Filter: (""time"" > (now() - '00:05:00'::interval))
  • Rows Removed by Filter: 336087
  • Heap Blocks: lossy=23530
23. 0.912 0.912 ↑ 1.7 235,520 1

Bitmap Index Scan on metric_log_entity_type_bri_idx (cost=0.00..4.24 rows=399,500 width=0) (actual time=0.912..0.912 rows=235,520 loops=1)

  • Index Cond: ((entity_type)::text = 'DEVICE'::text)
24. 0.007 0.020 ↓ 1.1 13 1

Hash (cost=1.12..1.12 rows=12 width=4) (actual time=0.020..0.020 rows=13 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
25. 0.013 0.013 ↓ 1.1 13 1

Seq Scan on field j2_1 (cost=0.00..1.12 rows=12 width=4) (actual time=0.009..0.013 rows=13 loops=1)

26. 0.084 0.084 ↓ 1.1 80 1

Seq Scan on component (cost=0.00..3.23 rows=70 width=146) (actual time=0.033..0.084 rows=80 loops=1)

27. 0.037 0.037 ↓ 1.0 89 1

Seq Scan on city (cost=0.00..1.87 rows=87 width=130) (actual time=0.011..0.037 rows=89 loops=1)

28. 0.010 0.010 ↓ 1.2 16 1

Seq Scan on region (cost=0.00..1.13 rows=13 width=240) (actual time=0.007..0.010 rows=16 loops=1)

29. 0.010 0.010 ↓ 1.1 12 1

Seq Scan on metric_event_rule (cost=0.00..1.11 rows=11 width=240) (actual time=0.007..0.010 rows=12 loops=1)

30. 0.015 0.015 ↓ 3.3 36 1

Seq Scan on dashboard (cost=0.00..1.11 rows=11 width=240) (actual time=0.008..0.015 rows=36 loops=1)

31. 0.026 0.026 ↓ 1.4 43 1

Seq Scan on discovery (cost=0.00..1.31 rows=31 width=134) (actual time=0.018..0.026 rows=43 loops=1)

32. 0.011 0.011 ↑ 1.0 12 1

Seq Scan on nms_user (cost=0.00..1.12 rows=12 width=130) (actual time=0.008..0.011 rows=12 loops=1)

33. 0.010 0.010 ↓ 2.8 17 1

Seq Scan on user_group (cost=0.00..1.06 rows=6 width=240) (actual time=0.007..0.010 rows=17 loops=1)

34. 0.018 0.018 ↓ 1.9 26 1

Seq Scan on user_role (cost=0.00..1.14 rows=14 width=240) (actual time=0.007..0.018 rows=26 loops=1)

35. 0.028 0.028 ↓ 1.3 105 1

Seq Scan on user_action (cost=0.00..1.81 rows=81 width=139) (actual time=0.007..0.028 rows=105 loops=1)

36. 0.007 0.007 ↑ 1.0 5 1

Seq Scan on device_group (cost=0.00..1.05 rows=5 width=240) (actual time=0.006..0.007 rows=5 loops=1)

37. 0.004 0.013 ↓ 1.2 11 1

Subquery Scan on "*SELECT* 13" (cost=0.00..1.18 rows=9 width=154) (actual time=0.008..0.013 rows=11 loops=1)

38. 0.009 0.009 ↓ 1.2 11 1

Seq Scan on interface_connection (cost=0.00..1.09 rows=9 width=154) (actual time=0.006..0.009 rows=11 loops=1)

Planning time : 2.960 ms
Execution time : 2,948.696 ms