explain.depesz.com

PostgreSQL's explain analyze made readable

Result: c3w

Settings
# exclusive inclusive rows x rows loops node
1. 0.054 3,176.355 ↑ 1.0 1 1

Limit (cost=2,385.45..67,265.62 rows=1 width=80) (actual time=3,176.302..3,176.355 rows=1 loops=1)

2. 0.023 3,176.301 ↑ 325.0 1 1

Result (cost=2,385.45..21,088,441.17 rows=325 width=80) (actual time=3,176.300..3,176.301 rows=1 loops=1)

3. 1.654 3,098.464 ↑ 325.0 1 1

Sort (cost=2,385.45..2,386.26 rows=325 width=90) (actual time=3,098.464..3,098.464 rows=1 loops=1)

  • Sort Key: de.event_level DESC, x1.occurred_at DESC
  • Sort Method: top-N heapsort Memory: 25kB
4. 13.675 3,096.810 ↓ 5.2 1,682 1

Hash Join (cost=6.44..2,383.83 rows=325 width=90) (actual time=2.178..3,096.810 rows=1,682 loops=1)

  • Hash Cond: (x1.event_id = de.id)
5. 2.897 3,083.037 ↓ 5.2 1,682 1

Nested Loop Left Join (cost=0.57..2,376.25 rows=325 width=55) (actual time=2.049..3,083.037 rows=1,682 loops=1)

  • Join Filter: (application_device.device_id = x1.device_id)
6. 1.310 3,080.140 ↓ 5.2 1,682 1

Custom Scan (ChunkAppend) on event_data x1 (cost=0.15..2,366.46 rows=325 width=48) (actual time=2.034..3,080.140 rows=1,682 loops=1)

  • Chunks excluded during startup: 21
7. 3.846 3,078.830 ↓ 5.5 1,682 1

Index Scan using _hyper_38_1683_chunk_event_data_device_id_occurred_at_idx on _hyper_38_1683_chunk x1_1 (cost=0.43..2,267.64 rows=304 width=48) (actual time=2.033..3,078.830 rows=1,682 loops=1)

  • Index Cond: ((device_id = 9900) AND (occurred_at > (now() - '1 day'::interval)))
  • Filter: (SubPlan 2)
  • Rows Removed by Filter: 2
8.          

SubPlan (for Index Scan)

9. 1.684 3,074.984 ↑ 1.0 1 1,684

Limit (cost=0.15..5.14 rows=1 width=8) (actual time=1.826..1.826 rows=1 loops=1,684)

10. 6.736 3,073.300 ↑ 22.0 1 1,684

GroupAggregate (cost=0.15..109.96 rows=22 width=8) (actual time=1.825..1.825 rows=1 loops=1,684)

  • Group Key: x3.seq_id
  • Filter: (1 <> ALL (array_agg(x3.event_id)))
  • Rows Removed by Filter: 0
11. 1.684 3,066.564 ↑ 22.0 1 1,684

Custom Scan (ChunkAppend) on event_data x3 (cost=0.15..109.57 rows=22 width=12) (actual time=0.827..1.821 rows=1 loops=1,684)

  • Chunks excluded during startup: 21
12. 3,064.880 3,064.880 ↑ 1.0 1 1,684

Index Scan using _hyper_38_1683_chunk_event_data_occurred_at_seq_id_idx on _hyper_38_1683_chunk x3_1 (cost=0.43..57.71 rows=1 width=12) (actual time=0.826..1.820 rows=1 loops=1,684)

  • Index Cond: ((occurred_at > (now() - '1 day'::interval)) AND (seq_id = x1_1.seq_id))
13. 0.000 0.000 ↓ 0.0 0 1,682

Materialize (cost=0.42..4.91 rows=1 width=19) (actual time=0.000..0.000 rows=0 loops=1,682)

14. 0.001 0.011 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.42..4.91 rows=1 width=19) (actual time=0.010..0.011 rows=0 loops=1)

15. 0.010 0.010 ↓ 0.0 0 1

Index Scan using application_device_device_id_key on application_device (cost=0.27..2.49 rows=1 width=16) (actual time=0.009..0.010 rows=0 loops=1)

  • Index Cond: (device_id = 9900)
16. 0.000 0.000 ↓ 0.0 0

Index Scan using application_location_pkey on application_location (cost=0.14..2.36 rows=1 width=15) (never executed)

  • Index Cond: (id = application_device.application_location_id)
17. 0.046 0.098 ↓ 1.0 85 1

Hash (cost=4.83..4.83 rows=83 width=38) (actual time=0.097..0.098 rows=85 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
18. 0.052 0.052 ↓ 1.0 85 1

Seq Scan on default_event de (cost=0.00..4.83 rows=83 width=38) (actual time=0.005..0.052 rows=85 loops=1)

19.          

SubPlan (for Result)

20. 0.070 77.814 ↑ 1.0 1 1

Aggregate (cost=64,880.14..64,880.15 rows=1 width=32) (actual time=77.813..77.814 rows=1 loops=1)

21. 0.046 77.744 ↑ 8.0 2 1

Nested Loop (cost=8.28..64,879.90 rows=16 width=274) (actual time=77.665..77.744 rows=2 loops=1)

  • Join Filter: (company_device.device_type_id = device_type.id)
  • Rows Removed by Join Filter: 54
22. 0.026 0.026 ↑ 1.7 28 1

Seq Scan on device_type (cost=0.00..2.48 rows=48 width=107) (actual time=0.009..0.026 rows=28 loops=1)

23. 0.034 77.672 ↑ 8.0 2 28

Materialize (cost=8.28..64,865.94 rows=16 width=175) (actual time=2.736..2.774 rows=2 loops=28)

24. 1.021 77.638 ↑ 8.0 2 1

Nested Loop (cost=8.28..64,865.86 rows=16 width=175) (actual time=76.601..77.638 rows=2 loops=1)

  • Join Filter: (application_device_1.device_id = company_device.id)
  • Rows Removed by Join Filter: 1250
25. 0.245 0.245 ↑ 1.0 626 1

Seq Scan on company_device (cost=0.00..22.26 rows=626 width=16) (actual time=0.005..0.245 rows=626 loops=1)

26. 0.464 76.372 ↑ 8.0 2 626

Materialize (cost=8.28..64,693.40 rows=16 width=175) (actual time=0.121..0.122 rows=2 loops=626)

27. 0.465 75.908 ↑ 8.0 2 1

Nested Loop (cost=8.28..64,693.32 rows=16 width=175) (actual time=75.840..75.908 rows=2 loops=1)

  • Join Filter: (x2.device_id = application_device_1.id)
  • Rows Removed by Join Filter: 786
28. 0.189 0.189 ↑ 1.0 394 1

Seq Scan on application_device application_device_1 (cost=0.00..14.94 rows=394 width=16) (actual time=0.005..0.189 rows=394 loops=1)

29. 0.427 75.254 ↑ 8.0 2 394

Materialize (cost=8.28..64,583.86 rows=16 width=167) (actual time=0.190..0.191 rows=2 loops=394)

30. 0.141 74.827 ↑ 8.0 2 1

Nested Loop (cost=8.28..64,583.78 rows=16 width=167) (actual time=74.632..74.827 rows=2 loops=1)

  • Join Filter: (x2.event_id = de_resp.id)
  • Rows Removed by Join Filter: 168
31. 0.056 0.056 ↓ 1.0 85 1

Seq Scan on default_event de_resp (cost=0.00..4.83 rows=83 width=118) (actual time=0.005..0.056 rows=85 loops=1)

32. 0.079 74.630 ↑ 8.0 2 85

Materialize (cost=8.28..64,559.07 rows=16 width=53) (actual time=0.875..0.878 rows=2 loops=85)

33. 0.020 74.551 ↑ 8.0 2 1

Merge Join (cost=8.28..64,558.99 rows=16 width=53) (actual time=74.361..74.551 rows=2 loops=1)

  • Merge Cond: (auth_user.id = resp_pers.user_id)
34. 0.200 74.400 ↑ 12.5 2 1

Nested Loop (cost=0.29..67,240.00 rows=25 width=58) (actual time=74.213..74.400 rows=2 loops=1)

  • Join Filter: (auth_user.id = au2.id)
  • Rows Removed by Join Filter: 268
35. 0.085 0.085 ↓ 1.2 135 1

Index Only Scan using auth_user_id_key on auth_user au2 (cost=0.14..4.05 rows=114 width=8) (actual time=0.016..0.085 rows=135 loops=1)

  • Heap Fetches: 135
36. 0.128 74.115 ↑ 12.5 2 135

Materialize (cost=0.15..67,193.26 rows=25 width=50) (actual time=0.548..0.549 rows=2 loops=135)

37. 0.218 73.987 ↑ 12.5 2 1

Nested Loop (cost=0.15..67,193.14 rows=25 width=50) (actual time=73.935..73.987 rows=2 loops=1)

  • Join Filter: (x2.user_id = auth_user.id)
  • Rows Removed by Join Filter: 268
38. 0.059 0.059 ↓ 1.2 135 1

Seq Scan on auth_user (cost=0.00..4.14 rows=114 width=22) (actual time=0.004..0.059 rows=135 loops=1)

39. 0.120 73.710 ↑ 12.5 2 135

Materialize (cost=0.15..67,146.31 rows=25 width=28) (actual time=0.321..0.546 rows=2 loops=135)

40. 0.000 73.590 ↑ 12.5 2 1

Custom Scan (ChunkAppend) on event_data x2 (cost=0.15..67,146.19 rows=25 width=28) (actual time=43.324..73.590 rows=2 loops=1)

41. 0.007 0.007 ↓ 0.0 0 1

Index Scan using _hyper_38_17_chunk_event_data_occurred_at_seq_id_idx on _hyper_38_17_chunk x2_1 (cost=0.15..5.59 rows=1 width=28) (actual time=0.006..0.007 rows=0 loops=1)

  • Index Cond: (seq_id = x1.seq_id)
  • Filter: (event_id <> x1.event_id)
42. 0.010 0.010 ↓ 0.0 0 1

Index Scan using _hyper_38_18_chunk_event_data_occurred_at_seq_id_idx on _hyper_38_18_chunk x2_2 (cost=0.15..5.59 rows=1 width=28) (actual time=0.010..0.010 rows=0 loops=1)

  • Index Cond: (seq_id = x1.seq_id)
  • Filter: (event_id <> x1.event_id)
43. 0.004 0.007 ↓ 0.0 0 1

Bitmap Heap Scan on _hyper_38_19_chunk x2_3 (cost=4.02..6.16 rows=2 width=28) (actual time=0.006..0.007 rows=0 loops=1)

  • Recheck Cond: (seq_id = x1.seq_id)
  • Filter: (event_id <> x1.event_id)
44. 0.003 0.003 ↓ 0.0 0 1

Bitmap Index Scan on _hyper_38_19_chunk_event_data_occurred_at_seq_id_idx (cost=0.00..4.02 rows=2 width=0) (actual time=0.002..0.003 rows=0 loops=1)

  • Index Cond: (seq_id = x1.seq_id)
45. 0.006 0.006 ↓ 0.0 0 1

Index Scan using _hyper_38_20_chunk_event_data_occurred_at_seq_id_idx on _hyper_38_20_chunk x2_4 (cost=0.15..5.59 rows=1 width=28) (actual time=0.005..0.006 rows=0 loops=1)

  • Index Cond: (seq_id = x1.seq_id)
  • Filter: (event_id <> x1.event_id)
46. 0.002 0.004 ↓ 0.0 0 1

Bitmap Heap Scan on _hyper_38_21_chunk x2_5 (cost=4.02..6.16 rows=2 width=28) (actual time=0.004..0.004 rows=0 loops=1)

  • Recheck Cond: (seq_id = x1.seq_id)
  • Filter: (event_id <> x1.event_id)
47. 0.002 0.002 ↓ 0.0 0 1

Bitmap Index Scan on _hyper_38_21_chunk_event_data_occurred_at_seq_id_idx (cost=0.00..4.02 rows=2 width=0) (actual time=0.002..0.002 rows=0 loops=1)

  • Index Cond: (seq_id = x1.seq_id)
48. 0.008 0.008 ↓ 0.0 0 1

Index Scan using _hyper_38_22_chunk_event_data_occurred_at_seq_id_idx on _hyper_38_22_chunk x2_6 (cost=0.15..5.59 rows=1 width=28) (actual time=0.008..0.008 rows=0 loops=1)

  • Index Cond: (seq_id = x1.seq_id)
  • Filter: (event_id <> x1.event_id)
49. 0.008 0.008 ↓ 0.0 0 1

Index Scan using _hyper_38_1659_chunk_event_data_occurred_at_seq_id_idx on _hyper_38_1659_chunk x2_7 (cost=0.15..5.59 rows=1 width=28) (actual time=0.007..0.008 rows=0 loops=1)

  • Index Cond: (seq_id = x1.seq_id)
  • Filter: (event_id <> x1.event_id)
50. 0.006 0.006 ↓ 0.0 0 1

Index Scan using _hyper_38_1660_chunk_event_data_occurred_at_seq_id_idx on _hyper_38_1660_chunk x2_8 (cost=0.15..5.59 rows=1 width=28) (actual time=0.006..0.006 rows=0 loops=1)

  • Index Cond: (seq_id = x1.seq_id)
  • Filter: (event_id <> x1.event_id)
51. 0.006 0.006 ↓ 0.0 0 1

Index Scan using _hyper_38_1661_chunk_event_data_occurred_at_seq_id_idx on _hyper_38_1661_chunk x2_9 (cost=0.15..5.59 rows=1 width=28) (actual time=0.005..0.006 rows=0 loops=1)

  • Index Cond: (seq_id = x1.seq_id)
  • Filter: (event_id <> x1.event_id)
52. 0.007 0.007 ↓ 0.0 0 1

Index Scan using _hyper_38_1662_chunk_event_data_occurred_at_seq_id_idx on _hyper_38_1662_chunk x2_10 (cost=0.15..5.59 rows=1 width=28) (actual time=0.006..0.007 rows=0 loops=1)

  • Index Cond: (seq_id = x1.seq_id)
  • Filter: (event_id <> x1.event_id)
53. 0.008 0.008 ↓ 0.0 0 1

Index Scan using _hyper_38_1663_chunk_event_data_occurred_at_seq_id_idx on _hyper_38_1663_chunk x2_11 (cost=0.15..5.59 rows=1 width=28) (actual time=0.007..0.008 rows=0 loops=1)

  • Index Cond: (seq_id = x1.seq_id)
  • Filter: (event_id <> x1.event_id)
54. 0.006 0.006 ↓ 0.0 0 1

Index Scan using _hyper_38_1664_chunk_event_data_occurred_at_seq_id_idx on _hyper_38_1664_chunk x2_12 (cost=0.15..5.59 rows=1 width=28) (actual time=0.006..0.006 rows=0 loops=1)

  • Index Cond: (seq_id = x1.seq_id)
  • Filter: (event_id <> x1.event_id)
55. 0.006 0.006 ↓ 0.0 0 1

Index Scan using _hyper_38_1665_chunk_event_data_occurred_at_seq_id_idx on _hyper_38_1665_chunk x2_13 (cost=0.15..5.59 rows=1 width=28) (actual time=0.006..0.006 rows=0 loops=1)

  • Index Cond: (seq_id = x1.seq_id)
  • Filter: (event_id <> x1.event_id)
56. 0.006 0.006 ↓ 0.0 0 1

Index Scan using _hyper_38_1666_chunk_event_data_occurred_at_seq_id_idx on _hyper_38_1666_chunk x2_14 (cost=0.15..5.51 rows=1 width=28) (actual time=0.005..0.006 rows=0 loops=1)

  • Index Cond: (seq_id = x1.seq_id)
  • Filter: (event_id <> x1.event_id)
57. 0.002 0.004 ↓ 0.0 0 1

Bitmap Heap Scan on _hyper_38_1675_chunk x2_15 (cost=4.02..6.16 rows=2 width=28) (actual time=0.003..0.004 rows=0 loops=1)

  • Recheck Cond: (seq_id = x1.seq_id)
  • Filter: (event_id <> x1.event_id)
58. 0.002 0.002 ↓ 0.0 0 1

Bitmap Index Scan on _hyper_38_1675_chunk_event_data_occurred_at_seq_id_idx (cost=0.00..4.02 rows=2 width=0) (actual time=0.001..0.002 rows=0 loops=1)

  • Index Cond: (seq_id = x1.seq_id)
59. 0.008 0.008 ↓ 0.0 0 1

Index Scan using _hyper_38_1676_chunk_event_data_occurred_at_seq_id_idx on _hyper_38_1676_chunk x2_16 (cost=0.15..5.59 rows=1 width=28) (actual time=0.007..0.008 rows=0 loops=1)

  • Index Cond: (seq_id = x1.seq_id)
  • Filter: (event_id <> x1.event_id)
60. 0.007 0.007 ↓ 0.0 0 1

Index Scan using _hyper_38_1677_chunk_event_data_occurred_at_seq_id_idx on _hyper_38_1677_chunk x2_17 (cost=0.15..5.96 rows=1 width=28) (actual time=0.006..0.007 rows=0 loops=1)

  • Index Cond: (seq_id = x1.seq_id)
  • Filter: (event_id <> x1.event_id)
61. 0.009 0.009 ↓ 0.0 0 1

Index Scan using _hyper_38_1678_chunk_event_data_occurred_at_seq_id_idx on _hyper_38_1678_chunk x2_18 (cost=0.15..5.96 rows=1 width=28) (actual time=0.008..0.009 rows=0 loops=1)

  • Index Cond: (seq_id = x1.seq_id)
  • Filter: (event_id <> x1.event_id)
62. 0.007 0.007 ↓ 0.0 0 1

Index Scan using _hyper_38_1679_chunk_event_data_occurred_at_seq_id_idx on _hyper_38_1679_chunk x2_19 (cost=0.15..5.96 rows=1 width=28) (actual time=0.006..0.007 rows=0 loops=1)

  • Index Cond: (seq_id = x1.seq_id)
  • Filter: (event_id <> x1.event_id)
63. 17.971 17.971 ↓ 0.0 0 1

Index Scan using _hyper_38_1680_chunk_event_data_occurred_at_seq_id_idx on _hyper_38_1680_chunk x2_20 (cost=0.42..15,317.44 rows=1 width=28) (actual time=17.971..17.971 rows=0 loops=1)

  • Index Cond: (seq_id = x1.seq_id)
  • Filter: (event_id <> x1.event_id)
64. 25.206 25.206 ↓ 0.0 0 1

Index Scan using _hyper_38_1682_chunk_event_data_occurred_at_seq_id_idx on _hyper_38_1682_chunk x2_21 (cost=0.43..22,725.52 rows=1 width=28) (actual time=25.205..25.206 rows=0 loops=1)

  • Index Cond: (seq_id = x1.seq_id)
  • Filter: (event_id <> x1.event_id)
65. 30.283 30.283 ↓ 2.0 2 1

Index Scan using _hyper_38_1683_chunk_event_data_occurred_at_seq_id_idx on _hyper_38_1683_chunk x2_22 (cost=0.43..28,994.32 rows=1 width=28) (actual time=0.020..30.283 rows=2 loops=1)

  • Index Cond: (seq_id = x1.seq_id)
  • Filter: (event_id <> x1.event_id)
  • Rows Removed by Filter: 1
66. 0.080 0.131 ↑ 2.0 36 1

Sort (cost=7.99..8.17 rows=73 width=19) (actual time=0.120..0.131 rows=36 loops=1)

  • Sort Key: resp_pers.user_id
  • Sort Method: quicksort Memory: 32kB
67. 0.051 0.051 ↓ 1.3 96 1

Seq Scan on person resp_pers (cost=0.00..5.73 rows=73 width=19) (actual time=0.007..0.051 rows=96 loops=1)

Planning time : 11.144 ms
Execution time : 3,177.313 ms