explain.depesz.com

PostgreSQL's explain analyze made readable

Result: a5Gq

Settings
# exclusive inclusive rows x rows loops node
1. 0.039 2,414.363 ↑ 1,000.0 1 1

Hash Left Join (cost=7,419.96..7,438.00 rows=1,000 width=370) (actual time=2,414.308..2,414.363 rows=1 loops=1)

  • Hash Cond: ((pm.identity_id)::text = (stt.identity_id)::text)
  • Buffers: shared hit=1562
2. 0.057 2,374.395 ↑ 1,000.0 1 1

Hash Join (cost=6,207.46..6,222.82 rows=1,000 width=362) (actual time=2,374.354..2,374.395 rows=1 loops=1)

  • Hash Cond: ((pm.identity_id)::text = (st.identity_id)::text)
  • Buffers: shared hit=1539
3. 0.040 1,197.723 ↑ 1,000.0 1 1

Hash Join (cost=3,103.98..3,116.67 rows=1,000 width=386) (actual time=1,197.695..1,197.723 rows=1 loops=1)

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

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

  • Buffers: shared hit=867
5. 0.024 1,178.189 ↑ 200.0 1 1

Hash (cost=3,101.48..3,101.48 rows=200 width=226) (actual time=1,178.183..1,178.189 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=336
6. 0.259 1,178.165 ↑ 200.0 1 1

HashAggregate (cost=3,096.98..3,099.48 rows=200 width=226) (actual time=1,178.151..1,178.165 rows=1 loops=1)

  • Group Key: pm.identity_id
  • Buffers: shared hit=336
7. 0.475 1,177.906 ↑ 80.8 30 1

HashAggregate (cost=3,036.41..3,060.64 rows=2,423 width=234) (actual time=1,177.668..1,177.906 rows=30 loops=1)

  • Group Key: pm.identity_id, (date_trunc('day'::text, pm.start_time)), (sum(pm.avg_steps))
  • Buffers: shared hit=336
8. 0.423 1,177.431 ↑ 80.8 30 1

Append (cost=2,943.44..3,018.23 rows=2,423 width=234) (actual time=1,176.736..1,177.431 rows=30 loops=1)

  • Buffers: shared hit=336
9. 162.189 1,176.961 ↑ 80.7 30 1

HashAggregate (cost=2,943.44..2,973.71 rows=2,422 width=53) (actual time=1,176.721..1,176.961 rows=30 loops=1)

  • Group Key: pm.identity_id, date_trunc('day'::text, pm.start_time)
  • Buffers: shared hit=335
10. 299.281 1,014.772 ↑ 1.3 19,285 1

Subquery Scan on pm (cost=0.42..2,761.82 rows=24,216 width=53) (actual time=0.090..1,014.772 rows=19,285 loops=1)

  • Buffers: shared hit=335
11. 441.356 715.491 ↑ 1.3 19,285 1

GroupAggregate (cost=0.42..2,459.12 rows=24,216 width=53) (actual time=0.073..715.491 rows=19,285 loops=1)

  • Group Key: al.identity_id, al.start_time
  • Buffers: shared hit=335
12. 274.135 274.135 ↓ 1.0 35,376 1

Index Only Scan using identity_id_start_time_steps_idx on activity_log_activitylog al (cost=0.42..1,900.67 rows=34,100 width=53) (actual time=0.023..274.135 rows=35,376 loops=1)

  • Index Cond: (identity_id = 'bb6eb20e-efd2-4828-8286-9657822e5353'::text)
  • Heap Fetches: 20
  • Buffers: shared hit=335
13. 0.014 0.047 ↓ 0.0 0 1

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

  • Buffers: shared hit=1
14. 0.033 0.033 ↓ 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.033 rows=0 loops=1)

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

Hash (cost=3,100.97..3,100.97 rows=200 width=226) (actual time=1,176.609..1,176.615 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=336
16. 0.029 1,176.579 ↑ 200.0 1 1

Subquery Scan on st (cost=3,096.97..3,100.97 rows=200 width=226) (actual time=1,176.551..1,176.579 rows=1 loops=1)

  • Buffers: shared hit=336
17. 0.269 1,176.550 ↑ 200.0 1 1

HashAggregate (cost=3,096.97..3,098.97 rows=200 width=226) (actual time=1,176.536..1,176.550 rows=1 loops=1)

  • Group Key: pm_1.identity_id
  • Buffers: shared hit=336
18. 0.604 1,176.281 ↑ 80.8 30 1

HashAggregate (cost=3,036.40..3,060.63 rows=2,423 width=234) (actual time=1,176.017..1,176.281 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=336
19. 0.495 1,175.677 ↑ 80.8 30 1

Append (cost=2,943.44..3,018.23 rows=2,423 width=234) (actual time=1,174.795..1,175.677 rows=30 loops=1)

  • Buffers: shared hit=336
20. 162.902 1,175.121 ↑ 80.7 30 1

HashAggregate (cost=2,943.44..2,973.71 rows=2,422 width=53) (actual time=1,174.780..1,175.121 rows=30 loops=1)

  • Group Key: pm_1.identity_id, date_trunc('day'::text, pm_1.start_time)
  • Buffers: shared hit=335
21. 296.574 1,012.219 ↑ 1.3 19,285 1

Subquery Scan on pm_1 (cost=0.42..2,761.82 rows=24,216 width=53) (actual time=0.098..1,012.219 rows=19,285 loops=1)

  • Buffers: shared hit=335
22. 441.353 715.645 ↑ 1.3 19,285 1

GroupAggregate (cost=0.42..2,459.12 rows=24,216 width=53) (actual time=0.081..715.645 rows=19,285 loops=1)

  • Group Key: al_1.identity_id, al_1.start_time
  • Buffers: shared hit=335
23. 274.292 274.292 ↓ 1.0 35,376 1

Index Only Scan using identity_id_start_time_steps_idx on activity_log_activitylog al_1 (cost=0.42..1,900.67 rows=34,100 width=53) (actual time=0.029..274.292 rows=35,376 loops=1)

  • Index Cond: (identity_id = 'bb6eb20e-efd2-4828-8286-9657822e5353'::text)
  • Heap Fetches: 20
  • Buffers: shared hit=335
24. 0.016 0.061 ↓ 0.0 0 1

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

  • Buffers: shared hit=1
25. 0.045 0.045 ↓ 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.039..0.045 rows=0 loops=1)

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

Hash (cost=1,210.00..1,210.00 rows=200 width=45) (actual time=39.922..39.929 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=23
27. 0.029 39.903 ↑ 200.0 1 1

Subquery Scan on stt (cost=1,206.00..1,210.00 rows=200 width=45) (actual time=39.875..39.903 rows=1 loops=1)

  • Buffers: shared hit=23
28. 4.507 39.874 ↑ 200.0 1 1

HashAggregate (cost=1,206.00..1,208.00 rows=200 width=45) (actual time=39.860..39.874 rows=1 loops=1)

  • Group Key: al_2.identity_id
  • Buffers: shared hit=23
29. 12.569 35.367 ↑ 22.5 505 1

HashAggregate (cost=893.44..1,035.51 rows=11,366 width=53) (actual time=31.021..35.367 rows=505 loops=1)

  • Group Key: al_2.identity_id, al_2.start_time
  • Buffers: shared hit=23
30.          

CTE tz

31. 0.033 0.638 ↑ 1.0 1 1

Result (cost=0.58..0.59 rows=1 width=32) (actual time=0.623..0.638 rows=1 loops=1)

  • Buffers: shared hit=9
32.          

Initplan (forResult)

33. 0.030 0.605 ↑ 1.0 1 1

Limit (cost=0.00..0.29 rows=1 width=32) (actual time=0.578..0.605 rows=1 loops=1)

  • Buffers: shared hit=9
34. 0.575 0.575 ↑ 60,670.0 1 1

Seq Scan on activity_log_activitylog (cost=0.00..17,641.72 rows=60,670 width=32) (actual time=0.562..0.575 rows=1 loops=1)

  • Filter: ((((identity_id)::text = 'bb6eb20e-efd2-4828-8286-9657822e5353'::text) AND ((datasource)::text = 'applehealth'::text)) OR ((datasource)::text = 'googlefit'::text))
  • Rows Removed by Filter: 377
  • Buffers: shared hit=9
35. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.00..0.29 rows=1 width=32) (never executed)

36. 0.000 0.000 ↓ 0.0 0

Seq Scan on activity_log_activitylog activity_log_activitylog_1 (cost=0.00..17,641.72 rows=60,670 width=32) (never executed)

  • Filter: ((((identity_id)::text = 'bb6eb20e-efd2-4828-8286-9657822e5353'::text) AND ((datasource)::text = 'applehealth'::text)) OR ((datasource)::text = 'googlefit'::text))
37. 14.471 22.798 ↑ 11.3 1,004 1

Nested Loop (cost=0.43..807.60 rows=11,366 width=53) (actual time=0.693..22.798 rows=1,004 loops=1)

  • Buffers: shared hit=23
38. 0.670 0.670 ↑ 1.0 1 1

CTE Scan on tz (cost=0.00..0.02 rows=1 width=32) (actual time=0.638..0.670 rows=1 loops=1)

  • Buffers: shared hit=9
39. 7.657 7.657 ↑ 11.3 1,004 1

Index Only Scan using identity_id_start_time_steps_idx on activity_log_activitylog al_2 (cost=0.43..693.92 rows=11,366 width=53) (actual time=0.024..7.657 rows=1,004 loops=1)

  • Index Cond: ((identity_id = 'bb6eb20e-efd2-4828-8286-9657822e5353'::text) AND (start_time >= timezone('UTC'::text, timezone(tz."offset", date_trunc('day'::text, timezone(tz."offset", LOCALTIMESTAMP))))) AND (start_time <= CURRENT_TIMESTAMP))
  • Heap Fetches: 20
  • Buffers: shared hit=14
Planning time : 32.335 ms