explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4by7

Settings
# exclusive inclusive rows x rows loops node
1. 1.812 378.472 ↑ 1.9 60 1

HashAggregate (cost=80.62..82.00 rows=111 width=48) (actual time=378.431..378.472 rows=60 loops=1)

  • Group Key: bucketed_positions.start, bucketed_positions.finish
2.          

CTE series

3. 0.020 0.020 ↑ 16.4 61 1

Result (cost=0.00..5.04 rows=1,000 width=8) (actual time=0.015..0.020 rows=61 loops=1)

4.          

CTE intervals

5. 0.059 0.059 ↑ 16.4 61 1

CTE Scan on series (cost=0.00..22.50 rows=1,000 width=16) (actual time=0.040..0.059 rows=61 loops=1)

6.          

CTE bucketed_positions

7. 1.815 375.570 ↓ 30.5 3,389 1

Unique (cost=49.19..50.02 rows=111 width=64) (actual time=370.892..375.570 rows=3,389 loops=1)

8. 29.441 373.755 ↓ 160.4 17,807 1

Sort (cost=49.19..49.47 rows=111 width=64) (actual time=370.892..373.755 rows=17,807 loops=1)

  • Sort Key: intervals.start, positions.user_id, positions.created_at DESC
  • Sort Method: external sort Disk: 3728kB
9. 230.668 344.314 ↓ 160.4 17,807 1

Nested Loop (cost=0.57..45.42 rows=111 width=64) (actual time=0.075..344.314 rows=17,807 loops=1)

  • Join Filter: ((positions.created_at >= intervals.start) AND (positions.created_at < intervals.finish))
  • Rows Removed by Join Filter: 1068420
10. 24.611 24.611 ↓ 17,807.0 17,807 1

Index Scan using positions_on_created_at on positions (cost=0.57..10.14 rows=1 width=48) (actual time=0.014..24.611 rows=17,807 loops=1)

  • Index Cond: ((created_at > '2019-11-06 21:00:00+00'::timestamp with time zone) AND (created_at < '2019-11-06 22:00:00+00'::timestamp with time zone))
  • Filter: (project_id = 204)
  • Rows Removed by Filter: 26151
11. 89.035 89.035 ↑ 16.4 61 17,807

CTE Scan on intervals (cost=0.00..20.00 rows=1,000 width=16) (actual time=0.000..0.005 rows=61 loops=17,807)

12. 376.660 376.660 ↓ 30.5 3,389 1

CTE Scan on bucketed_positions (cost=0.00..2.22 rows=111 width=48) (actual time=370.894..376.660 rows=3,389 loops=1)

Planning time : 0.409 ms
Execution time : 379.556 ms