explain.depesz.com

PostgreSQL's explain analyze made readable

Result: M6E8

Settings
# exclusive inclusive rows x rows loops node
1. 0.046 869.591 ↑ 1,000.0 1 1

Hash Join (cost=7,688.19..7,706.23 rows=1,000 width=657) (actual time=869.535..869.591 rows=1 loops=1)

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

Hash Join (cost=5,828.44..5,843.80 rows=1,000 width=612) (actual time=848.009..848.051 rows=1 loops=1)

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

Hash Join (cost=2,914.47..2,927.15 rows=1,000 width=386) (actual time=422.632..422.661 rows=1 loops=1)

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

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

  • Buffers: shared hit=585
5. 0.025 404.727 ↑ 200.0 1 1

Hash (cost=2,911.97..2,911.97 rows=200 width=226) (actual time=404.720..404.727 rows=1 loops=1)

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

HashAggregate (cost=2,907.47..2,909.97 rows=200 width=226) (actual time=404.687..404.702 rows=1 loops=1)

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

HashAggregate (cost=2,887.25..2,895.34 rows=809 width=234) (actual time=404.495..404.586 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 404.399 ↑ 73.5 11 1

Append (cost=2,850.77..2,881.18 rows=809 width=234) (actual time=404.106..404.399 rows=11 loops=1)

  • Buffers: shared hit=354
9. 55.900 404.181 ↑ 73.5 11 1

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

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

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

  • Buffers: shared hit=353
11. 154.205 247.066 ↑ 1.3 6,385 1

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

  • Group Key: al.identity_id, al.start_time
  • Buffers: shared hit=353
12. 91.423 92.861 ↓ 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=1.542..92.861 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
13. 1.438 1.438 ↓ 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.431..1.438 rows=11,113 loops=1)

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

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

  • Buffers: shared hit=1
15. 0.037 0.037 ↓ 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.030..0.037 rows=0 loops=1)

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

Hash (cost=2,911.46..2,911.46 rows=200 width=226) (actual time=425.339..425.346 rows=1 loops=1)

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

HashAggregate (cost=2,907.46..2,909.46 rows=200 width=226) (actual time=425.300..425.315 rows=1 loops=1)

  • Group Key: pm_1.identity_id
  • Buffers: shared hit=354
18. 0.265 425.201 ↑ 73.5 11 1

HashAggregate (cost=2,887.24..2,895.33 rows=809 width=234) (actual time=425.094..425.201 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
19. 0.296 424.936 ↑ 73.5 11 1

Append (cost=2,850.77..2,881.17 rows=809 width=234) (actual time=424.364..424.936 rows=11 loops=1)

  • Buffers: shared hit=354
20. 57.034 424.536 ↑ 73.5 11 1

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

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

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

  • Buffers: shared hit=353
22. 163.770 261.993 ↑ 1.3 6,385 1

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

  • Group Key: al_1.identity_id, al_1.start_time
  • Buffers: shared hit=353
23. 97.297 98.223 ↓ 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=0.986..98.223 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
24. 0.926 0.926 ↓ 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.919..0.926 rows=11,113 loops=1)

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

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

  • Buffers: shared hit=1
26. 0.088 0.088 ↓ 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.081..0.088 rows=0 loops=1)

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

Hash (cost=1,857.25..1,857.25 rows=200 width=45) (actual time=21.488..21.494 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=122
28. 5.441 21.451 ↑ 200.0 1 1

HashAggregate (cost=1,853.25..1,855.25 rows=200 width=45) (actual time=21.436..21.451 rows=1 loops=1)

  • Group Key: al_2.identity_id
  • Buffers: shared hit=122
29. 10.387 16.010 ↓ 1.0 510 1

HashAggregate (cost=1,839.72..1,845.87 rows=492 width=53) (actual time=10.599..16.010 rows=510 loops=1)

  • Group Key: al_2.identity_id, al_2.start_time
  • Buffers: shared hit=122
30. 4.254 5.623 ↓ 1.0 510 1

Bitmap Heap Scan on activity_log_activitylog al_2 (cost=695.28..1,835.98 rows=499 width=53) (actual time=1.399..5.623 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
31. 0.025 1.369 ↓ 0.0 0 1

BitmapAnd (cost=695.28..695.28 rows=499 width=0) (actual time=1.362..1.369 rows=0 loops=1)

  • Buffers: shared hit=110
32. 0.350 0.350 ↓ 1.0 3,924 1

Bitmap Index Scan on start_time_index (cost=0.00..239.16 rows=3,885 width=0) (actual time=0.343..0.350 rows=3,924 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
33. 0.994 0.994 ↓ 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.987..0.994 rows=11,113 loops=1)

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