explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kkTv

Settings
# exclusive inclusive rows x rows loops node
1. 0.018 102.081 ↑ 47.4 5 1

Hash Left Join (cost=973,625.14..973,660.51 rows=237 width=168) (actual time=102.057..102.081 rows=5 loops=1)

  • Hash Cond: ((users_count.source_type)::text = (doorways_expenses.source_type)::text)
2.          

CTE found_users

3. 3.643 35.566 ↓ 1.1 3,805 1

HashAggregate (cost=13,432.09..13,477.27 rows=3,615 width=64) (actual time=33.894..35.566 rows=3,805 loops=1)

  • Group Key: stats_install_sources.source_type, stats_install_sources.url_parameter, users.id, stats_install_sources.app_id
4. 2.327 31.923 ↓ 1.1 3,805 1

Hash Left Join (cost=229.82..13,395.94 rows=3,615 width=32) (actual time=10.097..31.923 rows=3,805 loops=1)

  • Hash Cond: ((users.install_source)::text = (stats_install_sources.url_parameter)::text)
5. 21.690 29.422 ↓ 1.1 3,805 1

Bitmap Heap Scan on users (cost=221.49..13,337.90 rows=3,615 width=20) (actual time=9.904..29.422 rows=3,805 loops=1)

  • Recheck Cond: ((created_at >= '2019-04-07 21:00:00'::timestamp without time zone) AND (created_at <= '2019-04-08 20:59:59'::timestamp without time zone))
  • Heap Blocks: exact=13203
6. 7.732 7.732 ↓ 6.1 22,086 1

Bitmap Index Scan on index_users_on_created_at (cost=0.00..220.58 rows=3,615 width=0) (actual time=7.732..7.732 rows=22,086 loops=1)

  • Index Cond: ((created_at >= '2019-04-07 21:00:00'::timestamp without time zone) AND (created_at <= '2019-04-08 20:59:59'::timestamp without time zone))
7. 0.098 0.174 ↑ 1.0 237 1

Hash (cost=5.37..5.37 rows=237 width=20) (actual time=0.173..0.174 rows=237 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
8. 0.076 0.076 ↑ 1.0 237 1

Seq Scan on stats_install_sources (cost=0.00..5.37 rows=237 width=20) (actual time=0.007..0.076 rows=237 loops=1)

9.          

CTE users_count

10. 1.357 39.733 ↑ 40.0 5 1

HashAggregate (cost=90.37..92.37 rows=200 width=40) (actual time=39.728..39.733 rows=5 loops=1)

  • Group Key: found_users.source_type
11. 38.376 38.376 ↓ 1.1 3,805 1

CTE Scan on found_users (cost=0.00..72.30 rows=3,615 width=36) (actual time=33.897..38.376 rows=3,805 loops=1)

12.          

CTE doorways_expenses

13. 1.204 4.666 ↑ 118.5 2 1

HashAggregate (cost=4,529.02..4,531.98 rows=237 width=42) (actual time=4.666..4.666 rows=2 loops=1)

  • Group Key: stats_install_sources_1.url_parameter
14. 1.470 3.462 ↓ 2.0 3,052 1

Hash Left Join (cost=8.76..4,521.44 rows=1,516 width=16) (actual time=0.140..3.462 rows=3,052 loops=1)

  • Hash Cond: (offer_hourly_statistics.app_id = stats_install_sources_1.app_id)
15. 1.870 1.870 ↓ 2.0 3,052 1

Index Scan using index_offer_hourly_statistics_on_start_time_and_offer_id on offer_hourly_statistics (cost=0.43..4,433.47 rows=1,516 width=10) (actual time=0.012..1.870 rows=3,052 loops=1)

  • Index Cond: ((start_time >= '2019-04-07 21:00:00'::timestamp without time zone) AND (start_time <= '2019-04-08 20:59:59'::timestamp without time zone))
  • Filter: (app_id IS NOT NULL)
16. 0.045 0.122 ↑ 118.5 2 1

Hash (cost=5.37..5.37 rows=237 width=14) (actual time=0.122..0.122 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
17. 0.077 0.077 ↑ 1.0 237 1

Seq Scan on stats_install_sources stats_install_sources_1 (cost=0.00..5.37 rows=237 width=14) (actual time=0.009..0.077 rows=237 loops=1)

18.          

CTE users_3_days

19. 2.288 19.413 ↑ 40.0 5 1

GroupAggregate (cost=318,273.74..318,498.77 rows=200 width=64) (actual time=15.899..19.413 rows=5 loops=1)

  • Group Key: found_users_1.source_type
20. 3.401 17.125 ↑ 4.3 5,131 1

Sort (cost=318,273.74..318,329.37 rows=22,253 width=43) (actual time=15.827..17.125 rows=5,131 loops=1)

  • Sort Key: found_users_1.source_type
  • Sort Method: quicksort Memory: 506kB
21. 4.844 13.724 ↑ 4.3 5,131 1

Nested Loop Left Join (cost=0.43..316,666.88 rows=22,253 width=43) (actual time=0.018..13.724 rows=5,131 loops=1)

22. 1.270 1.270 ↓ 1.1 3,805 1

CTE Scan on found_users found_users_1 (cost=0.00..72.30 rows=3,615 width=44) (actual time=0.001..1.270 rows=3,805 loops=1)

23. 7.610 7.610 ↑ 6.0 1 3,805

Index Scan using index_user_hourly_statistics_on_user_id on user_hourly_statistics three_days (cost=0.43..87.52 rows=6 width=23) (actual time=0.002..0.002 rows=1 loops=3,805)

  • Index Cond: (user_id = found_users_1.id)
  • Filter: ((start_time >= date_trunc('hour'::text, found_users_1.created_at)) AND (start_time <= (found_users_1.created_at + '3 days'::interval)))
24.          

CTE users_7_days

25. 2.272 19.040 ↑ 40.0 5 1

GroupAggregate (cost=318,273.74..318,498.77 rows=200 width=64) (actual time=15.653..19.040 rows=5 loops=1)

  • Group Key: found_users_2.source_type
26. 3.405 16.768 ↑ 4.3 5,131 1

Sort (cost=318,273.74..318,329.37 rows=22,253 width=43) (actual time=15.542..16.768 rows=5,131 loops=1)

  • Sort Key: found_users_2.source_type
  • Sort Method: quicksort Memory: 506kB
27. 4.399 13.363 ↑ 4.3 5,131 1

Nested Loop Left Join (cost=0.43..316,666.88 rows=22,253 width=43) (actual time=0.009..13.363 rows=5,131 loops=1)

28. 1.354 1.354 ↓ 1.1 3,805 1

CTE Scan on found_users found_users_2 (cost=0.00..72.30 rows=3,615 width=44) (actual time=0.001..1.354 rows=3,805 loops=1)

29. 7.610 7.610 ↑ 6.0 1 3,805

Index Scan using index_user_hourly_statistics_on_user_id on user_hourly_statistics seven_days (cost=0.43..87.52 rows=6 width=23) (actual time=0.002..0.002 rows=1 loops=3,805)

  • Index Cond: (user_id = found_users_2.id)
  • Filter: ((start_time >= date_trunc('hour'::text, found_users_2.created_at)) AND (start_time <= (found_users_2.created_at + '7 days'::interval)))
30.          

CTE users_30_days

31. 2.280 19.099 ↑ 40.0 5 1

GroupAggregate (cost=318,273.74..318,498.77 rows=200 width=64) (actual time=15.644..19.099 rows=5 loops=1)

  • Group Key: found_users_3.source_type
32. 3.238 16.819 ↑ 4.3 5,131 1

Sort (cost=318,273.74..318,329.37 rows=22,253 width=43) (actual time=15.577..16.819 rows=5,131 loops=1)

  • Sort Key: found_users_3.source_type
  • Sort Method: quicksort Memory: 506kB
33. 4.563 13.581 ↑ 4.3 5,131 1

Nested Loop Left Join (cost=0.43..316,666.88 rows=22,253 width=43) (actual time=0.008..13.581 rows=5,131 loops=1)

34. 1.408 1.408 ↓ 1.1 3,805 1

CTE Scan on found_users found_users_3 (cost=0.00..72.30 rows=3,615 width=44) (actual time=0.001..1.408 rows=3,805 loops=1)

35. 7.610 7.610 ↑ 6.0 1 3,805

Index Scan using index_user_hourly_statistics_on_user_id on user_hourly_statistics thirty_days (cost=0.43..87.52 rows=6 width=23) (actual time=0.002..0.002 rows=1 loops=3,805)

  • Index Cond: (user_id = found_users_3.id)
  • Filter: ((start_time >= date_trunc('hour'::text, found_users_3.created_at)) AND (start_time <= (found_users_3.created_at + '30 days'::interval)))
36. 0.013 97.387 ↑ 40.0 5 1

Hash Left Join (cost=19.50..46.00 rows=200 width=136) (actual time=97.367..97.387 rows=5 loops=1)

  • Hash Cond: ((users_count.source_type)::text = (users_30_days.source_type)::text)
37. 0.018 78.262 ↑ 40.0 5 1

Hash Left Join (cost=13.00..32.00 rows=200 width=104) (actual time=78.244..78.262 rows=5 loops=1)

  • Hash Cond: ((users_count.source_type)::text = (users_7_days.source_type)::text)
38. 0.024 59.190 ↑ 40.0 5 1

Hash Left Join (cost=6.50..18.00 rows=200 width=72) (actual time=59.177..59.190 rows=5 loops=1)

  • Hash Cond: ((users_count.source_type)::text = (users_3_days.source_type)::text)
39. 39.740 39.740 ↑ 40.0 5 1

CTE Scan on users_count (cost=0.00..4.00 rows=200 width=40) (actual time=39.731..39.740 rows=5 loops=1)

40. 0.006 19.426 ↑ 40.0 5 1

Hash (cost=4.00..4.00 rows=200 width=64) (actual time=19.426..19.426 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
41. 19.420 19.420 ↑ 40.0 5 1

CTE Scan on users_3_days (cost=0.00..4.00 rows=200 width=64) (actual time=15.901..19.420 rows=5 loops=1)

42. 0.008 19.054 ↑ 40.0 5 1

Hash (cost=4.00..4.00 rows=200 width=64) (actual time=19.054..19.054 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
43. 19.046 19.046 ↑ 40.0 5 1

CTE Scan on users_7_days (cost=0.00..4.00 rows=200 width=64) (actual time=15.656..19.046 rows=5 loops=1)

44. 0.004 19.112 ↑ 40.0 5 1

Hash (cost=4.00..4.00 rows=200 width=64) (actual time=19.111..19.112 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
45. 19.108 19.108 ↑ 40.0 5 1

CTE Scan on users_30_days (cost=0.00..4.00 rows=200 width=64) (actual time=15.646..19.108 rows=5 loops=1)

46. 0.004 4.676 ↑ 237.0 1 1

Hash (cost=4.74..4.74 rows=237 width=64) (actual time=4.676..4.676 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
47. 4.672 4.672 ↑ 118.5 2 1

CTE Scan on doorways_expenses (cost=0.00..4.74 rows=237 width=64) (actual time=4.669..4.672 rows=2 loops=1)

Planning time : 1.797 ms
Execution time : 102.480 ms