explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tDCK

Settings
# exclusive inclusive rows x rows loops node
1. 0.252 459.611 ↑ 22.4 9 1

Sort (cost=1,304.60..1,305.10 rows=202 width=148) (actual time=459.525..459.611 rows=9 loops=1)

  • Sort Key: (CASE (timeline.sort)::text WHEN '[TOTAL]'::text THEN 0 ELSE 1 END), (COALESCE(lpad((timeline.sort)::text, 10, '0'::text), '[TOTAL]'::text))
  • Sort Method: quicksort Memory: 26kB
2. 0.258 459.359 ↑ 22.4 9 1

Hash Full Join (cost=961.94..1,296.87 rows=202 width=148) (actual time=458.834..459.359 rows=9 loops=1)

  • Hash Cond: (COALESCE(data_stats_session.day, (COALESCE((COALESCE((((date_trunc('day'::text, _hyper_3_42_chunk_1.created_at))::timestamp without time zone)::date)::text, ''::text)), '[TOTAL]'::text)), (COALESCE((COALESCE((((date_trunc('day'::text, _hyper_5_43_chunk.created_at))::timestamp without time zone)::date)::text, ''::text)), '[TOTAL]'::text))) = timeline.day)
3. 2.751 446.960 ↑ 22.4 9 1

Hash Full Join (cost=825.97..1,150.28 rows=202 width=176) (actual time=446.642..446.960 rows=9 loops=1)

  • Hash Cond: ((COALESCE((COALESCE((((date_trunc('day'::text, _hyper_5_43_chunk.created_at))::timestamp without time zone)::date)::text, ''::text)), '[TOTAL]'::text)) = COALESCE(data_stats_session.day, (COALESCE((COALESCE((((date_trunc('day'::text, _hyper_3_42_chunk_1.created_at))::timestamp without time zone)::date)::text, ''::text)), '[TOTAL]'::text))))
4. 6.918 245.915 ↑ 22.3 9 1

MixedAggregate (cost=277.02..588.75 rows=201 width=72) (actual time=245.803..245.915 rows=9 loops=1)

  • Hash Key: (COALESCE((((date_trunc('day'::text, _hyper_5_43_chunk.created_at))::timestamp without time zone)::date)::text, ''::text))
  • Group Key: ()
5. 12.726 238.997 ↑ 1.6 543 1

Hash Join (cost=277.02..576.16 rows=846 width=52) (actual time=54.185..238.997 rows=543 loops=1)

  • Hash Cond: (_hyper_5_43_chunk.page_view_id = _hyper_3_42_chunk.id)
6. 15.018 187.241 ↓ 2.6 678 1

Hash Join (cost=62.37..331.36 rows=263 width=68) (actual time=14.744..187.241 rows=678 loops=1)

  • Hash Cond: (_hyper_5_43_chunk.session_id = _hyper_2_41_chunk.id)
7. 13.778 168.296 ↓ 1.0 686 1

Append (cost=0.29..264.09 rows=682 width=84) (actual time=10.751..168.296 rows=686 loops=1)

8. 154.518 154.518 ↓ 1.0 686 1

Index Scan using _hyper_5_43_chunk_stats_event_created_at_idx on _hyper_5_43_chunk (cost=0.29..260.68 rows=682 width=84) (actual time=10.732..154.518 rows=686 loops=1)

  • Index Cond: ((created_at >= '2020-08-23 22:00:00+00'::timestamp with time zone) AND (created_at <= '2020-08-30 21:59:59+00'::timestamp with time zone))
  • Filter: ((property_id = 1) AND (event_id = 1))
  • Rows Removed by Filter: 3,646
9. 0.965 3.927 ↓ 1.1 83 1

Hash (cost=61.12..61.12 rows=77 width=16) (actual time=3.919..3.927 rows=83 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
10. 1.647 2.962 ↓ 1.1 83 1

Append (cost=10.77..61.12 rows=77 width=16) (actual time=0.280..2.962 rows=83 loops=1)

11. 1.102 1.315 ↓ 1.1 83 1

Bitmap Heap Scan on _hyper_2_41_chunk (cost=10.77..60.73 rows=77 width=16) (actual time=0.257..1.315 rows=83 loops=1)

  • Recheck Cond: ((created_at >= '2020-08-22 22:00:00+00'::timestamp with time zone) AND (created_at <= '2020-08-30 21:59:59+00'::timestamp with time zone))
  • Filter: ((runtime_active > 1000) AND (property_id = 1))
  • Rows Removed by Filter: 162
  • Heap Blocks: exact=17
12. 0.213 0.213 ↓ 1.1 270 1

Bitmap Index Scan on _hyper_2_41_chunk_stats_session_created_at_idx (cost=0.00..10.76 rows=248 width=0) (actual time=0.205..0.213 rows=270 loops=1)

  • Index Cond: ((created_at >= '2020-08-22 22:00:00+00'::timestamp with time zone) AND (created_at <= '2020-08-30 21:59:59+00'::timestamp with time zone))
13. 7.999 39.030 ↓ 1.1 681 1

Hash (cost=206.61..206.61 rows=643 width=16) (actual time=39.022..39.030 rows=681 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 40kB
14. 14.154 31.031 ↓ 1.1 681 1

Append (cost=0.29..206.61 rows=643 width=16) (actual time=0.094..31.031 rows=681 loops=1)

15. 16.877 16.877 ↓ 1.1 681 1

Index Scan using _hyper_3_42_chunk_stats_page_view_created_at_idx on _hyper_3_42_chunk (cost=0.29..203.40 rows=643 width=16) (actual time=0.075..16.877 rows=681 loops=1)

  • Index Cond: ((created_at >= '2020-08-22 22:00:00+00'::timestamp with time zone) AND (created_at <= '2020-08-30 21:59:59+00'::timestamp with time zone))
  • Filter: ((runtime_active > 1000) AND (property_id = 1))
  • Rows Removed by Filter: 1,764
16. 0.122 198.294 ↑ 22.3 9 1

Hash (cost=546.43..546.43 rows=201 width=104) (actual time=198.286..198.294 rows=9 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
17. 0.210 198.172 ↑ 22.3 9 1

Hash Full Join (cost=340.81..546.43 rows=201 width=104) (actual time=197.920..198.172 rows=9 loops=1)

  • Hash Cond: ((COALESCE((COALESCE((((date_trunc('day'::text, _hyper_3_42_chunk_1.created_at))::timestamp without time zone)::date)::text, ''::text)), '[TOTAL]'::text)) = COALESCE(data_stats_session.day))
18. 6.306 43.858 ↑ 22.3 9 1

MixedAggregate (cost=62.37..263.31 rows=201 width=80) (actual time=43.768..43.858 rows=9 loops=1)

  • Hash Key: (COALESCE((((date_trunc('day'::text, _hyper_3_42_chunk_1.created_at))::timestamp without time zone)::date)::text, ''::text))
  • Group Key: ()
19. 10.318 37.552 ↓ 2.5 551 1

Hash Join (cost=62.37..257.00 rows=217 width=52) (actual time=8.162..37.552 rows=551 loops=1)

  • Hash Cond: (_hyper_3_42_chunk_1.session_id = _hyper_2_41_chunk_1.id)
20. 10.109 19.285 ↑ 1.0 561 1

Append (cost=0.29..190.64 rows=563 width=68) (actual time=0.114..19.285 rows=561 loops=1)

21. 9.176 9.176 ↑ 1.0 561 1

Index Scan using _hyper_3_42_chunk_stats_page_view_created_at_idx on _hyper_3_42_chunk _hyper_3_42_chunk_1 (cost=0.29..187.83 rows=563 width=68) (actual time=0.097..9.176 rows=561 loops=1)

  • Index Cond: ((created_at >= '2020-08-23 22:00:00+00'::timestamp with time zone) AND (created_at <= '2020-08-30 21:59:59+00'::timestamp with time zone))
  • Filter: ((runtime_active > 1000) AND (property_id = 1))
  • Rows Removed by Filter: 1,605
22. 1.011 7.949 ↓ 1.1 83 1

Hash (cost=61.12..61.12 rows=77 width=16) (actual time=7.941..7.949 rows=83 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
23. 1.399 6.938 ↓ 1.1 83 1

Append (cost=10.77..61.12 rows=77 width=16) (actual time=3.827..6.938 rows=83 loops=1)

24. 5.464 5.539 ↓ 1.1 83 1

Bitmap Heap Scan on _hyper_2_41_chunk _hyper_2_41_chunk_1 (cost=10.77..60.73 rows=77 width=16) (actual time=3.808..5.539 rows=83 loops=1)

  • Recheck Cond: ((created_at >= '2020-08-22 22:00:00+00'::timestamp with time zone) AND (created_at <= '2020-08-30 21:59:59+00'::timestamp with time zone))
  • Filter: ((runtime_active > 1000) AND (property_id = 1))
  • Rows Removed by Filter: 162
  • Heap Blocks: exact=17
25. 0.075 0.075 ↓ 1.1 270 1

Bitmap Index Scan on _hyper_2_41_chunk_stats_session_created_at_idx (cost=0.00..10.76 rows=248 width=0) (actual time=0.067..0.075 rows=270 loops=1)

  • Index Cond: ((created_at >= '2020-08-22 22:00:00+00'::timestamp with time zone) AND (created_at <= '2020-08-30 21:59:59+00'::timestamp with time zone))
26. 0.123 154.104 ↑ 21.1 9 1

Hash (cost=276.07..276.07 rows=190 width=56) (actual time=154.097..154.104 rows=9 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
27. 0.169 153.981 ↑ 21.1 9 1

Subquery Scan on data_stats_session (cost=258.09..276.07 rows=190 width=56) (actual time=132.659..153.981 rows=9 loops=1)

28. 8.322 153.812 ↑ 21.1 9 1

GroupAggregate (cost=258.09..274.17 rows=190 width=88) (actual time=132.644..153.812 rows=9 loops=1)

  • Group Key: (COALESCE((((date_trunc('day'::text, _hyper_2_41_chunk_2.created_at))::timestamp without time zone)::date)::text, ''::text))
  • Group Key: ()
29. 11.502 145.490 ↓ 2.9 550 1

WindowAgg (cost=258.09..264.23 rows=189 width=66) (actual time=129.412..145.490 rows=550 loops=1)

30. 11.726 133.988 ↓ 2.9 550 1

Sort (cost=258.09..258.56 rows=189 width=58) (actual time=129.139..133.988 rows=550 loops=1)

  • Sort Key: (COALESCE((((date_trunc('day'::text, _hyper_2_41_chunk_2.created_at))::timestamp without time zone)::date)::text, ''::text)), _hyper_2_41_chunk_2.id
  • Sort Method: quicksort Memory: 67kB
31. 12.159 122.262 ↓ 2.9 550 1

Hash Join (cost=61.26..250.94 rows=189 width=58) (actual time=22.573..122.262 rows=550 loops=1)

  • Hash Cond: (_hyper_3_42_chunk_2.session_id = _hyper_2_41_chunk_2.id)
32. 10.016 92.876 ↑ 1.0 561 1

Append (cost=0.29..183.60 rows=563 width=16) (actual time=5.283..92.876 rows=561 loops=1)

33. 82.860 82.860 ↑ 1.0 561 1

Index Scan using _hyper_3_42_chunk_stats_page_view_created_at_idx on _hyper_3_42_chunk _hyper_3_42_chunk_2 (cost=0.29..180.79 rows=563 width=16) (actual time=5.265..82.860 rows=561 loops=1)

  • Index Cond: ((created_at >= '2020-08-23 22:00:00+00'::timestamp with time zone) AND (created_at <= '2020-08-30 21:59:59+00'::timestamp with time zone))
  • Filter: ((runtime_active > 1000) AND (property_id = 1))
  • Rows Removed by Filter: 1,605
34. 1.134 17.227 ↓ 1.0 68 1

Hash (cost=60.14..60.14 rows=67 width=34) (actual time=17.219..17.227 rows=68 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
35. 1.486 16.093 ↓ 1.0 68 1

Append (cost=10.46..60.14 rows=67 width=34) (actual time=3.282..16.093 rows=68 loops=1)

36. 12.250 14.607 ↓ 1.0 68 1

Bitmap Heap Scan on _hyper_2_41_chunk _hyper_2_41_chunk_2 (cost=10.46..59.80 rows=67 width=34) (actual time=3.264..14.607 rows=68 loops=1)

  • Recheck Cond: ((created_at >= '2020-08-23 22:00:00+00'::timestamp with time zone) AND (created_at <= '2020-08-30 21:59:59+00'::timestamp with time zone))
  • Filter: ((runtime_active > 1000) AND (property_id = 1))
  • Rows Removed by Filter: 149
  • Heap Blocks: exact=15
37. 2.357 2.357 ↓ 1.1 238 1

Bitmap Index Scan on _hyper_2_41_chunk_stats_session_created_at_idx (cost=0.00..10.45 rows=217 width=0) (actual time=2.349..2.357 rows=238 loops=1)

  • Index Cond: ((created_at >= '2020-08-23 22:00:00+00'::timestamp with time zone) AND (created_at <= '2020-08-30 21:59:59+00'::timestamp with time zone))
38. 0.096 12.141 ↑ 25.0 8 1

Hash (cost=133.48..133.48 rows=200 width=40) (actual time=12.133..12.141 rows=8 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
39. 0.147 12.045 ↑ 25.0 8 1

Subquery Scan on timeline (cost=125.98..133.48 rows=200 width=40) (actual time=11.719..12.045 rows=8 loops=1)

40. 0.162 11.898 ↑ 25.0 8 1

WindowAgg (cost=125.98..131.48 rows=200 width=48) (actual time=11.702..11.898 rows=8 loops=1)

41. 0.190 11.736 ↑ 25.0 8 1

Sort (cost=125.98..126.48 rows=200 width=52) (actual time=11.663..11.736 rows=8 loops=1)

  • Sort Key: (first_value(tl.dt) OVER (?))
  • Sort Method: quicksort Memory: 25kB
42. 1.654 11.546 ↑ 25.0 8 1

HashAggregate (cost=112.33..118.33 rows=200 width=52) (actual time=11.468..11.546 rows=8 loops=1)

  • Group Key: first_value(tl.dt) OVER (?), ((((date_trunc('day'::text, tl.dt))::timestamp without time zone)::date))::text
43. 3.426 9.892 ↑ 6.0 168 1

WindowAgg (cost=67.33..107.33 rows=1,000 width=52) (actual time=5.035..9.892 rows=168 loops=1)

44. 3.712 6.466 ↑ 6.0 168 1

Sort (cost=67.33..69.83 rows=1,000 width=12) (actual time=4.975..6.466 rows=168 loops=1)

  • Sort Key: (((date_trunc('day'::text, tl.dt))::timestamp without time zone)::date), tl.dt
  • Sort Method: quicksort Memory: 32kB
45. 2.754 2.754 ↑ 6.0 168 1

Function Scan on generate_series tl (cost=0.00..17.50 rows=1,000 width=12) (actual time=0.905..2.754 rows=168 loops=1)

Planning time : 111.772 ms
Execution time : 463.787 ms