explain.depesz.com

PostgreSQL's explain analyze made readable

Result: x2PS

Settings
# exclusive inclusive rows x rows loops node
1. 0.047 834.778 ↑ 1,000.0 1 1

Hash Join (cost=7,754.10..7,772.14 rows=1,000 width=657) (actual time=834.718..834.778 rows=1 loops=1)

  • Hash Cond: ((pm_1.identity_id)::text = (al_2.identity_id)::text)
  • Buffers: shared hit=1415
2. 0.050 803.326 ↑ 1,000.0 1 1

Hash Join (cost=5,910.52..5,925.88 rows=1,000 width=612) (actual time=803.281..803.326 rows=1 loops=1)

  • Hash Cond: ((pm.identity_id)::text = (pm_1.identity_id)::text)
  • Buffers: shared hit=1293
3. 0.043 405.578 ↑ 1,000.0 1 1

Hash Join (cost=2,955.51..2,968.19 rows=1,000 width=386) (actual time=405.546..405.578 rows=1 loops=1)

  • Hash Cond: (ct.identity_id = (pm.identity_id)::text)
  • Buffers: shared hit=939
4. 18.198 18.198 ↑ 1,000.0 1 1

Function Scan on crosstab ct (cost=0.00..10.00 rows=1,000 width=160) (actual time=18.182..18.198 rows=1 loops=1)

  • Buffers: shared hit=585
5. 0.030 387.337 ↑ 200.0 1 1

Hash (cost=2,953.01..2,953.01 rows=200 width=226) (actual time=387.328..387.337 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=354
6. 0.127 387.307 ↑ 200.0 1 1

HashAggregate (cost=2,948.51..2,951.01 rows=200 width=226) (actual time=387.291..387.307 rows=1 loops=1)

  • Group Key: pm.identity_id
  • Buffers: shared hit=354
7. 0.206 387.180 ↑ 73.5 11 1

HashAggregate (cost=2,928.29..2,936.38 rows=809 width=234) (actual time=387.079..387.180 rows=11 loops=1)

  • Group Key: pm.identity_id, (date_trunc('day'::text, pm.start_time)), (sum(pm.avg_steps))
  • Buffers: shared hit=354
8. 0.173 386.974 ↑ 73.5 11 1

Append (cost=2,899.89..2,922.22 rows=809 width=234) (actual time=386.637..386.974 rows=11 loops=1)

  • Buffers: shared hit=354
9. 0.187 386.705 ↑ 73.5 11 1

Sort (cost=2,899.89..2,901.91 rows=808 width=53) (actual time=386.620..386.705 rows=11 loops=1)

  • Sort Key: (date_trunc('day'::text, pm.start_time))
  • Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=353
10. 52.853 386.518 ↑ 73.5 11 1

HashAggregate (cost=2,850.77..2,860.87 rows=808 width=53) (actual time=386.420..386.518 rows=11 loops=1)

  • Group Key: date_trunc('day'::text, pm.start_time), pm.identity_id
  • Buffers: shared hit=353
11. 99.504 333.665 ↑ 1.3 6,385 1

Subquery Scan on pm (cost=2,588.04..2,790.14 rows=8,084 width=53) (actual time=183.345..333.665 rows=6,385 loops=1)

  • Buffers: shared hit=353
12. 147.231 234.161 ↑ 1.3 6,385 1

HashAggregate (cost=2,588.04..2,689.09 rows=8,084 width=53) (actual time=183.324..234.161 rows=6,385 loops=1)

  • Group Key: al.identity_id, al.start_time
  • Buffers: shared hit=353
13. 86.202 86.930 ↓ 1.0 11,113 1

Bitmap Heap Scan on activity_log_activitylog al (cost=458.40..2,504.83 rows=11,095 width=53) (actual time=0.773..86.930 rows=11,113 loops=1)

  • Recheck Cond: (((identity_id)::text = '79de8463-d571-4c50-85e8-45479b50451f'::text) AND (steps > '0'::double precision))
  • Heap Blocks: exact=259
  • Buffers: shared hit=353
14. 0.728 0.728 ↓ 1.0 11,113 1

Bitmap Index Scan on identity_id_steps_idx (cost=0.00..455.63 rows=11,095 width=0) (actual time=0.722..0.728 rows=11,113 loops=1)

  • Index Cond: ((identity_id)::text = '79de8463-d571-4c50-85e8-45479b50451f'::text)
  • Buffers: shared hit=94
15. 0.018 0.096 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=0.15..8.19 rows=1 width=234) (actual time=0.088..0.096 rows=0 loops=1)

  • Buffers: shared hit=1
16. 0.078 0.078 ↓ 0.0 0 1

Index Scan using activity_log_rollup_identity_id_3da05b48_like on activity_log_rollup ar (cost=0.15..8.17 rows=1 width=230) (actual time=0.071..0.078 rows=0 loops=1)

  • Index Cond: ((identity_id)::text = '79de8463-d571-4c50-85e8-45479b50451f'::text)
  • Filter: (day > (CURRENT_DATE - 365))
  • Buffers: shared hit=1
17. 0.025 397.698 ↑ 200.0 1 1

Hash (cost=2,952.50..2,952.50 rows=200 width=226) (actual time=397.691..397.698 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=354
18. 0.110 397.673 ↑ 200.0 1 1

HashAggregate (cost=2,948.50..2,950.50 rows=200 width=226) (actual time=397.658..397.673 rows=1 loops=1)

  • Group Key: pm_1.identity_id
  • Buffers: shared hit=354
19. 0.182 397.563 ↑ 73.5 11 1

HashAggregate (cost=2,928.28..2,936.37 rows=809 width=234) (actual time=397.475..397.563 rows=11 loops=1)

  • Group Key: pm_1.identity_id, (date_trunc('day'::text, pm_1.start_time)), (sum(pm_1.avg_steps))
  • Buffers: shared hit=354
20. 0.162 397.381 ↑ 73.5 11 1

Append (cost=2,899.89..2,922.21 rows=809 width=234) (actual time=397.102..397.381 rows=11 loops=1)

  • Buffers: shared hit=354
21. 0.170 397.168 ↑ 73.5 11 1

Sort (cost=2,899.89..2,901.91 rows=808 width=53) (actual time=397.087..397.168 rows=11 loops=1)

  • Sort Key: (date_trunc('day'::text, pm_1.start_time))
  • Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=353
22. 53.589 396.998 ↑ 73.5 11 1

HashAggregate (cost=2,850.77..2,860.87 rows=808 width=53) (actual time=396.910..396.998 rows=11 loops=1)

  • Group Key: date_trunc('day'::text, pm_1.start_time), pm_1.identity_id
  • Buffers: shared hit=353
23. 100.427 343.409 ↑ 1.3 6,385 1

Subquery Scan on pm_1 (cost=2,588.04..2,790.14 rows=8,084 width=53) (actual time=190.845..343.409 rows=6,385 loops=1)

  • Buffers: shared hit=353
24. 151.461 242.982 ↑ 1.3 6,385 1

HashAggregate (cost=2,588.04..2,689.09 rows=8,084 width=53) (actual time=190.825..242.982 rows=6,385 loops=1)

  • Group Key: al_1.identity_id, al_1.start_time
  • Buffers: shared hit=353
25. 90.553 91.521 ↓ 1.0 11,113 1

Bitmap Heap Scan on activity_log_activitylog al_1 (cost=458.40..2,504.83 rows=11,095 width=53) (actual time=1.008..91.521 rows=11,113 loops=1)

  • Recheck Cond: (((identity_id)::text = '79de8463-d571-4c50-85e8-45479b50451f'::text) AND (steps > '0'::double precision))
  • Heap Blocks: exact=259
  • Buffers: shared hit=353
26. 0.968 0.968 ↓ 1.0 11,113 1

Bitmap Index Scan on identity_id_steps_idx (cost=0.00..455.63 rows=11,095 width=0) (actual time=0.961..0.968 rows=11,113 loops=1)

  • Index Cond: ((identity_id)::text = '79de8463-d571-4c50-85e8-45479b50451f'::text)
  • Buffers: shared hit=94
27. 0.013 0.051 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2_1 (cost=0.15..8.18 rows=1 width=234) (actual time=0.044..0.051 rows=0 loops=1)

  • Buffers: shared hit=1
28. 0.038 0.038 ↓ 0.0 0 1

Index Scan using activity_log_rollup_identity_id_3da05b48_like on activity_log_rollup ar_1 (cost=0.15..8.17 rows=1 width=230) (actual time=0.030..0.038 rows=0 loops=1)

  • Index Cond: ((identity_id)::text = '79de8463-d571-4c50-85e8-45479b50451f'::text)
  • Buffers: shared hit=1
29. 0.023 31.405 ↑ 200.0 1 1

Hash (cost=1,841.09..1,841.09 rows=200 width=45) (actual time=31.398..31.405 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=122
30. 5.224 31.382 ↑ 200.0 1 1

HashAggregate (cost=1,837.09..1,839.09 rows=200 width=45) (actual time=31.350..31.382 rows=1 loops=1)

  • Group Key: al_2.identity_id
  • Buffers: shared hit=122
31. 11.622 26.158 ↓ 1.1 510 1

Sort (cost=1,828.81..1,829.99 rows=473 width=53) (actual time=21.372..26.158 rows=510 loops=1)

  • Sort Key: al_2.start_time
  • Sort Method: quicksort Memory: 96kB
  • Buffers: shared hit=122
32. 8.881 14.536 ↓ 1.1 510 1

HashAggregate (cost=1,801.88..1,807.79 rows=473 width=53) (actual time=10.049..14.536 rows=510 loops=1)

  • Group Key: al_2.start_time, al_2.identity_id
  • Buffers: shared hit=122
33. 4.335 5.655 ↓ 1.1 510 1

Bitmap Heap Scan on activity_log_activitylog al_2 (cost=685.84..1,798.28 rows=480 width=53) (actual time=1.350..5.655 rows=510 loops=1)

  • Recheck Cond: ((start_time >= timezone('UTC'::text, timezone('+10:00'::text, date_trunc('day'::text, timezone('+10:00'::text, LOCALTIMESTAMP))))) AND (start_time <= CURRENT_TIMESTAMP) AND ((identity_id)::text = '79de8463-d571-4c50-85e8-45479b50451f'::text) AND (steps > '0'::double precision))
  • Heap Blocks: exact=12
  • Buffers: shared hit=122
34. 0.029 1.320 ↓ 0.0 0 1

BitmapAnd (cost=685.84..685.84 rows=480 width=0) (actual time=1.313..1.320 rows=0 loops=1)

  • Buffers: shared hit=110
35. 0.309 0.309 ↓ 1.0 3,786 1

Bitmap Index Scan on start_time_index (cost=0.00..229.72 rows=3,741 width=0) (actual time=0.302..0.309 rows=3,786 loops=1)

  • Index Cond: ((start_time >= timezone('UTC'::text, timezone('+10:00'::text, date_trunc('day'::text, timezone('+10:00'::text, LOCALTIMESTAMP))))) AND (start_time <= CURRENT_TIMESTAMP))
  • Buffers: shared hit=16
36. 0.982 0.982 ↓ 1.0 11,113 1

Bitmap Index Scan on identity_id_steps_idx (cost=0.00..455.63 rows=11,095 width=0) (actual time=0.975..0.982 rows=11,113 loops=1)

  • Index Cond: ((identity_id)::text = '79de8463-d571-4c50-85e8-45479b50451f'::text)
  • Buffers: shared hit=94
Planning time : 20.484 ms