explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZcUr

Settings
# exclusive inclusive rows x rows loops node
1. 0.044 3,176.400 ↑ 835.0 1 1

Hash Join (cost=15,206.10..15,223.26 rows=835 width=838) (actual time=3,176.345..3,176.400 rows=1 loops=1)

  • Hash Cond: ((pm.identity_id)::text = (pm_1.identity_id)::text)
  • Buffers: shared hit=2027
2.          

CTE userlog

3. 290.662 294.103 ↓ 1.0 35,377 1

Bitmap Heap Scan on activity_log_activitylog (cost=1,657.30..11,647.97 rows=34,178 width=134) (actual time=3.567..294.103 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
4. 3.441 3.441 ↓ 1.0 35,377 1

Bitmap Index Scan on identity_id_steps_idx (cost=0.00..1,648.76 rows=34,178 width=0) (actual time=3.434..3.441 rows=35,377 loops=1)

  • Index Cond: ((identity_id)::text = 'bb6eb20e-efd2-4828-8286-9657822e5353'::text)
  • Buffers: shared hit=370
5. 0.045 1,345.753 ↑ 835.0 1 1

Hash Join (cost=2,471.92..2,486.83 rows=835 width=612) (actual time=1,345.712..1,345.753 rows=1 loops=1)

  • Hash Cond: ((userlog.identity_id)::text = (pm.identity_id)::text)
  • Buffers: shared hit=822
6. 0.045 104.216 ↑ 835.0 1 1

Hash Join (cost=1,385.19..1,397.88 rows=835 width=386) (actual time=104.188..104.216 rows=1 loops=1)

  • Hash Cond: (ct.identity_id = (userlog.identity_id)::text)
  • Buffers: shared hit=821
7. 23.996 23.996 ↑ 1,000.0 1 1

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

  • Buffers: shared hit=821
8. 0.044 80.175 ↑ 167.0 1 1

Hash (cost=1,383.10..1,383.10 rows=167 width=226) (actual time=80.169..80.175 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
9. 4.246 80.131 ↑ 167.0 1 1

GroupAggregate (cost=1,373.46..1,381.43 rows=167 width=226) (actual time=80.118..80.131 rows=1 loops=1)

  • Group Key: userlog.identity_id
10. 13.568 75.885 ↓ 3.1 523 1

GroupAggregate (cost=1,373.46..1,377.26 rows=167 width=234) (actual time=54.119..75.885 rows=523 loops=1)

  • Group Key: userlog.identity_id, userlog.start_time
11. 17.959 62.317 ↓ 6.1 1,041 1

Sort (cost=1,373.46..1,373.89 rows=171 width=234) (actual time=54.066..62.317 rows=1,041 loops=1)

  • Sort Key: userlog.identity_id, userlog.start_time
  • Sort Method: quicksort Memory: 195kB
12. 44.358 44.358 ↓ 6.1 1,041 1

CTE Scan on userlog (cost=0.00..1,367.12 rows=171 width=234) (actual time=0.196..44.358 rows=1,041 loops=1)

  • Filter: ((start_time <= CURRENT_TIMESTAMP) AND (start_time >= timezone('UTC'::text, timezone('+10:00'::text, date_trunc('day'::text, timezone('+10:00'::text, LOCALTIMESTAMP))))))
  • Rows Removed by Filter: 34336
13. 0.026 1,241.492 ↑ 200.0 1 1

Hash (cost=1,084.22..1,084.22 rows=200 width=226) (actual time=1,241.485..1,241.492 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
14. 0.331 1,241.466 ↑ 200.0 1 1

HashAggregate (cost=1,079.72..1,082.22 rows=200 width=226) (actual time=1,241.450..1,241.466 rows=1 loops=1)

  • Group Key: pm.identity_id
  • Buffers: shared hit=1
15. 0.608 1,241.135 ↑ 11.4 30 1

HashAggregate (cost=1,071.15..1,074.58 rows=343 width=234) (actual time=1,240.837..1,241.135 rows=30 loops=1)

  • Group Key: pm.identity_id, (date_trunc('day'::text, pm.start_time)), (sum(pm.avg_steps))
  • Buffers: shared hit=1
16. 0.444 1,240.527 ↑ 11.4 30 1

Append (cost=1,050.98..1,068.58 rows=343 width=234) (actual time=1,239.729..1,240.527 rows=30 loops=1)

  • Buffers: shared hit=1
17. 166.935 1,240.035 ↑ 11.4 30 1

HashAggregate (cost=1,050.98..1,055.26 rows=342 width=234) (actual time=1,239.714..1,240.035 rows=30 loops=1)

  • Group Key: pm.identity_id, date_trunc('day'::text, pm.start_time)
18. 309.422 1,073.100 ↓ 5.6 19,285 1

Subquery Scan on pm (cost=939.90..1,025.35 rows=3,418 width=234) (actual time=600.588..1,073.100 rows=19,285 loops=1)

19. 483.990 763.678 ↓ 5.6 19,285 1

HashAggregate (cost=939.90..982.62 rows=3,418 width=234) (actual time=600.568..763.678 rows=19,285 loops=1)

  • Group Key: userlog_1.identity_id, userlog_1.start_time
20. 279.688 279.688 ↓ 1.0 35,377 1

CTE Scan on userlog userlog_1 (cost=0.00..683.56 rows=34,178 width=234) (actual time=0.008..279.688 rows=35,377 loops=1)

21. 0.016 0.048 ↓ 0.0 0 1

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

  • Buffers: shared hit=1
22. 0.032 0.032 ↓ 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.026..0.032 rows=0 loops=1)

  • Index Cond: ((identity_id)::text = 'bb6eb20e-efd2-4828-8286-9657822e5353'::text)
  • Filter: (day > (CURRENT_DATE - 365))
  • Buffers: shared hit=1
23. 0.025 1,830.603 ↑ 200.0 1 1

Hash (cost=1,083.72..1,083.72 rows=200 width=226) (actual time=1,830.597..1,830.603 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1205
24. 0.271 1,830.578 ↑ 200.0 1 1

HashAggregate (cost=1,079.72..1,081.72 rows=200 width=226) (actual time=1,830.564..1,830.578 rows=1 loops=1)

  • Group Key: pm_1.identity_id
  • Buffers: shared hit=1205
25. 0.518 1,830.307 ↑ 11.4 30 1

HashAggregate (cost=1,071.14..1,074.57 rows=343 width=234) (actual time=1,830.079..1,830.307 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
26. 0.569 1,829.789 ↑ 11.4 30 1

Append (cost=1,050.98..1,068.57 rows=343 width=234) (actual time=1,828.911..1,829.789 rows=30 loops=1)

  • Buffers: shared hit=1205
27. 165.089 1,829.174 ↑ 11.4 30 1

HashAggregate (cost=1,050.98..1,055.26 rows=342 width=234) (actual time=1,828.895..1,829.174 rows=30 loops=1)

  • Group Key: pm_1.identity_id, date_trunc('day'::text, pm_1.start_time)
  • Buffers: shared hit=1204
28. 304.756 1,664.085 ↓ 5.6 19,285 1

Subquery Scan on pm_1 (cost=939.90..1,025.35 rows=3,418 width=234) (actual time=1,201.064..1,664.085 rows=19,285 loops=1)

  • Buffers: shared hit=1204
29. 487.835 1,359.329 ↓ 5.6 19,285 1

HashAggregate (cost=939.90..982.62 rows=3,418 width=234) (actual time=1,201.043..1,359.329 rows=19,285 loops=1)

  • Group Key: userlog_2.identity_id, userlog_2.start_time
  • Buffers: shared hit=1204
30. 871.494 871.494 ↓ 1.0 35,377 1

CTE Scan on userlog userlog_2 (cost=0.00..683.56 rows=34,178 width=234) (actual time=3.637..871.494 rows=35,377 loops=1)

  • Buffers: shared hit=1204
31. 0.025 0.046 ↓ 0.0 0 1

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

  • Buffers: shared hit=1
32. 0.021 0.021 ↓ 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.014..0.021 rows=0 loops=1)

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