explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qygZ

Settings
# exclusive inclusive rows x rows loops node
1. 434,436.319 434,436.319 ↓ 34.7 3,686,293 1

CTE Scan on metric_calc (cost=4,006,815.46..4,008,938.72 rows=106,163 width=324) (actual time=416,151.021..434,436.319 rows=3,686,293 loops=1)

2.          

CTE base

3. 3,997.719 78,821.148 ↓ 9.5 10,119,520 1

Append (cost=317,173.12..2,798,620.92 rows=1,061,630 width=228) (actual time=5,741.318..78,821.148 rows=10,119,520 loops=1)

4. 4,759.276 74,792.718 ↓ 10.0 10,061,080 1

Result (cost=317,173.12..2,796,457.93 rows=1,006,112 width=228) (actual time=5,741.318..74,792.718 rows=10,061,080 loops=1)

5. 3,752.123 70,033.442 ↓ 10.0 10,061,080 1

Append (cost=317,173.12..2,783,881.53 rows=1,006,112 width=204) (actual time=5,741.308..70,033.442 rows=10,061,080 loops=1)

6. 555.846 8,312.403 ↓ 10.0 1,257,635 1

Subquery Scan on *SELECT* 1 (cost=317,173.12..347,985.19 rows=125,764 width=97) (actual time=5,741.307..8,312.403 rows=1,257,635 loops=1)

7. 935.444 7,756.557 ↓ 10.0 1,257,635 1

GroupAggregate (cost=317,173.12..346,727.55 rows=125,764 width=97) (actual time=5,741.306..7,756.557 rows=1,257,635 loops=1)

  • Group Key: ga_dim.report_date, ga_dim.platform, ga_dim.os, ga_dim.device, ga_dim.channel_grouping, ga_dim.source_medium, 'users'::text
8. 6,326.834 6,821.113 ↑ 1.0 1,257,635 1

Sort (cost=317,173.12..320,317.21 rows=1,257,635 width=93) (actual time=5,741.296..6,821.113 rows=1,257,635 loops=1)

  • Sort Key: ga_dim.report_date, ga_dim.platform, ga_dim.os, ga_dim.device, ga_dim.channel_grouping, ga_dim.source_medium
  • Sort Method: external merge Disk: 89240kB
9. 494.279 494.279 ↑ 1.0 1,257,635 1

Seq Scan on ga_dim (cost=0.00..60,799.35 rows=1,257,635 width=93) (actual time=0.019..494.279 rows=1,257,635 loops=1)

10. 543.329 8,235.131 ↓ 10.0 1,257,635 1

Subquery Scan on *SELECT* 2 (cost=317,173.12..347,985.19 rows=125,764 width=97) (actual time=5,696.984..8,235.131 rows=1,257,635 loops=1)

11. 922.567 7,691.802 ↓ 10.0 1,257,635 1

GroupAggregate (cost=317,173.12..346,727.55 rows=125,764 width=97) (actual time=5,696.983..7,691.802 rows=1,257,635 loops=1)

  • Group Key: ga_dim_1.report_date, ga_dim_1.platform, ga_dim_1.os, ga_dim_1.device, ga_dim_1.channel_grouping, ga_dim_1.source_medium, 'livecam_pis'::text
12. 6,293.886 6,769.235 ↑ 1.0 1,257,635 1

Sort (cost=317,173.12..320,317.21 rows=1,257,635 width=93) (actual time=5,696.972..6,769.235 rows=1,257,635 loops=1)

  • Sort Key: ga_dim_1.report_date, ga_dim_1.platform, ga_dim_1.os, ga_dim_1.device, ga_dim_1.channel_grouping, ga_dim_1.source_medium
  • Sort Method: external merge Disk: 92016kB
13. 475.349 475.349 ↑ 1.0 1,257,635 1

Seq Scan on ga_dim ga_dim_1 (cost=0.00..60,799.35 rows=1,257,635 width=93) (actual time=0.012..475.349 rows=1,257,635 loops=1)

14. 546.613 8,243.217 ↓ 10.0 1,257,635 1

Subquery Scan on *SELECT* 3 (cost=317,173.12..347,985.19 rows=125,764 width=97) (actual time=5,695.361..8,243.217 rows=1,257,635 loops=1)

15. 926.401 7,696.604 ↓ 10.0 1,257,635 1

GroupAggregate (cost=317,173.12..346,727.55 rows=125,764 width=97) (actual time=5,695.359..7,696.604 rows=1,257,635 loops=1)

  • Group Key: ga_dim_2.report_date, ga_dim_2.platform, ga_dim_2.os, ga_dim_2.device, ga_dim_2.channel_grouping, ga_dim_2.source_medium, 'video_pis'::text
16. 6,291.303 6,770.203 ↑ 1.0 1,257,635 1

Sort (cost=317,173.12..320,317.21 rows=1,257,635 width=93) (actual time=5,695.348..6,770.203 rows=1,257,635 loops=1)

  • Sort Key: ga_dim_2.report_date, ga_dim_2.platform, ga_dim_2.os, ga_dim_2.device, ga_dim_2.channel_grouping, ga_dim_2.source_medium
  • Sort Method: external merge Disk: 89664kB
17. 478.900 478.900 ↑ 1.0 1,257,635 1

Seq Scan on ga_dim ga_dim_2 (cost=0.00..60,799.35 rows=1,257,635 width=93) (actual time=0.012..478.900 rows=1,257,635 loops=1)

18. 547.417 8,272.301 ↓ 10.0 1,257,635 1

Subquery Scan on *SELECT* 4 (cost=317,173.12..347,985.19 rows=125,764 width=97) (actual time=5,731.306..8,272.301 rows=1,257,635 loops=1)

19. 919.472 7,724.884 ↓ 10.0 1,257,635 1

GroupAggregate (cost=317,173.12..346,727.55 rows=125,764 width=97) (actual time=5,731.303..7,724.884 rows=1,257,635 loops=1)

  • Group Key: ga_dim_3.report_date, ga_dim_3.platform, ga_dim_3.os, ga_dim_3.device, ga_dim_3.channel_grouping, ga_dim_3.source_medium, 'livecam_views'::text
20. 6,321.040 6,805.412 ↑ 1.0 1,257,635 1

Sort (cost=317,173.12..320,317.21 rows=1,257,635 width=93) (actual time=5,731.294..6,805.412 rows=1,257,635 loops=1)

  • Sort Key: ga_dim_3.report_date, ga_dim_3.platform, ga_dim_3.os, ga_dim_3.device, ga_dim_3.channel_grouping, ga_dim_3.source_medium
  • Sort Method: external merge Disk: 94112kB
21. 484.372 484.372 ↑ 1.0 1,257,635 1

Seq Scan on ga_dim ga_dim_3 (cost=0.00..60,799.35 rows=1,257,635 width=93) (actual time=0.011..484.372 rows=1,257,635 loops=1)

22. 545.391 8,240.601 ↓ 10.0 1,257,635 1

Subquery Scan on *SELECT* 5 (cost=317,173.12..347,985.19 rows=125,764 width=97) (actual time=5,703.675..8,240.601 rows=1,257,635 loops=1)

23. 922.014 7,695.210 ↓ 10.0 1,257,635 1

GroupAggregate (cost=317,173.12..346,727.55 rows=125,764 width=97) (actual time=5,703.674..7,695.210 rows=1,257,635 loops=1)

  • Group Key: ga_dim_4.report_date, ga_dim_4.platform, ga_dim_4.os, ga_dim_4.device, ga_dim_4.channel_grouping, ga_dim_4.source_medium, 'video_views'::text
24. 6,286.778 6,773.196 ↑ 1.0 1,257,635 1

Sort (cost=317,173.12..320,317.21 rows=1,257,635 width=93) (actual time=5,703.664..6,773.196 rows=1,257,635 loops=1)

  • Sort Key: ga_dim_4.report_date, ga_dim_4.platform, ga_dim_4.os, ga_dim_4.device, ga_dim_4.channel_grouping, ga_dim_4.source_medium
  • Sort Method: external merge Disk: 91752kB
25. 486.418 486.418 ↑ 1.0 1,257,635 1

Seq Scan on ga_dim ga_dim_4 (cost=0.00..60,799.35 rows=1,257,635 width=93) (actual time=0.013..486.418 rows=1,257,635 loops=1)

26. 544.322 8,279.386 ↓ 10.0 1,257,635 1

Subquery Scan on *SELECT* 6 (cost=317,173.12..347,985.19 rows=125,764 width=97) (actual time=5,721.964..8,279.386 rows=1,257,635 loops=1)

27. 939.728 7,735.064 ↓ 10.0 1,257,635 1

GroupAggregate (cost=317,173.12..346,727.55 rows=125,764 width=97) (actual time=5,721.962..7,735.064 rows=1,257,635 loops=1)

  • Group Key: ga_dim_5.report_date, ga_dim_5.platform, ga_dim_5.os, ga_dim_5.device, ga_dim_5.channel_grouping, ga_dim_5.source_medium, 'total_pis'::text
28. 6,302.290 6,795.336 ↑ 1.0 1,257,635 1

Sort (cost=317,173.12..320,317.21 rows=1,257,635 width=93) (actual time=5,721.953..6,795.336 rows=1,257,635 loops=1)

  • Sort Key: ga_dim_5.report_date, ga_dim_5.platform, ga_dim_5.os, ga_dim_5.device, ga_dim_5.channel_grouping, ga_dim_5.source_medium
  • Sort Method: external merge Disk: 94128kB
29. 493.046 493.046 ↑ 1.0 1,257,635 1

Seq Scan on ga_dim ga_dim_5 (cost=0.00..60,799.35 rows=1,257,635 width=93) (actual time=0.017..493.046 rows=1,257,635 loops=1)

30. 547.025 8,343.439 ↓ 10.0 1,257,635 1

Subquery Scan on *SELECT* 7 (cost=317,173.12..347,985.19 rows=125,764 width=97) (actual time=5,785.716..8,343.439 rows=1,257,635 loops=1)

31. 933.646 7,796.414 ↓ 10.0 1,257,635 1

GroupAggregate (cost=317,173.12..346,727.55 rows=125,764 width=97) (actual time=5,785.715..7,796.414 rows=1,257,635 loops=1)

  • Group Key: ga_dim_6.report_date, ga_dim_6.platform, ga_dim_6.os, ga_dim_6.device, ga_dim_6.channel_grouping, ga_dim_6.source_medium, 'total_sessions'::text
32. 6,364.518 6,862.768 ↑ 1.0 1,257,635 1

Sort (cost=317,173.12..320,317.21 rows=1,257,635 width=93) (actual time=5,785.705..6,862.768 rows=1,257,635 loops=1)

  • Sort Key: ga_dim_6.report_date, ga_dim_6.platform, ga_dim_6.os, ga_dim_6.device, ga_dim_6.channel_grouping, ga_dim_6.source_medium
  • Sort Method: external merge Disk: 100248kB
33. 498.250 498.250 ↑ 1.0 1,257,635 1

Seq Scan on ga_dim ga_dim_6 (cost=0.00..60,799.35 rows=1,257,635 width=93) (actual time=0.013..498.250 rows=1,257,635 loops=1)

34. 542.849 8,354.841 ↓ 10.0 1,257,635 1

Subquery Scan on *SELECT* 8 (cost=317,173.12..347,985.19 rows=125,764 width=97) (actual time=5,780.761..8,354.841 rows=1,257,635 loops=1)

35. 935.331 7,811.992 ↓ 10.0 1,257,635 1

GroupAggregate (cost=317,173.12..346,727.55 rows=125,764 width=97) (actual time=5,780.760..7,811.992 rows=1,257,635 loops=1)

  • Group Key: ga_dim_7.report_date, ga_dim_7.platform, ga_dim_7.os, ga_dim_7.device, ga_dim_7.channel_grouping, ga_dim_7.source_medium, 'downloads'::text
36. 6,368.594 6,876.661 ↑ 1.0 1,257,635 1

Sort (cost=317,173.12..320,317.21 rows=1,257,635 width=93) (actual time=5,780.750..6,876.661 rows=1,257,635 loops=1)

  • Sort Key: ga_dim_7.report_date, ga_dim_7.platform, ga_dim_7.os, ga_dim_7.device, ga_dim_7.channel_grouping, ga_dim_7.source_medium
  • Sort Method: external merge Disk: 88672kB
37. 508.067 508.067 ↑ 1.0 1,257,635 1

Seq Scan on ga_dim ga_dim_7 (cost=0.00..60,799.35 rows=1,257,635 width=93) (actual time=0.012..508.067 rows=1,257,635 loops=1)

38. 2.267 2.267 ↑ 1.0 5,844 1

Seq Scan on daily_report (cost=0.00..145.44 rows=5,844 width=174) (actual time=0.028..2.267 rows=5,844 loops=1)

39. 2.174 2.174 ↑ 1.0 5,844 1

Seq Scan on daily_report daily_report_1 (cost=0.00..145.44 rows=5,844 width=174) (actual time=0.005..2.174 rows=5,844 loops=1)

40. 2.460 10.293 ↓ 2.0 5,844 1

Subquery Scan on *SELECT* 11 (cost=262.32..328.07 rows=2,922 width=200) (actual time=5.493..10.293 rows=5,844 loops=1)

41. 5.733 7.833 ↓ 2.0 5,844 1

HashAggregate (cost=262.32..298.85 rows=2,922 width=200) (actual time=5.490..7.833 rows=5,844 loops=1)

  • Group Key: daily_report_2.report_date, daily_report_2.platform, NULL::text, NULL::text, NULL::text, NULL::text, 'ivw_page_impressions'::text
42. 2.100 2.100 ↑ 1.0 5,844 1

Seq Scan on daily_report daily_report_2 (cost=0.00..145.44 rows=5,844 width=175) (actual time=0.005..2.100 rows=5,844 loops=1)

43. 2.251 2.251 ↑ 1.0 5,844 1

Seq Scan on daily_report daily_report_3 (cost=0.00..145.44 rows=5,844 width=175) (actual time=0.008..2.251 rows=5,844 loops=1)

44. 2.247 2.247 ↑ 1.0 5,844 1

Seq Scan on daily_report daily_report_4 (cost=0.00..145.44 rows=5,844 width=174) (actual time=0.010..2.247 rows=5,844 loops=1)

45. 2.254 2.254 ↑ 1.0 5,844 1

Seq Scan on daily_report daily_report_5 (cost=0.00..145.44 rows=5,844 width=174) (actual time=0.006..2.254 rows=5,844 loops=1)

46. 2.320 2.320 ↑ 1.0 5,844 1

Seq Scan on daily_report daily_report_6 (cost=0.00..145.44 rows=5,844 width=173) (actual time=0.007..2.320 rows=5,844 loops=1)

47. 2.283 2.283 ↑ 1.0 5,844 1

Seq Scan on daily_report daily_report_7 (cost=0.00..145.44 rows=5,844 width=173) (actual time=0.005..2.283 rows=5,844 loops=1)

48. 2.312 2.312 ↑ 1.0 5,844 1

Seq Scan on daily_report daily_report_8 (cost=0.00..145.44 rows=5,844 width=174) (actual time=0.009..2.312 rows=5,844 loops=1)

49. 2.310 2.310 ↑ 1.0 5,844 1

Seq Scan on daily_report daily_report_9 (cost=0.00..145.44 rows=5,844 width=175) (actual time=0.006..2.310 rows=5,844 loops=1)

50.          

CTE metric_calc

51. 4,623.131 430,511.642 ↓ 34.7 3,686,293 1

WindowAgg (cost=1,203,417.21..1,208,194.54 rows=106,163 width=340) (actual time=416,151.017..430,511.642 rows=3,686,293 loops=1)

52. 27,052.129 425,888.511 ↓ 34.7 3,686,293 1

Sort (cost=1,203,417.21..1,203,682.61 rows=106,163 width=308) (actual time=416,150.999..425,888.511 rows=3,686,293 loops=1)

  • Sort Key: (date_trunc('YEAR'::text, (b.report_date)::timestamp with time zone)), b.platform, b.os, b.device, b.channel_grouping, b.source_medium, b.key, b.report_date
  • Sort Method: external merge Disk: 396000kB
53. 4,698.755 398,836.382 ↓ 34.7 3,686,293 1

WindowAgg (cost=1,174,534.93..1,179,312.26 rows=106,163 width=308) (actual time=389,242.055..398,836.382 rows=3,686,293 loops=1)

54. 38,953.244 394,137.627 ↓ 34.7 3,686,293 1

Sort (cost=1,174,534.93..1,174,800.33 rows=106,163 width=276) (actual time=389,242.037..394,137.627 rows=3,686,293 loops=1)

  • Sort Key: (date_trunc('MONTH'::text, (b.report_date)::timestamp with time zone)), b.platform, b.os, b.device, b.channel_grouping, b.source_medium, b.key, b.report_date
  • Sort Method: external merge Disk: 377672kB
55. 5,764.302 355,184.383 ↓ 34.7 3,686,293 1

GroupAggregate (cost=1,123,085.76..1,151,882.48 rows=106,163 width=276) (actual time=340,308.878..355,184.383 rows=3,686,293 loops=1)

  • Group Key: b.report_date, b.platform, b.os, b.device, b.channel_grouping, b.source_medium, b.key, b.value
56. 38,377.181 349,420.081 ↓ 4.3 4,553,732 1

Sort (cost=1,123,085.76..1,125,726.57 rows=1,056,322 width=260) (actual time=340,308.846..349,420.081 rows=4,553,732 loops=1)

  • Sort Key: b.report_date, b.platform, b.os, b.device, b.channel_grouping, b.source_medium, b.key, b.value
  • Sort Method: external merge Disk: 378008kB
57. 34,658.218 311,042.900 ↓ 4.3 4,553,732 1

Merge Left Join (cost=717,720.23..757,438.48 rows=1,056,322 width=260) (actual time=253,202.487..311,042.900 rows=4,553,732 loops=1)

  • Merge Cond: ((b.platform = b2.platform) AND (b.os = b2.os) AND (b.device = b2.device) AND (b.channel_grouping = b2.channel_grouping) AND (b.source_medium = b2.source_medium) AND (b.key = b2.key))
  • Join Filter: ((b2.report_date <= b.report_date) AND (b2.report_date >= (b.report_date - '6 days'::interval)))
  • Rows Removed by Join Filter: 55494019
58. 45,052.271 131,999.014 ↓ 3.5 3,686,293 1

Sort (cost=357,997.88..360,638.69 rows=1,056,322 width=228) (actual time=120,824.550..131,999.014 rows=3,686,293 loops=1)

  • Sort Key: b.platform, b.os, b.device, b.channel_grouping, b.source_medium, b.key
  • Sort Method: external merge Disk: 278880kB
59. 86,946.743 86,946.743 ↓ 3.5 3,686,293 1

CTE Scan on base b (cost=0.00..21,232.60 rows=1,056,322 width=228) (actual time=5,769.002..86,946.743 rows=3,686,293 loops=1)

  • Filter: (value IS NOT NULL)
  • Rows Removed by Filter: 6433227
60. 11,331.721 144,385.668 ↓ 53.3 56,533,984 1

Materialize (cost=359,722.35..365,030.50 rows=1,061,630 width=228) (actual time=132,377.886..144,385.668 rows=56,533,984 loops=1)

61. 129,343.899 133,053.947 ↑ 4.8 221,817 1

Sort (cost=359,722.35..362,376.42 rows=1,061,630 width=228) (actual time=132,377.881..133,053.947 rows=221,817 loops=1)

  • Sort Key: b2.platform, b2.os, b2.device, b2.channel_grouping, b2.source_medium, b2.key
  • Sort Method: external merge Disk: 751016kB
62. 3,710.048 3,710.048 ↓ 9.5 10,119,520 1

CTE Scan on base b2 (cost=0.00..21,232.60 rows=1,061,630 width=228) (actual time=0.040..3,710.048 rows=10,119,520 loops=1)

Planning time : 1.644 ms
Execution time : 435,723.475 ms