explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7YK7

Settings
# exclusive inclusive rows x rows loops node
1. 120.756 24,378.162 ↓ 61,849.0 61,849 1

WindowAgg (cost=44,299.44..44,299.66 rows=1 width=699) (actual time=24,325.229..24,378.162 rows=61,849 loops=1)

2. 421.152 24,257.406 ↓ 61,849.0 61,849 1

GroupAggregate (cost=44,299.44..44,299.64 rows=1 width=683) (actual time=23,816.240..24,257.406 rows=61,849 loops=1)

  • Group Key: daily_revenue_summary.revenue_date, daily_revenue_summary.brand_id, daily_revenue_summary.platform_id, daily_revenue_summary.server, daily_revenue_summary.country_id, daily_revenue_summary.currency_id, daily_revenue_summary.game_id, promotion_optins.before_period_from, promotion_optins.before_period_until, promotion_optins.promo_start_date, promotion_optins.promo_end_date, promotion_optins.after_period_from, promotion_optins.after_period_until, promotion_optins.promo_name, promotion_optins.am, brand_info.hub, brand_info.brand, brand_info.platform, brand_info.operator_group, promotion_optins.optins_status, (CASE WHEN (daily_revenue_summary.revenue_date < promotion_optins.promo_start_date) THEN 'Before Promo'::text WHEN (daily_revenue_summary.revenue_date > promotion_optins.promo_end_date) THEN 'After Promo'::text ELSE 'During Promo'::text END)
3. 824.876 23,836.254 ↓ 84,350.0 84,350 1

Sort (cost=44,299.44..44,299.45 rows=1 width=246) (actual time=23,816.165..23,836.254 rows=84,350 loops=1)

  • Sort Key: daily_revenue_summary.revenue_date, daily_revenue_summary.brand_id, daily_revenue_summary.platform_id, daily_revenue_summary.server, daily_revenue_summary.country_id, daily_revenue_summary.currency_id, daily_revenue_summary.game_id, promotion_optins.before_period_from, promotion_optins.before_period_until, promotion_optins.promo_start_date, promotion_optins.promo_end_date, promotion_optins.after_period_from, promotion_optins.after_period_until, promotion_optins.am, brand_info.brand, brand_info.platform, brand_info.operator_group, promotion_optins.optins_status, (CASE WHEN (daily_revenue_summary.revenue_date < promotion_optins.promo_start_date) THEN 'Before Promo'::text WHEN (daily_revenue_summary.revenue_date > promotion_optins.promo_end_date) THEN 'After Promo'::text ELSE 'During Promo'::text END)
  • Sort Method: external merge Disk: 19,176kB
4. 4,559.058 23,011.378 ↓ 84,350.0 84,350 1

Gather (cost=32,904.07..44,299.43 rows=1 width=246) (actual time=381.824..23,011.378 rows=84,350 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
5. 79.278 18,452.320 ↓ 21,088.0 21,088 4 / 4

Nested Loop (cost=31,904.07..43,299.33 rows=1 width=246) (actual time=372.801..18,452.320 rows=21,088 loops=4)

  • Join Filter: (((brand_info.server)::text = (daily_revenue_summary.server)::text) AND ((brand_info.brand_id)::numeric = daily_revenue_summary.brand_id) AND ((brand_info.platform_id)::numeric = daily_revenue_summary.platform_id))
6. 92.368 504.170 ↓ 91.9 96,588 4 / 4

Merge Join (cost=31,903.38..34,649.31 rows=1,051 width=139) (actual time=340.361..504.170 rows=96,588 loops=4)

  • Merge Cond: ((((promotion_optins.brand_id)::numeric) = ((brand_info.brand_id)::numeric)) AND (((promotion_optins.platform_id)::numeric) = ((brand_info.platform_id)::numeric)) AND ((promotion_optins.server)::text = (brand_info.server)::text))
7. 313.485 393.876 ↑ 1.3 103,690 4 / 4

Sort (cost=30,551.85..30,886.24 rows=133,756 width=85) (actual time=332.131..393.876 rows=103,690 loops=4)

  • Sort Key: ((promotion_optins.brand_id)::numeric), ((promotion_optins.platform_id)::numeric), promotion_optins.server
  • Sort Method: external merge Disk: 12,080kB
8. 80.391 80.391 ↑ 1.3 103,690 4 / 4

Parallel Seq Scan on promotion_optins (cost=0.00..15,413.58 rows=133,756 width=85) (actual time=0.025..80.391 rows=103,690 loops=4)

  • Filter: ((promo_name)::text = 'Daily Wins-Slot Championship'::text)
  • Rows Removed by Filter: 196
9. 11.326 17.926 ↓ 32.3 97,373 4 / 4

Sort (cost=1,351.52..1,359.06 rows=3,015 width=54) (actual time=8.215..17.926 rows=97,373 loops=4)

  • Sort Key: ((brand_info.brand_id)::numeric), ((brand_info.platform_id)::numeric), brand_info.server
  • Sort Method: quicksort Memory: 467kB
10. 6.600 6.600 ↑ 1.1 2,643 4 / 4

Seq Scan on brand_info (cost=0.00..1,177.29 rows=3,015 width=54) (actual time=0.037..6.600 rows=2,643 loops=4)

  • Filter: ((hub)::text = 'Manila'::text)
  • Rows Removed by Filter: 16,997
11. 17,868.873 17,868.873 ↓ 0.0 0 386,354 / 4

Index Scan using daily_revenue_summary_device_unq on daily_revenue_summary (cost=0.70..8.21 rows=1 width=109) (actual time=0.171..0.185 rows=0 loops=386,354)

  • Index Cond: ((revenue_date = promotion_optins.summary) AND (revenue_date >= '2020-08-03'::date) AND (revenue_date <= '2020-08-04'::date) AND ((server)::text = (promotion_optins.server)::text) AND (platform_id = (promotion_optins.platform_id)::numeric) AND (brand_id = (promotion_optins.brand_id)::numeric))
  • Filter: ((product)::text = 'SLOTS'::text)
Planning time : 3.506 ms
Execution time : 24,386.136 ms