explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YYtk0

Settings
# exclusive inclusive rows x rows loops node
1. 92.332 21,464.192 ↓ 80.4 96,485 1

Sort (cost=1,435,421.19..1,435,424.19 rows=1,200 width=26) (actual time=21,452.857..21,464.192 rows=96,485 loops=1)

  • Sort Key: detectionlog.detectable_id, (count(*) FILTER (WHERE (((detectionlog.captured_at - lag(detectionlog.captured_at) OVER (?)) > '00:00:03'::interval))) OVER (?))
  • Sort Method: external merge Disk: 3952kB
2. 1,892.462 21,371.860 ↓ 80.4 96,485 1

HashAggregate (cost=1,435,347.82..1,435,359.82 rows=1,200 width=26) (actual time=21,337.344..21,371.860 rows=96,485 loops=1)

  • Group Key: detectionlog.detectable_id, count(*) FILTER (WHERE (((detectionlog.captured_at - lag(detectionlog.captured_at) OVER (?)) > '00:00:03'::interval))) OVER (?)
3. 3,576.668 19,479.398 ↓ 1.6 7,594,413 1

WindowAgg (cost=1,099,131.17..1,342,598.40 rows=4,637,471 width=20) (actual time=10,551.506..19,479.398 rows=7,594,413 loops=1)

4. 3,608.212 15,902.730 ↓ 1.6 7,594,413 1

WindowAgg (cost=1,099,131.17..1,215,067.95 rows=4,637,471 width=13) (actual time=10,551.496..15,902.730 rows=7,594,413 loops=1)

5. 7,821.505 12,294.518 ↓ 1.6 7,594,413 1

Sort (cost=1,099,131.17..1,110,724.85 rows=4,637,471 width=10) (actual time=10,551.481..12,294.518 rows=7,594,413 loops=1)

  • Sort Key: detectionlog.detectable_id, detectionlog.captured_at
  • Sort Method: external merge Disk: 193024kB
6. 4,473.013 4,473.013 ↓ 1.6 7,594,413 1

Index Scan using detectionlog_captured_at_index on detectionlog (cost=0.57..427,141.36 rows=4,637,471 width=10) (actual time=380.770..4,473.013 rows=7,594,413 loops=1)

  • Index Cond: ((captured_at >= (now() - '1 mon'::interval)) AND (captured_at <= now()))
  • Filter: (camera_id = 63)
  • Rows Removed by Filter: 4580185
Planning time : 0.242 ms
Execution time : 21,502.981 ms