explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FgJD : Optimization for: plan #P11V

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.019 0.198 ↑ 1.5 2 1

Aggregate (cost=7.35..7.38 rows=3 width=264) (actual time=0.197..0.198 rows=2 loops=1)

  • Buffers: shared hit=5
2.          

CTE timebetweenpicks

3. 0.011 0.043 ↑ 1.0 8 1

Sort (cost=1.54..1.56 rows=8 width=56) (actual time=0.042..0.043 rows=8 loops=1)

  • Sort Key: test_1."timestamp"
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=1
4. 0.017 0.032 ↑ 1.0 8 1

WindowAgg (cost=1.2..1.42 rows=8 width=56) (actual time=0.019..0.032 rows=8 loops=1)

  • Buffers: shared hit=1
5. 0.013 0.015 ↑ 1.0 8 1

Sort (cost=1.2..1.22 rows=8 width=40) (actual time=0.013..0.015 rows=8 loops=1)

  • Sort Key: test_1.picker_username, test_1."timestamp"
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=1
6. 0.002 0.002 ↑ 1.0 8 1

Seq Scan on test test_1 (cost=0..1.08 rows=8 width=40) (actual time=0.001..0.002 rows=8 loops=1)

  • Buffers: shared hit=1
7.          

CTE active_time

8. 0.010 0.058 ↑ 1.5 2 1

Aggregate (cost=0.2..0.23 rows=3 width=48) (actual time=0.056..0.058 rows=2 loops=1)

  • Buffers: shared hit=1
9. 0.048 0.048 ↓ 1.3 4 1

CTE Scan on timebetweenpicks timebetweenpicks (cost=0..0.18 rows=3 width=48) (actual time=0.044..0.048 rows=4 loops=1)

  • Filter: (timebetweenpicks.duration < '00:05:00'::interval)
  • Buffers: shared hit=1
10.          

CTE last_action

11. 0.001 0.025 ↑ 4.0 2 1

Unique (cost=1.2..1.24 rows=8 width=200) (actual time=0.024..0.025 rows=2 loops=1)

  • Buffers: shared hit=1
12. 0.018 0.024 ↑ 1.0 8 1

Sort (cost=1.2..1.22 rows=8 width=200) (actual time=0.023..0.024 rows=8 loops=1)

  • Sort Key: test_2.picker_username, test_2."timestamp" DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=1
13. 0.006 0.006 ↑ 1.0 8 1

Seq Scan on test test_2 (cost=0..1.08 rows=8 width=200) (actual time=0.003..0.006 rows=8 loops=1)

  • Buffers: shared hit=1
14.          

CTE last_interval

15. 0.007 0.012 ↑ 1.5 2 1

Aggregate (cost=1.12..1.15 rows=3 width=72) (actual time=0.012..0.012 rows=2 loops=1)

  • Buffers: shared hit=1
16. 0.005 0.005 ↓ 2.0 6 1

Seq Scan on test test_3 (cost=0..1.1 rows=3 width=32) (actual time=0.003..0.005 rows=6 loops=1)

  • Filter: (test_3."timestamp" >= '2019-08-12 11:02:00.066'::timestamp without time zone)
  • Buffers: shared hit=1
17.          

CTE total_interval

18. 0.008 0.013 ↑ 4.0 2 1

Aggregate (cost=1.14..1.22 rows=8 width=72) (actual time=0.012..0.013 rows=2 loops=1)

  • Buffers: shared hit=1
19. 0.005 0.005 ↑ 1.0 8 1

Seq Scan on test test_4 (cost=0..1.08 rows=8 width=32) (actual time=0.002..0.005 rows=8 loops=1)

  • Buffers: shared hit=1
20. 0.012 0.179 ↓ 2.7 8 1

Hash Join (cost=1.63..1.87 rows=3 width=232) (actual time=0.171..0.179 rows=8 loops=1)

  • Buffers: shared hit=5
21. 0.012 0.152 ↓ 2.7 8 1

Hash Join (cost=1.53..1.75 rows=3 width=224) (actual time=0.146..0.152 rows=8 loops=1)

  • Buffers: shared hit=4
22. 0.014 0.014 ↑ 4.0 2 1

CTE Scan on total_interval total_interval (cost=0..0.16 rows=8 width=40) (actual time=0.012..0.014 rows=2 loops=1)

  • Buffers: shared hit=1
23. 0.005 0.126 ↓ 2.7 8 1

Hash (cost=1.5..1.5 rows=3 width=216) (actual time=0.126..0.126 rows=8 loops=1)

  • Buffers: shared hit=3
24. 0.012 0.121 ↓ 2.7 8 1

Hash Join (cost=0.35..1.5 rows=3 width=216) (actual time=0.116..0.121 rows=8 loops=1)

  • Buffers: shared hit=3
25. 0.010 0.010 ↑ 1.0 8 1

Seq Scan on test test (cost=0..1.08 rows=8 width=32) (actual time=0.008..0.01 rows=8 loops=1)

  • Buffers: shared hit=1
26. 0.002 0.099 ↑ 1.5 2 1

Hash (cost=0.32..0.32 rows=3 width=248) (actual time=0.099..0.099 rows=2 loops=1)

  • Buffers: shared hit=2
27. 0.010 0.097 ↑ 1.5 2 1

Hash Join (cost=0.1..0.32 rows=3 width=248) (actual time=0.093..0.097 rows=2 loops=1)

  • Buffers: shared hit=2
28. 0.026 0.026 ↑ 4.0 2 1

CTE Scan on last_action last_action (cost=0..0.16 rows=8 width=200) (actual time=0.024..0.026 rows=2 loops=1)

  • Buffers: shared hit=1
29. 0.002 0.061 ↑ 1.5 2 1

Hash (cost=0.06..0.06 rows=3 width=48) (actual time=0.061..0.061 rows=2 loops=1)

  • Buffers: shared hit=1
30. 0.059 0.059 ↑ 1.5 2 1

CTE Scan on active_time active_time (cost=0..0.06 rows=3 width=48) (actual time=0.057..0.059 rows=2 loops=1)

  • Buffers: shared hit=1
31. 0.002 0.015 ↑ 1.5 2 1

Hash (cost=0.06..0.06 rows=3 width=40) (actual time=0.015..0.015 rows=2 loops=1)

  • Buffers: shared hit=1
32. 0.013 0.013 ↑ 1.5 2 1

CTE Scan on last_interval last_interval (cost=0..0.06 rows=3 width=40) (actual time=0.012..0.013 rows=2 loops=1)

  • Buffers: shared hit=1