explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xrPZ

Settings
# exclusive inclusive rows x rows loops node
1. 11.663 264,330.279 ↓ 91.1 3,916 1

Unique (cost=5,354.87..5,355.84 rows=43 width=71) (actual time=264,316.278..264,330.279 rows=3,916 loops=1)

2. 55.560 264,318.616 ↓ 296.8 12,761 1

Sort (cost=5,354.87..5,354.98 rows=43 width=71) (actual time=264,316.275..264,318.616 rows=12,761 loops=1)

  • Sort Key: play_summary_sing.summary_date, play_summary_sing.server, play_summary_sing.casinoid, play_summary_sing.customerid, play_summary_sing.memberid, play_summary_sing.gameid, play_summary_sing.last_login_country_id, (CASE WHEN (play_summary_sing.summary_date < promotion_optins.promo_start_date) THEN 'Before Promo'::text WHEN (play_summary_sing.summary_date > promotion_optins.promo_end_date) THEN 'After Promo'::text ELSE 'During Promo'::text END)
  • Sort Method: quicksort Memory: 2,179kB
3. 4.932 264,263.056 ↓ 296.8 12,761 1

Hash Join (cost=4,603.27..5,353.71 rows=43 width=71) (actual time=264,254.868..264,263.056 rows=12,761 loops=1)

  • Hash Cond: ((c.game_id)::numeric = play_summary_sing.gameid)
4. 3.341 3.341 ↑ 1.5 5,762 1

Index Only Scan using games_idx on games_info c (cost=0.41..707.63 rows=8,514 width=4) (actual time=0.037..3.341 rows=5,762 loops=1)

  • Filter: ((game)::text <> ALL ('{"Sweet Bonanza","Aztec Gems","Sweet Bonanza Xmas","Wild West Gold","Fire Strike","The Dog House","Release the Kraken","Money Mouse","888 Dragons","Caishen''s Cash","Wolf Gold","Fire 88"}'::text[]))
  • Rows Removed by Filter: 276
  • Heap Fetches: 0
5. 13.349 264,254.783 ↓ 4,524.0 4,524 1

Hash (cost=4,602.85..4,602.85 rows=1 width=47) (actual time=264,254.783..264,254.783 rows=4,524 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 418kB
6. 29.490 264,241.434 ↓ 4,524.0 4,524 1

Nested Loop (cost=1.52..4,602.85 rows=1 width=47) (actual time=47,882.813..264,241.434 rows=4,524 loops=1)

  • Join Filter: (((play_summary_sing.server)::text = (promotion_optins.server)::text) AND (play_summary_sing.casinoid = (promotion_optins.brand_id)::numeric) AND (play_summary_sing.customerid = (promotion_optins.platform_id)::numeric))
7. 23.977 3,238.044 ↓ 5,742.0 5,742 1

Nested Loop (cost=0.97..445.37 rows=1 width=54) (actual time=3.338..3,238.044 rows=5,742 loops=1)

8. 43.931 43.931 ↓ 5,743.0 5,743 1

Index Scan using play_summary_sing_idx on play_summary_sing (cost=0.56..2.79 rows=1 width=39) (actual time=0.040..43.931 rows=5,743 loops=1)

  • Index Cond: ((summary_date >= '2020-08-04'::date) AND (summary_date < '2020-08-05'::date) AND (currencyid = '145'::numeric))
9. 3,170.136 3,170.136 ↑ 1.0 1 5,743

Index Only Scan using brand_info_pk on brand_info cc (cost=0.41..442.57 rows=1 width=15) (actual time=0.130..0.552 rows=1 loops=5,743)

  • Index Cond: (server = (play_summary_sing.server)::text)
  • Filter: ((play_summary_sing.casinoid = (brand_id)::numeric) AND (play_summary_sing.customerid = (platform_id)::numeric))
  • Rows Removed by Filter: 1,029
  • Heap Fetches: 2,377,602
10. 260,973.900 260,973.900 ↑ 1.0 1 5,742

Index Only Scan using promotion_optins_pk on promotion_optins (cost=0.55..4,157.46 rows=1 width=23) (actual time=45.439..45.450 rows=1 loops=5,742)

  • Index Cond: ((promo_name = 'To Be Number One'::text) AND (server = (cc.server)::text))
  • Filter: (((cc.brand_id)::numeric = (brand_id)::numeric) AND ((cc.platform_id)::numeric = (platform_id)::numeric))
  • Rows Removed by Filter: 5
  • Heap Fetches: 0
Planning time : 3.842 ms
Execution time : 264,330.931 ms