explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pYp3

Settings
# exclusive inclusive rows x rows loops node
1. 798.688 3,891.214 ↑ 4.1 126,815 1

GroupAggregate (cost=219,186.31..239,892.83 rows=517,663 width=164) (actual time=2,695.573..3,891.214 rows=126,815 loops=1)

  • Group Key: j1.username, t.""timestamp"", t.ip, t.backend_url, t.backend_body, t.backend_params, t.backend_query
2. 2,208.763 3,092.526 ↑ 1.2 431,449 1

Sort (cost=219,186.31..220,480.46 rows=517,663 width=180) (actual time=2,695.547..3,092.526 rows=431,449 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: 77,152kB
3. 145.288 883.763 ↑ 1.2 431,449 1

Hash Left Join (cost=73,602.97..143,110.61 rows=517,663 width=180) (actual time=410.972..883.763 rows=431,449 loops=1)

  • Hash Cond: ((t.object_id = j2.id) AND ((t.object_type)::text = (j2.object_type)::text))
4. 230.391 327.544 ↑ 1.2 431,449 1

Hash Left Join (cost=1.27..20,306.69 rows=517,663 width=152) (actual time=0.033..327.544 rows=431,449 loops=1)

  • Hash Cond: (t.userid = j1.id)
5. 97.138 97.138 ↑ 1.2 431,449 1

Seq Scan on app_log t (cost=0.00..18,466.63 rows=517,663 width=148) (actual time=0.010..97.138 rows=431,449 loops=1)

6. 0.007 0.015 ↑ 1.0 12 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
7. 0.008 0.008 ↑ 1.0 12 1

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

8. 0.163 410.931 ↓ 1.3 454 1

Hash (cost=73,596.36..73,596.36 rows=356 width=154) (actual time=410.931..410.931 rows=454 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 35kB
9. 0.092 410.768 ↓ 1.3 454 1

Subquery Scan on j2 (cost=73,571.06..73,596.36 rows=356 width=154) (actual time=410.327..410.768 rows=454 loops=1)

10. 0.105 410.676 ↓ 1.3 454 1

Append (cost=73,571.06..73,592.80 rows=356 width=154) (actual time=410.326..410.676 rows=454 loops=1)

11. 0.017 410.337 ↑ 3.0 2 1

Hash Join (cost=73,571.06..73,572.21 rows=6 width=136) (actual time=410.325..410.337 rows=2 loops=1)

  • Hash Cond: (j3.field_id = j2_1.id)
12. 0.007 410.307 ↑ 3.0 2 1

Hash Left Join (cost=73,569.79..73,570.93 rows=6 width=22) (actual time=410.297..410.307 rows=2 loops=1)

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

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

  • Filter: (device_name IS NOT NULL)
  • Rows Removed by Filter: 9
14. 0.004 410.285 ↓ 2.0 2 1

Hash (cost=73,569.78..73,569.78 rows=1 width=4) (actual time=410.285..410.285 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
15. 0.002 410.281 ↓ 2.0 2 1

Subquery Scan on foo (cost=73,568.66..73,569.78 rows=1 width=4) (actual time=410.274..410.281 rows=2 loops=1)

  • Filter: (foo.rank = 1)
  • Rows Removed by Filter: 1
16. 0.013 410.279 ↑ 10.7 3 1

WindowAgg (cost=73,568.66..73,569.38 rows=32 width=61) (actual time=410.273..410.279 rows=3 loops=1)

17. 0.006 410.266 ↑ 10.7 3 1

Sort (cost=73,568.66..73,568.74 rows=32 width=21) (actual time=410.265..410.266 rows=3 loops=1)

  • Sort Key: faa.entity_id, faa.""time"" DESC
  • Sort Method: quicksort Memory: 25kB
18. 0.002 410.260 ↑ 10.7 3 1

Subquery Scan on faa (cost=73,566.82..73,567.86 rows=32 width=21) (actual time=410.255..410.260 rows=3 loops=1)

19. 0.010 410.258 ↑ 10.7 3 1

GroupAggregate (cost=73,566.82..73,567.54 rows=32 width=53) (actual time=410.254..410.258 rows=3 loops=1)

  • Group Key: metric_log.entity_id, metric_log.entity_type, (time_bucket('00:01:00'::interval, metric_log.""time""))
20. 0.017 410.248 ↑ 3.6 9 1

Sort (cost=73,566.82..73,566.90 rows=32 width=21) (actual time=410.247..410.248 rows=9 loops=1)

  • Sort Key: metric_log.entity_id, (time_bucket('00:01:00'::interval, metric_log.""time""))
  • Sort Method: quicksort Memory: 25kB
21. 410.231 410.231 ↑ 3.6 9 1

Seq Scan on metric_log (cost=0.00..73,566.02 rows=32 width=21) (actual time=410.146..410.231 rows=9 loops=1)

  • Filter: (((entity_type)::text = 'DEVICE'::text) AND (""time"" > (now() - '00:05:00'::interval)))
  • Rows Removed by Filter: 2,502,574
22. 0.005 0.013 ↓ 1.1 13 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
23. 0.008 0.008 ↓ 1.1 13 1

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

24. 0.068 0.068 ↓ 1.1 80 1

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

25. 0.024 0.024 ↓ 1.0 89 1

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

26. 0.014 0.014 ↓ 1.2 16 1

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

27. 0.008 0.008 ↓ 1.1 12 1

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

28. 0.012 0.012 ↓ 3.3 36 1

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

29. 0.024 0.024 ↓ 1.4 43 1

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

30. 0.010 0.010 ↑ 1.0 12 1

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

31. 0.008 0.008 ↓ 2.8 17 1

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

32. 0.010 0.010 ↓ 1.9 26 1

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

33. 0.037 0.037 ↓ 1.3 105 1

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

34. 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)

35. 0.004 0.012 ↓ 1.2 11 1

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

36. 0.008 0.008 ↓ 1.2 11 1

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

Planning time : 1.397 ms
Execution time : 3,928.446 ms