explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9Y4

Settings
# exclusive inclusive rows x rows loops node
1. 0.044 1,038.143 ↑ 1,000.0 1 1

Hash Join (cost=8,816.51..8,834.55 rows=1,000 width=657) (actual time=1,038.087..1,038.143 rows=1 loops=1)

  • Hash Cond: ((pm.identity_id)::text = (pm_1.identity_id)::text)
  • Buffers: shared hit=1415
2. 0.048 538.076 ↑ 1,000.0 1 1

Hash Join (cost=5,316.61..5,331.97 rows=1,000 width=431) (actual time=538.034..538.076 rows=1 loops=1)

  • Hash Cond: ((pm.identity_id)::text = (al_1.identity_id)::text)
  • Buffers: shared hit=1061
3. 0.040 513.113 ↑ 1,000.0 1 1

Hash Join (cost=3,500.41..3,513.09 rows=1,000 width=386) (actual time=513.084..513.113 rows=1 loops=1)

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

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

  • Buffers: shared hit=585
5. 0.027 494.342 ↑ 200.0 1 1

Hash (cost=3,497.91..3,497.91 rows=200 width=226) (actual time=494.335..494.342 rows=1 loops=1)

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

HashAggregate (cost=3,493.41..3,495.91 rows=200 width=226) (actual time=494.301..494.315 rows=1 loops=1)

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

HashAggregate (cost=3,473.18..3,481.27 rows=809 width=234) (actual time=494.112..494.201 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.167 494.016 ↑ 73.5 11 1

Append (cost=3,444.78..3,467.11 rows=809 width=234) (actual time=493.729..494.016 rows=11 loops=1)

  • Buffers: shared hit=354
9. 0.176 493.796 ↑ 73.5 11 1

Sort (cost=3,444.78..3,446.80 rows=808 width=53) (actual time=493.714..493.796 rows=11 loops=1)

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

HashAggregate (cost=3,395.66..3,405.76 rows=808 width=53) (actual time=493.529..493.620 rows=11 loops=1)

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

Subquery Scan on pm (cost=3,213.77..3,335.03 rows=8,084 width=53) (actual time=286.674..438.659 rows=6,385 loops=1)

  • Buffers: shared hit=353
12. 101.524 335.615 ↑ 1.3 6,385 1

Sort (cost=3,213.77..3,233.98 rows=8,084 width=53) (actual time=286.643..335.615 rows=6,385 loops=1)

  • Sort Key: al.start_time
  • Sort Method: quicksort Memory: 1090kB
  • Buffers: shared hit=353
13. 146.594 234.091 ↑ 1.3 6,385 1

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

  • Group Key: al.start_time, al.identity_id
  • Buffers: shared hit=353
14. 86.647 87.497 ↓ 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.892..87.497 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
15. 0.850 0.850 ↓ 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.843..0.850 rows=11,113 loops=1)

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

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

  • Buffers: shared hit=1
17. 0.039 0.039 ↓ 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.032..0.039 rows=0 loops=1)

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

Hash (cost=1,813.70..1,813.70 rows=200 width=45) (actual time=24.909..24.915 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=122
19. 4.005 24.892 ↑ 200.0 1 1

HashAggregate (cost=1,809.70..1,811.70 rows=200 width=45) (actual time=24.878..24.892 rows=1 loops=1)

  • Group Key: al_1.identity_id
  • Buffers: shared hit=122
20. 7.560 20.887 ↓ 1.1 510 1

Sort (cost=1,801.65..1,802.80 rows=460 width=53) (actual time=17.367..20.887 rows=510 loops=1)

  • Sort Key: al_1.start_time
  • Sort Method: quicksort Memory: 96kB
  • Buffers: shared hit=122
21. 8.354 13.327 ↓ 1.1 510 1

HashAggregate (cost=1,775.56..1,781.31 rows=460 width=53) (actual time=9.149..13.327 rows=510 loops=1)

  • Group Key: al_1.start_time, al_1.identity_id
  • Buffers: shared hit=122
22. 3.806 4.973 ↓ 1.1 510 1

Bitmap Heap Scan on activity_log_activitylog al_1 (cost=680.70..1,772.06 rows=466 width=53) (actual time=1.184..4.973 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
23. 0.022 1.167 ↓ 0.0 0 1

BitmapAnd (cost=680.70..680.70 rows=466 width=0) (actual time=1.160..1.167 rows=0 loops=1)

  • Buffers: shared hit=110
24. 0.325 0.325 ↓ 1.0 3,678 1

Bitmap Index Scan on start_time_index (cost=0.00..224.59 rows=3,628 width=0) (actual time=0.318..0.325 rows=3,678 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
25. 0.820 0.820 ↓ 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.813..0.820 rows=11,113 loops=1)

  • Index Cond: ((identity_id)::text = '79de8463-d571-4c50-85e8-45479b50451f'::text)
  • Buffers: shared hit=94
26. 0.029 500.023 ↑ 200.0 1 1

Hash (cost=3,497.40..3,497.40 rows=200 width=226) (actual time=500.016..500.023 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=354
27. 0.124 499.994 ↑ 200.0 1 1

HashAggregate (cost=3,493.40..3,495.40 rows=200 width=226) (actual time=499.978..499.994 rows=1 loops=1)

  • Group Key: pm_1.identity_id
  • Buffers: shared hit=354
28. 0.295 499.870 ↑ 73.5 11 1

HashAggregate (cost=3,473.17..3,481.26 rows=809 width=234) (actual time=499.770..499.870 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
29. 0.220 499.575 ↑ 73.5 11 1

Append (cost=3,444.78..3,467.11 rows=809 width=234) (actual time=499.173..499.575 rows=11 loops=1)

  • Buffers: shared hit=354
30. 0.256 499.264 ↑ 73.5 11 1

Sort (cost=3,444.78..3,446.80 rows=808 width=53) (actual time=499.153..499.264 rows=11 loops=1)

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

HashAggregate (cost=3,395.66..3,405.76 rows=808 width=53) (actual time=498.819..499.008 rows=11 loops=1)

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

Subquery Scan on pm_1 (cost=3,213.77..3,335.03 rows=8,084 width=53) (actual time=293.131..445.022 rows=6,385 loops=1)

  • Buffers: shared hit=353
33. 99.456 341.885 ↑ 1.3 6,385 1

Sort (cost=3,213.77..3,233.98 rows=8,084 width=53) (actual time=293.110..341.885 rows=6,385 loops=1)

  • Sort Key: al_2.start_time
  • Sort Method: quicksort Memory: 1090kB
  • Buffers: shared hit=353
34. 150.581 242.429 ↑ 1.3 6,385 1

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

  • Group Key: al_2.start_time, al_2.identity_id
  • Buffers: shared hit=353
35. 90.698 91.848 ↓ 1.0 11,113 1

Bitmap Heap Scan on activity_log_activitylog al_2 (cost=458.40..2,504.83 rows=11,095 width=53) (actual time=1.228..91.848 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
36. 1.150 1.150 ↓ 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=1.143..1.150 rows=11,113 loops=1)

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

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

  • Buffers: shared hit=1
38. 0.051 0.051 ↓ 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.045..0.051 rows=0 loops=1)

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