explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 20kU

Settings
# exclusive inclusive rows x rows loops node
1. 0.053 1,117.221 ↑ 1,000.0 1 1

Hash Join (cost=10,289.07..10,339.25 rows=1,000 width=657) (actual time=1,117.179..1,117.221 rows=1 loops=1)

  • Hash Cond: ((al.identity_id)::text = (pd_1.identity_id)::text)
  • Buffers: shared hit=4387 read=1
2. 0.060 48.393 ↑ 1,000.0 1 1

Hash Join (cost=1,863.74..1,876.42 rows=1,000 width=205) (actual time=48.365..48.393 rows=1 loops=1)

  • Hash Cond: (ct.identity_id = (al.identity_id)::text)
  • Buffers: shared hit=625 read=1
3. 18.400 18.400 ↑ 1,000.0 1 1

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

  • Buffers: shared hit=585
4. 0.027 29.933 ↑ 200.0 1 1

Hash (cost=1,861.24..1,861.24 rows=200 width=45) (actual time=29.926..29.933 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=40 read=1
5. 4.009 29.906 ↑ 200.0 1 1

HashAggregate (cost=1,857.24..1,859.24 rows=200 width=45) (actual time=29.891..29.906 rows=1 loops=1)

  • Group Key: al.identity_id
  • Buffers: shared hit=40 read=1
6. 8.167 25.897 ↓ 1.1 510 1

Sort (cost=1,849.42..1,850.53 rows=447 width=53) (actual time=22.317..25.897 rows=510 loops=1)

  • Sort Key: al.start_time
  • Sort Method: quicksort Memory: 96kB
  • Buffers: shared hit=40 read=1
7. 10.154 17.730 ↓ 1.1 510 1

HashAggregate (cost=1,824.15..1,829.74 rows=447 width=53) (actual time=13.014..17.730 rows=510 loops=1)

  • Group Key: al.start_time, al.identity_id
  • Buffers: shared hit=40 read=1
8. 5.079 7.576 ↓ 1.1 510 1

Bitmap Heap Scan on activity_log_activitylog al (cost=55.15..1,820.75 rows=453 width=53) (actual time=2.560..7.576 rows=510 loops=1)

  • Recheck Cond: (((identity_id)::text = '79de8463-d571-4c50-85e8-45479b50451f'::text) AND (start_time >= timezone('UTC'::text, timezone('+10:00'::text, date_trunc('day'::text, timezone('+10:00'::text, LOCALTIMESTAMP))))) AND (start_time <= CURRENT_TIMESTAMP))
  • Filter: (steps > '0'::double precision)
  • Rows Removed by Filter: 680
  • Heap Blocks: exact=28
  • Buffers: shared hit=40 read=1
9. 2.497 2.497 ↓ 1.0 1,190 1

Bitmap Index Scan on identity_id_start_time_idx (cost=0.00..55.03 rows=1,168 width=0) (actual time=2.490..2.497 rows=1,190 loops=1)

  • Index Cond: (((identity_id)::text = '79de8463-d571-4c50-85e8-45479b50451f'::text) AND (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=12 read=1
10. 0.037 1,068.775 ↑ 200.0 1 1

Hash (cost=8,422.83..8,422.83 rows=200 width=452) (actual time=1,068.768..1,068.775 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=3762
11. 0.033 1,068.738 ↑ 200.0 1 1

Merge Join (cost=8,399.20..8,422.83 rows=200 width=452) (actual time=1,068.711..1,068.738 rows=1 loops=1)

  • Merge Cond: ((pd.identity_id)::text = (pd_1.identity_id)::text)
  • Buffers: shared hit=3762
12. 0.106 527.142 ↑ 200.0 1 1

GroupAggregate (cost=4,199.60..4,208.17 rows=200 width=226) (actual time=527.129..527.142 rows=1 loops=1)

  • Group Key: pd.identity_id
  • Buffers: shared hit=1881
13. 0.169 527.036 ↑ 73.5 11 1

Sort (cost=4,199.60..4,201.62 rows=809 width=226) (actual time=526.955..527.036 rows=11 loops=1)

  • Sort Key: pd.identity_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=1881
14. 0.163 526.867 ↑ 73.5 11 1

Subquery Scan on pd (cost=4,144.35..4,160.53 rows=809 width=226) (actual time=526.632..526.867 rows=11 loops=1)

  • Buffers: shared hit=1881
15. 0.180 526.704 ↑ 73.5 11 1

HashAggregate (cost=4,144.35..4,152.44 rows=809 width=234) (actual time=526.617..526.704 rows=11 loops=1)

  • Group Key: pm.identity_id, (date_trunc('day'::text, pm.start_time)), (sum(pm.avg_steps))
  • Buffers: shared hit=1881
16. 0.161 526.524 ↑ 73.5 11 1

Append (cost=4,115.95..4,138.28 rows=809 width=234) (actual time=526.256..526.524 rows=11 loops=1)

  • Buffers: shared hit=1881
17. 0.172 526.322 ↑ 73.5 11 1

Sort (cost=4,115.95..4,117.97 rows=808 width=53) (actual time=526.241..526.322 rows=11 loops=1)

  • Sort Key: (date_trunc('day'::text, pm.start_time))
  • Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=1880
18. 56.113 526.150 ↑ 73.5 11 1

HashAggregate (cost=4,066.83..4,076.93 rows=808 width=53) (actual time=526.061..526.150 rows=11 loops=1)

  • Group Key: date_trunc('day'::text, pm.start_time), pm.identity_id
  • Buffers: shared hit=1880
19. 105.659 470.037 ↑ 1.3 6,385 1

Subquery Scan on pm (cost=3,884.94..4,006.20 rows=8,084 width=53) (actual time=313.405..470.037 rows=6,385 loops=1)

  • Buffers: shared hit=1880
20. 106.364 364.378 ↑ 1.3 6,385 1

Sort (cost=3,884.94..3,905.15 rows=8,084 width=53) (actual time=313.383..364.378 rows=6,385 loops=1)

  • Sort Key: al_1.start_time
  • Sort Method: quicksort Memory: 1090kB
  • Buffers: shared hit=1880
21. 153.900 258.014 ↑ 1.3 6,385 1

HashAggregate (cost=3,259.20..3,360.26 rows=8,084 width=53) (actual time=204.167..258.014 rows=6,385 loops=1)

  • Group Key: al_1.start_time, al_1.identity_id
  • Buffers: shared hit=1880
22. 104.114 104.114 ↓ 1.0 11,113 1

Seq Scan on activity_log_activitylog al_1 (cost=0.00..3,176.00 rows=11,094 width=53) (actual time=0.659..104.114 rows=11,113 loops=1)

  • Filter: ((steps > '0'::double precision) AND ((identity_id)::text = '79de8463-d571-4c50-85e8-45479b50451f'::text))
  • Rows Removed by Filter: 75288
  • Buffers: shared hit=1880
23. 0.014 0.041 ↓ 0.0 0 1

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

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

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

GroupAggregate (cost=4,199.59..4,207.66 rows=200 width=226) (actual time=541.557..541.563 rows=1 loops=1)

  • Group Key: pd_1.identity_id
  • Buffers: shared hit=1881
26. 0.175 541.464 ↑ 73.5 11 1

Sort (cost=4,199.59..4,201.62 rows=809 width=226) (actual time=541.381..541.464 rows=11 loops=1)

  • Sort Key: pd_1.identity_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=1881
27. 0.166 541.289 ↑ 73.5 11 1

Subquery Scan on pd_1 (cost=4,144.34..4,160.52 rows=809 width=226) (actual time=541.049..541.289 rows=11 loops=1)

  • Buffers: shared hit=1881
28. 0.183 541.123 ↑ 73.5 11 1

HashAggregate (cost=4,144.34..4,152.43 rows=809 width=234) (actual time=541.034..541.123 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=1881
29. 0.164 540.940 ↑ 73.5 11 1

Append (cost=4,115.95..4,138.27 rows=809 width=234) (actual time=540.665..540.940 rows=11 loops=1)

  • Buffers: shared hit=1881
30. 0.178 540.732 ↑ 73.5 11 1

Sort (cost=4,115.95..4,117.97 rows=808 width=53) (actual time=540.650..540.732 rows=11 loops=1)

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

HashAggregate (cost=4,066.83..4,076.93 rows=808 width=53) (actual time=540.461..540.554 rows=11 loops=1)

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

Subquery Scan on pm_1 (cost=3,884.94..4,006.20 rows=8,084 width=53) (actual time=342.097..487.716 rows=6,385 loops=1)

  • Buffers: shared hit=1880
33. 116.550 389.424 ↑ 1.3 6,385 1

Sort (cost=3,884.94..3,905.15 rows=8,084 width=53) (actual time=342.075..389.424 rows=6,385 loops=1)

  • Sort Key: al_2.start_time
  • Sort Method: quicksort Memory: 1090kB
  • Buffers: shared hit=1880
34. 169.448 272.874 ↑ 1.3 6,385 1

HashAggregate (cost=3,259.20..3,360.26 rows=8,084 width=53) (actual time=203.563..272.874 rows=6,385 loops=1)

  • Group Key: al_2.start_time, al_2.identity_id
  • Buffers: shared hit=1880
35. 103.426 103.426 ↓ 1.0 11,113 1

Seq Scan on activity_log_activitylog al_2 (cost=0.00..3,176.00 rows=11,094 width=53) (actual time=0.843..103.426 rows=11,113 loops=1)

  • Filter: ((steps > '0'::double precision) AND ((identity_id)::text = '79de8463-d571-4c50-85e8-45479b50451f'::text))
  • Rows Removed by Filter: 75288
  • Buffers: shared hit=1880
36. 0.015 0.044 ↓ 0.0 0 1

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

  • Buffers: shared hit=1
37. 0.029 0.029 ↓ 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.023..0.029 rows=0 loops=1)

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