explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LcO2

Settings
# exclusive inclusive rows x rows loops node
1. 2.118 171,847.018 ↑ 24.8 50 1

Subquery Scan on results (cost=105,350.39..105,536.49 rows=1,241 width=272) (actual time=171,798.077..171,847.018 rows=50 loops=1)

  • Filter: (results.row_number <= 50)
  • Rows Removed by Filter: 3353
2. 44.862 171,844.900 ↑ 1.1 3,403 1

WindowAgg (cost=105,350.39..105,489.97 rows=3,722 width=313) (actual time=171,798.071..171,844.900 rows=3,403 loops=1)

3. 6.288 171,800.038 ↑ 1.1 3,403 1

Sort (cost=105,350.39..105,359.70 rows=3,722 width=353) (actual time=171,798.024..171,800.038 rows=3,403 loops=1)

  • Sort Key: (count(f_survey_views.visitor_key)) DESC
  • Sort Method: quicksort Memory: 669kB
4. 198.899 171,793.750 ↑ 1.1 3,403 1

GroupAggregate (cost=104,450.38..105,129.64 rows=3,722 width=353) (actual time=171,556.898..171,793.750 rows=3,403 loops=1)

  • Group Key: locations.location_continent_name, locations.location_country_name, locations.location_subdivision_1_name, locations.location_subdivision_2_name, locations.location_city_name
5. 468.107 171,594.851 ↓ 8.3 30,837 1

Sort (cost=104,450.38..104,459.68 rows=3,722 width=118) (actual time=171,556.838..171,594.851 rows=30,837 loops=1)

  • Sort Key: locations.location_continent_name, locations.location_country_name, locations.location_subdivision_1_name, locations.location_subdivision_2_name, locations.location_city_name
  • Sort Method: quicksort Memory: 5120kB
6. 313.911 171,126.744 ↓ 8.3 30,837 1

Nested Loop (cost=78,746.66..104,229.63 rows=3,722 width=118) (actual time=3,103.071..171,126.744 rows=30,837 loops=1)

7. 53.898 3,645.456 ↓ 8.3 30,837 1

Hash Left Join (cost=78,746.23..78,834.81 rows=3,722 width=85) (actual time=3,103.004..3,645.456 rows=30,837 loops=1)

  • Hash Cond: ((first_value(f_survey_views.sentiment_follow_up_response_key) OVER (?)) = sent_fups.sentiment_follow_up_response_key)
8. 90.845 3,591.535 ↓ 8.3 30,837 1

Hash Left Join (cost=78,744.89..78,816.73 rows=3,722 width=53) (actual time=3,102.971..3,591.535 rows=30,837 loops=1)

  • Hash Cond: ((first_value(f_survey_views.sentiment_key) OVER (?)) = sents.sentiment_key)
9. 298.872 3,500.645 ↓ 8.3 30,837 1

Unique (cost=78,743.64..78,762.25 rows=3,722 width=174) (actual time=3,102.906..3,500.645 rows=30,837 loops=1)

10. 161.634 3,201.773 ↓ 11.4 42,601 1

Sort (cost=78,743.64..78,752.95 rows=3,722 width=174) (actual time=3,102.905..3,201.773 rows=42,601 loops=1)

  • Sort Key: (ROW(f_survey_views.survey_key, f_survey_views.visitor_key))
  • Sort Method: quicksort Memory: 7527kB
11. 115.899 3,040.139 ↓ 11.4 42,601 1

WindowAgg (cost=78,439.15..78,522.89 rows=3,722 width=174) (actual time=2,898.906..3,040.139 rows=42,601 loops=1)

12. 82.076 2,924.240 ↓ 11.4 42,601 1

Sort (cost=78,439.15..78,448.45 rows=3,722 width=114) (actual time=2,898.889..2,924.240 rows=42,601 loops=1)

  • Sort Key: f_survey_views.survey_key, f_survey_views.visitor_key, f_survey_views.scroll_depth DESC
  • Sort Method: quicksort Memory: 7530kB
13. 154.877 2,842.164 ↓ 11.4 42,601 1

WindowAgg (cost=78,134.66..78,218.40 rows=3,722 width=114) (actual time=2,659.832..2,842.164 rows=42,601 loops=1)

14. 152.562 2,687.287 ↓ 11.4 42,601 1

Sort (cost=78,134.66..78,143.96 rows=3,722 width=113) (actual time=2,659.821..2,687.287 rows=42,601 loops=1)

  • Sort Key: f_survey_views.survey_key, f_survey_views.visitor_key, f_survey_views.watched_full_video DESC
  • Sort Method: quicksort Memory: 7530kB
15. 228.724 2,534.725 ↓ 11.4 42,601 1

WindowAgg (cost=77,830.16..77,913.91 rows=3,722 width=113) (actual time=2,270.192..2,534.725 rows=42,601 loops=1)

16. 85.593 2,306.001 ↓ 11.4 42,601 1

Sort (cost=77,830.16..77,839.47 rows=3,722 width=109) (actual time=2,270.180..2,306.001 rows=42,601 loops=1)

  • Sort Key: f_survey_views.survey_key, f_survey_views.visitor_key, f_survey_views.video_seconds_watched DESC NULLS LAST
  • Sort Method: quicksort Memory: 7530kB
17. 92.705 2,220.408 ↓ 11.4 42,601 1

WindowAgg (cost=77,525.67..77,609.41 rows=3,722 width=109) (actual time=2,106.402..2,220.408 rows=42,601 loops=1)

18. 86.424 2,127.703 ↓ 11.4 42,601 1

Sort (cost=77,525.67..77,534.97 rows=3,722 width=108) (actual time=2,106.390..2,127.703 rows=42,601 loops=1)

  • Sort Key: f_survey_views.survey_key, f_survey_views.visitor_key, f_survey_views.rbt_clicked DESC
  • Sort Method: quicksort Memory: 7530kB
19. 130.698 2,041.279 ↓ 11.4 42,601 1

WindowAgg (cost=77,221.17..77,304.92 rows=3,722 width=108) (actual time=1,890.576..2,041.279 rows=42,601 loops=1)

20. 61.789 1,910.581 ↓ 11.4 42,601 1

Sort (cost=77,221.17..77,230.48 rows=3,722 width=107) (actual time=1,890.567..1,910.581 rows=42,601 loops=1)

  • Sort Key: f_survey_views.survey_key, f_survey_views.visitor_key, f_survey_views.interacted_with_survey DESC
  • Sort Method: quicksort Memory: 7530kB
21. 93.950 1,848.792 ↓ 11.4 42,601 1

WindowAgg (cost=76,888.76..77,000.42 rows=3,722 width=107) (actual time=1,733.407..1,848.792 rows=42,601 loops=1)

22. 136.669 1,754.842 ↓ 11.4 42,601 1

Sort (cost=76,888.76..76,898.07 rows=3,722 width=101) (actual time=1,733.391..1,754.842 rows=42,601 loops=1)

  • Sort Key: f_survey_views.survey_key, f_survey_views.visitor_key, f_survey_views.sentiment_key DESC NULLS LAST, f_survey_views.utc_date_key, f_survey_views.utc_time_key
  • Sort Method: quicksort Memory: 7530kB
23. 188.572 1,618.173 ↓ 11.4 42,601 1

WindowAgg (cost=76,556.35..76,668.01 rows=3,722 width=101) (actual time=1,390.447..1,618.173 rows=42,601 loops=1)

24. 235.891 1,429.601 ↓ 11.4 42,601 1

Sort (cost=76,556.35..76,565.66 rows=3,722 width=77) (actual time=1,390.423..1,429.601 rows=42,601 loops=1)

  • Sort Key: f_survey_views.survey_key, f_survey_views.visitor_key, f_survey_views.utc_date_key, f_survey_views.utc_time_key
  • Sort Method: quicksort Memory: 7527kB
25. 137.748 1,193.710 ↓ 11.4 42,601 1

Hash Join (cost=2,953.18..76,335.61 rows=3,722 width=77) (actual time=244.389..1,193.710 rows=42,601 loops=1)

  • Hash Cond: (f_survey_views.utc_time_key = d_times.time_key)
  • Join Filter: ((d_dates.full_date + d_times.full_time) <= '2019-11-08 07:59:59'::timestamp without time zone)
26. 89.297 831.610 ↓ 3.8 42,601 1

Hash Join (cost=373.18..73,546.26 rows=11,165 width=81) (actual time=19.423..831.610 rows=42,601 loops=1)

  • Hash Cond: (f_survey_views.utc_date_key = d_dates.date_key)
27. 92.287 723.092 ↓ 3.8 42,601 1

Nested Loop (cost=0.85..73,020.41 rows=11,165 width=77) (actual time=0.142..723.092 rows=42,601 loops=1)

28. 0.059 0.059 ↑ 1.0 1 1

Index Scan using index_d_surveys_on_survey_id on d_surveys (cost=0.28..8.30 rows=1 width=4) (actual time=0.056..0.059 rows=1 loops=1)

  • Index Cond: ((survey_id)::text = '5b64ccf4dafc2cfe6b5ad043'::text)
29. 630.746 630.746 ↓ 2.1 42,601 1

Index Scan using index_f_survey_views_on_survey_key on f_survey_views (cost=0.57..72,811.69 rows=20,042 width=77) (actual time=0.081..630.746 rows=42,601 loops=1)

  • Index Cond: (survey_key = d_surveys.survey_key)
30. 3.202 19.221 ↑ 1.5 5,480 1

Hash (cost=270.48..270.48 rows=8,148 width=8) (actual time=19.220..19.221 rows=5,480 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 279kB
31. 16.019 16.019 ↑ 1.5 5,480 1

Seq Scan on d_dates (cost=0.00..270.48 rows=8,148 width=8) (actual time=0.017..16.019 rows=5,480 loops=1)

32. 104.096 224.352 ↑ 1.0 86,400 1

Hash (cost=1,500.00..1,500.00 rows=86,400 width=12) (actual time=224.352..224.352 rows=86,400 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 5074kB
33. 120.256 120.256 ↑ 1.0 86,400 1

Seq Scan on d_times (cost=0.00..1,500.00 rows=86,400 width=12) (actual time=0.012..120.256 rows=86,400 loops=1)

34. 0.009 0.045 ↑ 1.0 11 1

Hash (cost=1.11..1.11 rows=11 width=8) (actual time=0.045..0.045 rows=11 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
35. 0.036 0.036 ↑ 1.0 11 1

Seq Scan on d_sentiments sents (cost=0.00..1.11 rows=11 width=8) (actual time=0.029..0.036 rows=11 loops=1)

36. 0.004 0.023 ↑ 1.0 15 1

Hash (cost=1.15..1.15 rows=15 width=36) (actual time=0.023..0.023 rows=15 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
37. 0.019 0.019 ↑ 1.0 15 1

Seq Scan on d_sentiment_follow_up_responses sent_fups (cost=0.00..1.15 rows=15 width=36) (actual time=0.006..0.019 rows=15 loops=1)

38. 167,167.377 167,167.377 ↑ 1.0 1 30,837

Index Scan using d_locations_pkey on d_locations locations (cost=0.43..6.81 rows=1 width=49) (actual time=0.197..5.421 rows=1 loops=30,837)

  • Index Cond: (location_key = (first_value(f_survey_views.location_key) OVER (?)))
Planning time : 4.153 ms
Execution time : 171,862.395 ms