explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NzJl

Settings
# exclusive inclusive rows x rows loops node
1. 0.192 3,135.480 ↑ 1,000.0 1 1

Hash Join (cost=72,942.25..72,989.75 rows=1,000 width=689) (actual time=3,135.307..3,135.480 rows=1 loops=1)

  • Hash Cond: (ct.identity_id = (pd_1.identity_id)::text)
  • Buffers: shared hit=6706
2. 31.798 31.798 ↑ 1,000.0 1 1

Function Scan on crosstab ct (cost=0.00..10.00 rows=1,000 width=192) (actual time=31.765..31.798 rows=1 loops=1)

  • Buffers: shared hit=2325
3. 0.060 3,103.490 ↑ 200.0 1 1

Hash (cost=72,939.75..72,939.75 rows=200 width=497) (actual time=3,103.483..3,103.490 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=4381
4. 0.037 3,103.430 ↑ 200.0 1 1

Merge Left Join (cost=72,888.00..72,939.75 rows=200 width=497) (actual time=3,103.389..3,103.430 rows=1 loops=1)

  • Merge Cond: ((pd.identity_id)::text = (al_2.identity_id)::text)
  • Buffers: shared hit=4381
5. 0.045 2,436.666 ↑ 200.0 1 1

Merge Join (cost=48,515.54..48,563.79 rows=200 width=452) (actual time=2,436.639..2,436.666 rows=1 loops=1)

  • Merge Cond: ((pd.identity_id)::text = (pd_1.identity_id)::text)
  • Buffers: shared hit=2912
6. 0.274 1,228.381 ↑ 200.0 1 1

GroupAggregate (cost=24,257.77..24,278.65 rows=200 width=226) (actual time=1,228.361..1,228.381 rows=1 loops=1)

  • Group Key: pd.identity_id
  • Buffers: shared hit=1456
7. 0.441 1,228.107 ↑ 81.7 30 1

Sort (cost=24,257.77..24,263.90 rows=2,450 width=226) (actual time=1,227.896..1,228.107 rows=30 loops=1)

  • Sort Key: pd.identity_id
  • Sort Method: quicksort Memory: 27kB
  • Buffers: shared hit=1456
8. 0.423 1,227.666 ↑ 81.7 30 1

Subquery Scan on pd (cost=24,070.85..24,119.85 rows=2,450 width=226) (actual time=1,227.025..1,227.666 rows=30 loops=1)

  • Buffers: shared hit=1456
9. 0.506 1,227.243 ↑ 81.7 30 1

HashAggregate (cost=24,070.85..24,095.35 rows=2,450 width=234) (actual time=1,227.010..1,227.243 rows=30 loops=1)

  • Group Key: pm.identity_id, (date_trunc('day'::text, pm.start_time)), (sum(pm.avg_steps))
  • Buffers: shared hit=1456
10. 0.442 1,226.737 ↑ 81.7 30 1

Append (cost=23,976.94..24,052.48 rows=2,450 width=234) (actual time=1,226.050..1,226.737 rows=30 loops=1)

  • Buffers: shared hit=1456
11. 166.263 1,226.250 ↑ 81.6 30 1

HashAggregate (cost=23,976.94..24,007.55 rows=2,449 width=53) (actual time=1,226.013..1,226.250 rows=30 loops=1)

  • Group Key: pm.identity_id, date_trunc('day'::text, pm.start_time)
  • Buffers: shared hit=1455
12. 308.057 1,059.987 ↑ 1.3 19,285 1

Subquery Scan on pm (cost=23,181.08..23,793.28 rows=24,488 width=53) (actual time=590.548..1,059.987 rows=19,285 loops=1)

  • Buffers: shared hit=1455
13. 468.277 751.930 ↑ 1.3 19,285 1

HashAggregate (cost=23,181.08..23,487.18 rows=24,488 width=53) (actual time=590.528..751.930 rows=19,285 loops=1)

  • Group Key: al.identity_id, al.start_time
  • Buffers: shared hit=1455
14. 277.737 283.653 ↓ 1.0 35,377 1

Bitmap Heap Scan on activity_log_activitylog al (cost=3,065.14..22,921.03 rows=34,673 width=53) (actual time=6.030..283.653 rows=35,377 loops=1)

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

Bitmap Index Scan on identity_id_steps_idx (cost=0.00..3,056.47 rows=34,673 width=0) (actual time=5.909..5.916 rows=35,377 loops=1)

  • Index Cond: ((identity_id)::text = 'bb6eb20e-efd2-4828-8286-9657822e5353'::text)
  • Buffers: shared hit=625
16. 0.015 0.045 ↓ 0.0 0 1

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

  • Buffers: shared hit=1
17. 0.030 0.030 ↓ 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.024..0.030 rows=0 loops=1)

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

GroupAggregate (cost=24,257.76..24,278.14 rows=200 width=226) (actual time=1,208.233..1,208.240 rows=1 loops=1)

  • Group Key: pd_1.identity_id
  • Buffers: shared hit=1456
19. 0.459 1,208.002 ↑ 81.7 30 1

Sort (cost=24,257.76..24,263.89 rows=2,450 width=226) (actual time=1,207.784..1,208.002 rows=30 loops=1)

  • Sort Key: pd_1.identity_id
  • Sort Method: quicksort Memory: 27kB
  • Buffers: shared hit=1456
20. 0.423 1,207.543 ↑ 81.7 30 1

Subquery Scan on pd_1 (cost=24,070.85..24,119.85 rows=2,450 width=226) (actual time=1,206.902..1,207.543 rows=30 loops=1)

  • Buffers: shared hit=1456
21. 0.500 1,207.120 ↑ 81.7 30 1

HashAggregate (cost=24,070.85..24,095.35 rows=2,450 width=234) (actual time=1,206.887..1,207.120 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=1456
22. 0.473 1,206.620 ↑ 81.7 30 1

Append (cost=23,976.94..24,052.47 rows=2,450 width=234) (actual time=1,205.819..1,206.620 rows=30 loops=1)

  • Buffers: shared hit=1456
23. 161.753 1,206.083 ↑ 81.6 30 1

HashAggregate (cost=23,976.94..24,007.55 rows=2,449 width=53) (actual time=1,205.805..1,206.083 rows=30 loops=1)

  • Group Key: pm_1.identity_id, date_trunc('day'::text, pm_1.start_time)
  • Buffers: shared hit=1455
24. 298.387 1,044.330 ↑ 1.3 19,285 1

Subquery Scan on pm_1 (cost=23,181.08..23,793.28 rows=24,488 width=53) (actual time=591.374..1,044.330 rows=19,285 loops=1)

  • Buffers: shared hit=1455
25. 463.523 745.943 ↑ 1.3 19,285 1

HashAggregate (cost=23,181.08..23,487.18 rows=24,488 width=53) (actual time=591.354..745.943 rows=19,285 loops=1)

  • Group Key: al_1.identity_id, al_1.start_time
  • Buffers: shared hit=1455
26. 277.659 282.420 ↓ 1.0 35,377 1

Bitmap Heap Scan on activity_log_activitylog al_1 (cost=3,065.14..22,921.03 rows=34,673 width=53) (actual time=4.919..282.420 rows=35,377 loops=1)

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

Bitmap Index Scan on identity_id_steps_idx (cost=0.00..3,056.47 rows=34,673 width=0) (actual time=4.753..4.761 rows=35,377 loops=1)

  • Index Cond: ((identity_id)::text = 'bb6eb20e-efd2-4828-8286-9657822e5353'::text)
  • Buffers: shared hit=625
28. 0.000 0.064 ↓ 0.0 0 1

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

  • Buffers: shared hit=1
29. 0.072 0.072 ↓ 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.072 rows=0 loops=1)

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

Sort (cost=24,372.47..24,372.97 rows=200 width=45) (actual time=666.721..666.727 rows=1 loops=1)

  • Sort Key: al_2.identity_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=1469
31. 4.542 666.680 ↑ 200.0 1 1

HashAggregate (cost=24,360.82..24,362.82 rows=200 width=45) (actual time=666.665..666.680 rows=1 loops=1)

  • Group Key: al_2.identity_id
  • Buffers: shared hit=1469
32. 13.482 662.138 ↑ 22.8 505 1

HashAggregate (cost=24,044.43..24,188.25 rows=11,505 width=53) (actual time=657.363..662.138 rows=505 loops=1)

  • Group Key: al_2.identity_id, al_2.start_time
  • Buffers: shared hit=1469
33.          

CTE tz

34. 0.030 0.710 ↑ 1.0 1 1

Result (cost=0.88..0.89 rows=1 width=32) (actual time=0.696..0.710 rows=1 loops=1)

  • Buffers: shared hit=14
35.          

Initplan (forResult)

36. 0.030 0.680 ↑ 1.0 1 1

Limit (cost=0.00..0.44 rows=1 width=32) (actual time=0.651..0.680 rows=1 loops=1)

  • Buffers: shared hit=14
37. 0.650 0.650 ↑ 61,535.0 1 1

Seq Scan on activity_log_activitylog (cost=0.00..27,131.37 rows=61,535 width=32) (actual time=0.635..0.650 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: 341
  • Buffers: shared hit=14
38. 0.000 0.000 ↓ 0.0 0

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

39. 0.000 0.000 ↓ 0.0 0

Seq Scan on activity_log_activitylog activity_log_activitylog_1 (cost=0.00..27,131.37 rows=61,535 width=32) (never executed)

  • Filter: ((((identity_id)::text = 'bb6eb20e-efd2-4828-8286-9657822e5353'::text) AND ((datasource)::text = 'applehealth'::text)) OR ((datasource)::text = 'googlefit'::text))
40. 354.625 648.656 ↑ 11.4 1,005 1

Nested Loop (cost=3,065.10..23,957.26 rows=11,505 width=53) (actual time=4.644..648.656 rows=1,005 loops=1)

  • Join Filter: (al_2.start_time >= timezone('UTC'::text, timezone(tz."offset", date_trunc('day'::text, timezone(tz."offset", LOCALTIMESTAMP)))))
  • Rows Removed by Join Filter: 34372
  • Buffers: shared hit=1469
41. 0.741 0.741 ↑ 1.0 1 1

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

  • Buffers: shared hit=14
42. 290.421 293.290 ↓ 1.0 35,377 1

Bitmap Heap Scan on activity_log_activitylog al_2 (cost=3,065.10..23,094.36 rows=34,515 width=53) (actual time=2.957..293.290 rows=35,377 loops=1)

  • Recheck Cond: (((identity_id)::text = 'bb6eb20e-efd2-4828-8286-9657822e5353'::text) AND (steps > '0'::double precision))
  • Filter: (start_time <= CURRENT_TIMESTAMP)
  • Heap Blocks: exact=830
  • Buffers: shared hit=1455
43. 2.869 2.869 ↓ 1.0 35,377 1

Bitmap Index Scan on identity_id_steps_idx (cost=0.00..3,056.47 rows=34,673 width=0) (actual time=2.863..2.869 rows=35,377 loops=1)

  • Index Cond: ((identity_id)::text = 'bb6eb20e-efd2-4828-8286-9657822e5353'::text)
  • Buffers: shared hit=625