explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rJeH

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

Hash Join (cost=30,081.37..30,128.87 rows=1,000 width=657) (actual time=2,884.164..2,884.194 rows=1 loops=1)

  • Hash Cond: (ct.identity_id = (pm_1.identity_id)::text)
  • Buffers: shared hit=1750 read=1915
2. 108.228 108.228 ↑ 1,000.0 1 1

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

  • Buffers: shared hit=15 read=809
3. 0.037 2,775.922 ↑ 200.0 1 1

Hash (cost=30,078.87..30,078.87 rows=200 width=497) (actual time=2,775.915..2,775.922 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1735 read=1106
4. 0.035 2,775.885 ↑ 200.0 1 1

Merge Join (cost=30,062.35..30,078.87 rows=200 width=497) (actual time=2,775.843..2,775.885 rows=1 loops=1)

  • Merge Cond: ((pm_1.identity_id)::text = (avs.identity_id)::text)
  • Buffers: shared hit=1735 read=1106
5. 0.034 2,713.950 ↑ 200.0 1 1

Merge Join (cost=25,730.34..25,734.34 rows=200 width=452) (actual time=2,713.922..2,713.950 rows=1 loops=1)

  • Merge Cond: ((pm.identity_id)::text = (pm_1.identity_id)::text)
  • Buffers: shared hit=1304 read=1106
6. 0.049 1,496.324 ↑ 200.0 1 1

Sort (cost=12,865.42..12,865.92 rows=200 width=226) (actual time=1,496.310..1,496.324 rows=1 loops=1)

  • Sort Key: pm.identity_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=99 read=1106
7. 0.315 1,496.275 ↑ 200.0 1 1

HashAggregate (cost=12,853.28..12,855.78 rows=200 width=226) (actual time=1,496.261..1,496.275 rows=1 loops=1)

  • Group Key: pm.identity_id
  • Buffers: shared hit=99 read=1106
8. 0.478 1,495.960 ↑ 81.0 30 1

HashAggregate (cost=12,792.56..12,816.85 rows=2,429 width=234) (actual time=1,495.721..1,495.960 rows=30 loops=1)

  • Group Key: pm.identity_id, (date_trunc('day'::text, pm.start_time)), (sum(pm.avg_steps))
  • Buffers: shared hit=99 read=1106
9. 0.416 1,495.482 ↑ 81.0 30 1

Append (cost=12,699.38..12,774.34 rows=2,429 width=234) (actual time=1,493.151..1,495.482 rows=30 loops=1)

  • Buffers: shared hit=99 read=1106
10. 168.799 1,493.370 ↑ 80.9 30 1

HashAggregate (cost=12,699.38..12,729.73 rows=2,428 width=53) (actual time=1,493.136..1,493.370 rows=30 loops=1)

  • Group Key: pm.identity_id, date_trunc('day'::text, pm.start_time)
  • Buffers: shared hit=99 read=1105
11. 311.985 1,324.571 ↑ 1.3 19,285 1

Subquery Scan on pm (cost=11,910.18..12,517.26 rows=24,283 width=53) (actual time=847.781..1,324.571 rows=19,285 loops=1)

  • Buffers: shared hit=99 read=1105
12. 483.886 1,012.586 ↑ 1.3 19,285 1

HashAggregate (cost=11,910.18..12,213.72 rows=24,283 width=53) (actual time=847.762..1,012.586 rows=19,285 loops=1)

  • Group Key: al.identity_id, al.start_time
  • Buffers: shared hit=99 read=1105
13. 334.577 528.700 ↓ 1.0 35,377 1

Bitmap Heap Scan on activity_log_activitylog al (cost=1,661.78..11,653.38 rows=34,240 width=53) (actual time=194.225..528.700 rows=35,377 loops=1)

  • Recheck Cond: (((identity_id)::text = 'bb6eb20e-efd2-4828-8286-9657822e5353'::text) AND (steps > '0'::double precision))
  • Heap Blocks: exact=834
  • Buffers: shared hit=99 read=1105
14. 194.123 194.123 ↓ 1.0 35,377 1

Bitmap Index Scan on identity_id_steps_idx (cost=0.00..1,653.22 rows=34,240 width=0) (actual time=194.116..194.123 rows=35,377 loops=1)

  • Index Cond: ((identity_id)::text = 'bb6eb20e-efd2-4828-8286-9657822e5353'::text)
  • Buffers: shared hit=4 read=366
15. 0.014 1.696 ↓ 0.0 0 1

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

  • Buffers: shared read=1
16. 1.682 1.682 ↓ 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=1.676..1.682 rows=0 loops=1)

  • Index Cond: ((identity_id)::text = 'bb6eb20e-efd2-4828-8286-9657822e5353'::text)
  • Filter: (day > (CURRENT_DATE - 365))
  • Buffers: shared read=1
17. 0.049 1,217.592 ↑ 200.0 1 1

Sort (cost=12,864.92..12,865.42 rows=200 width=226) (actual time=1,217.586..1,217.592 rows=1 loops=1)

  • Sort Key: pm_1.identity_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=1205
18. 0.283 1,217.543 ↑ 200.0 1 1

HashAggregate (cost=12,853.27..12,855.27 rows=200 width=226) (actual time=1,217.529..1,217.543 rows=1 loops=1)

  • Group Key: pm_1.identity_id
  • Buffers: shared hit=1205
19. 0.482 1,217.260 ↑ 81.0 30 1

HashAggregate (cost=12,792.55..12,816.84 rows=2,429 width=234) (actual time=1,217.014..1,217.260 rows=30 loops=1)

  • Group Key: pm_1.identity_id, (date_trunc('day'::text, pm_1.start_time)), (sum(pm_1.avg_steps))
  • Buffers: shared hit=1205
20. 0.415 1,216.778 ↑ 81.0 30 1

Append (cost=12,699.38..12,774.33 rows=2,429 width=234) (actual time=1,216.105..1,216.778 rows=30 loops=1)

  • Buffers: shared hit=1205
21. 164.713 1,216.322 ↑ 80.9 30 1

HashAggregate (cost=12,699.38..12,729.73 rows=2,428 width=53) (actual time=1,216.090..1,216.322 rows=30 loops=1)

  • Group Key: pm_1.identity_id, date_trunc('day'::text, pm_1.start_time)
  • Buffers: shared hit=1204
22. 302.283 1,051.609 ↑ 1.3 19,285 1

Subquery Scan on pm_1 (cost=11,910.18..12,517.26 rows=24,283 width=53) (actual time=589.300..1,051.609 rows=19,285 loops=1)

  • Buffers: shared hit=1204
23. 467.355 749.326 ↑ 1.3 19,285 1

HashAggregate (cost=11,910.18..12,213.72 rows=24,283 width=53) (actual time=589.280..749.326 rows=19,285 loops=1)

  • Group Key: al_1.identity_id, al_1.start_time
  • Buffers: shared hit=1204
24. 278.404 281.971 ↓ 1.0 35,377 1

Bitmap Heap Scan on activity_log_activitylog al_1 (cost=1,661.78..11,653.38 rows=34,240 width=53) (actual time=3.678..281.971 rows=35,377 loops=1)

  • Recheck Cond: (((identity_id)::text = 'bb6eb20e-efd2-4828-8286-9657822e5353'::text) AND (steps > '0'::double precision))
  • Heap Blocks: exact=834
  • Buffers: shared hit=1204
25. 3.567 3.567 ↓ 1.0 35,377 1

Bitmap Index Scan on identity_id_steps_idx (cost=0.00..1,653.22 rows=34,240 width=0) (actual time=3.560..3.567 rows=35,377 loops=1)

  • Index Cond: ((identity_id)::text = 'bb6eb20e-efd2-4828-8286-9657822e5353'::text)
  • Buffers: shared hit=370
26. 0.013 0.041 ↓ 0.0 0 1

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

  • Buffers: shared hit=1
27. 0.028 0.028 ↓ 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.021..0.028 rows=0 loops=1)

  • Index Cond: ((identity_id)::text = 'bb6eb20e-efd2-4828-8286-9657822e5353'::text)
  • Buffers: shared hit=1
28. 4.475 61.900 ↑ 200.0 1 1

GroupAggregate (cost=4,332.01..4,339.53 rows=200 width=45) (actual time=61.893..61.900 rows=1 loops=1)

  • Group Key: avs.identity_id
  • Buffers: shared hit=431
29. 7.950 57.425 ↑ 1.4 523 1

Sort (cost=4,332.01..4,333.85 rows=735 width=45) (actual time=53.638..57.425 rows=523 loops=1)

  • Sort Key: avs.identity_id
  • Sort Method: quicksort Memory: 65kB
  • Buffers: shared hit=431
30. 7.771 49.475 ↑ 1.4 523 1

Subquery Scan on avs (cost=4,273.07..4,297.02 rows=735 width=45) (actual time=21.143..49.475 rows=523 loops=1)

  • Buffers: shared hit=431
31. 12.755 41.704 ↑ 1.4 523 1

GroupAggregate (cost=4,273.07..4,289.67 rows=735 width=53) (actual time=21.129..41.704 rows=523 loops=1)

  • Group Key: al_2.identity_id, al_2.start_time
  • Buffers: shared hit=431
32. 16.240 28.949 ↓ 1.4 1,041 1

Sort (cost=4,273.07..4,274.93 rows=741 width=53) (actual time=21.074..28.949 rows=1,041 loops=1)

  • Sort Key: al_2.start_time
  • Sort Method: quicksort Memory: 195kB
  • Buffers: shared hit=431
33. 8.634 12.709 ↓ 1.4 1,041 1

Bitmap Heap Scan on activity_log_activitylog al_2 (cost=1,943.73..4,237.75 rows=741 width=53) (actual time=4.095..12.709 rows=1,041 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 = 'bb6eb20e-efd2-4828-8286-9657822e5353'::text) AND (steps > '0'::double precision))
  • Heap Blocks: exact=25
  • Buffers: shared hit=431
34. 0.033 4.075 ↓ 0.0 0 1

BitmapAnd (cost=1,943.73..1,943.73 rows=741 width=0) (actual time=4.069..4.075 rows=0 loops=1)

  • Buffers: shared hit=406
35. 0.556 0.556 ↑ 1.0 9,338 1

Bitmap Index Scan on start_time_index (cost=0.00..289.89 rows=9,345 width=0) (actual time=0.549..0.556 rows=9,338 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=36
36. 3.486 3.486 ↓ 1.0 35,377 1

Bitmap Index Scan on identity_id_steps_idx (cost=0.00..1,653.22 rows=34,240 width=0) (actual time=3.479..3.486 rows=35,377 loops=1)

  • Index Cond: ((identity_id)::text = 'bb6eb20e-efd2-4828-8286-9657822e5353'::text)
  • Buffers: shared hit=370
Planning time : 62.613 ms