explain.depesz.com

PostgreSQL's explain analyze made readable

Result: csJr

Settings
# exclusive inclusive rows x rows loops node
1. 33.187 95,649.645 ↓ 172.0 172 1

Merge Join (cost=3,693,773.88..3,696,083.69 rows=1 width=394) (actual time=91,889.563..95,649.645 rows=172 loops=1)

  • Merge Cond: ((a.summary = a_2.revenue_date) AND (a.brand_id = a_2.brand_id) AND (a.platform_id = a_2.platform_id))
  • Join Filter: (((a.server)::text = (a_2.server)::text) AND ((a.product)::text = (a_2.product)::text) AND ((a.tabletype)::text = (c_1.tabletype)::text) AND ((abs(((trunc(sum(a_2.bets_usd), 0)) - a.bets)) > '5'::numeric) OR (abs(((trunc(sum(a_2.wins_usd), 0)) - ((a.wins + COALESCE(b.jackpot, '0'::numeric))))) > '5'::numeric) OR (abs(((trunc(sum(a_2.margin_usd), 0)) - ((a.margin - COALESCE(b.jackpot, '0'::numeric))))) > '5'::numeric)))
  • Rows Removed by Join Filter: 25360
2. 16.365 50,487.899 ↓ 1.8 25,441 1

Merge Left Join (cost=1,402,571.72..1,402,892.79 rows=14,221 width=131) (actual time=50,468.747..50,487.899 rows=25,441 loops=1)

  • Merge Cond: ((a.summary = b.summary) AND (a.brand_id = ((b.brand_id)::numeric)) AND (a.platform_id = ((b.platform_id)::numeric)) AND ((a.server)::text = (b.server)::text))
3. 42.106 50,448.622 ↓ 1.8 25,173 1

Sort (cost=1,401,806.44..1,401,841.99 rows=14,221 width=131) (actual time=50,445.879..50,448.622 rows=25,173 loops=1)

  • Sort Key: a.summary, a.brand_id, a.platform_id, a.server
  • Sort Method: quicksort Memory: 4377kB
4. 4.052 50,406.516 ↓ 1.9 26,396 1

Subquery Scan on a (cost=1,398,031.60..1,400,825.50 rows=14,221 width=131) (actual time=46,477.569..50,406.516 rows=26,396 loops=1)

5. 222.061 50,402.464 ↓ 1.9 26,396 1

Finalize GroupAggregate (cost=1,398,031.60..1,400,683.29 rows=14,221 width=145) (actual time=46,477.557..50,402.464 rows=26,396 loops=1)

  • Group Key: a_1.summary, a_1.brand_id, a_1.platform_id, brand_info.brand, a_1.server, a_1.product, c.tabletype
6. 5,059.569 50,180.403 ↓ 6.5 92,007 1

Gather Merge (cost=1,398,031.60..1,399,865.48 rows=14,224 width=145) (actual time=46,477.390..50,180.403 rows=92,007 loops=1)

  • Workers Planned: 7
  • Workers Launched: 7
7. 2,344.182 45,120.834 ↓ 5.7 11,501 8 / 8

Partial GroupAggregate (cost=1,397,031.48..1,397,122.92 rows=2,032 width=145) (actual time=41,854.103..45,120.834 rows=11,501 loops=8)

  • Group Key: a_1.summary, a_1.brand_id, a_1.platform_id, brand_info.brand, a_1.server, a_1.product, c.tabletype
8. 4,704.738 42,776.652 ↓ 1,526.0 3,100,755 8 / 8

Sort (cost=1,397,031.48..1,397,036.56 rows=2,032 width=88) (actual time=41,853.610..42,776.652 rows=3,100,755 loops=8)

  • Sort Key: a_1.summary, a_1.brand_id, a_1.platform_id, brand_info.brand, a_1.server
  • Sort Method: external merge Disk: 287256kB
9. 1,276.381 38,071.914 ↓ 1,526.0 3,100,755 8 / 8

Nested Loop Left Join (cost=1,395,422.29..1,396,919.84 rows=2,032 width=88) (actual time=3,911.112..38,071.914 rows=3,100,755 loops=8)

10. 1,520.502 5,787.984 ↓ 1,526.0 3,100,755 8 / 8

Merge Join (cost=1,395,421.88..1,395,993.09 rows=2,032 width=74) (actual time=3,910.994..5,787.984 rows=3,100,755 loops=8)

  • Merge Cond: ((a_1.brand_id = c.brand_id) AND (a_1.platform_id = c.platform_id) AND ((a_1.server)::text = (c.server)::text))
11. 453.493 3,985.484 ↓ 9.8 209,531 8 / 8

Sort (cost=1,387,756.36..1,387,809.96 rows=21,442 width=67) (actual time=3,840.356..3,985.484 rows=209,531 loops=8)

  • Sort Key: a_1.brand_id, a_1.platform_id, a_1.server
  • Sort Method: external merge Disk: 15760kB
12. 3,531.991 3,531.991 ↓ 9.8 209,531 8 / 8

Parallel Seq Scan on invoice_summary a_1 (cost=0.00..1,386,213.81 rows=21,442 width=67) (actual time=630.252..3,531.991 rows=209,531 loops=8)

  • Filter: (((product)::text = 'SLOTS'::text) AND (date_trunc('month'::text, (summary)::timestamp with time zone) = '2020-03-01 00:00:00+05:30'::timestamp with time zone))
  • Rows Removed by Filter: 3576762
13. 243.628 281.998 ↓ 92.0 3,120,791 8 / 8

Sort (cost=7,665.49..7,750.32 rows=33,933 width=29) (actual time=70.616..281.998 rows=3,120,791 loops=8)

  • Sort Key: c.brand_id, c.platform_id, c.server
  • Sort Method: quicksort Memory: 4217kB
14. 38.370 38.370 ↓ 1.0 34,311 8 / 8

Seq Scan on revenue_parameters c (cost=0.00..5,111.97 rows=33,933 width=29) (actual time=0.073..38.370 rows=34,311 loops=8)

  • Filter: (((product_type)::text = 'SLOTS'::text) AND ((tabletype)::text = 'Generic'::text))
  • Rows Removed by Filter: 4067
15. 31,007.549 31,007.549 ↑ 1.0 1 24,806,039 / 8

Index Scan using brand_info_idx on brand_info (cost=0.41..0.45 rows=1 width=33) (actual time=0.010..0.010 rows=1 loops=24,806,039)

  • Index Cond: (((a_1.product)::text = (product)::text) AND ((product)::text = 'SLOTS'::text) AND ((a_1.server)::text = (server)::text) AND (a_1.brand_id = brand_id))
  • Filter: (a_1.platform_id = (platform_id)::numeric)
16. 1.303 22.912 ↓ 10.5 648 1

Sort (cost=765.28..765.43 rows=62 width=48) (actual time=22.798..22.912 rows=648 loops=1)

  • Sort Key: b.summary, ((b.brand_id)::numeric), ((b.platform_id)::numeric), b.server
  • Sort Method: quicksort Memory: 75kB
17. 0.289 21.609 ↓ 10.5 648 1

Subquery Scan on b (cost=760.78..763.43 rows=62 width=48) (actual time=20.531..21.609 rows=648 loops=1)

18. 0.801 21.320 ↓ 10.5 648 1

GroupAggregate (cost=760.78..762.81 rows=62 width=50) (actual time=20.523..21.320 rows=648 loops=1)

  • Group Key: daily_jackpot_summary.summary, daily_jackpot_summary.server, daily_jackpot_summary.brand_id, daily_jackpot_summary.platform_id, daily_jackpot_summary.game_id
19. 1.204 20.519 ↓ 10.3 648 1

Sort (cost=760.78..760.93 rows=63 width=21) (actual time=20.465..20.519 rows=648 loops=1)

  • Sort Key: daily_jackpot_summary.summary, daily_jackpot_summary.server, daily_jackpot_summary.brand_id, daily_jackpot_summary.platform_id, daily_jackpot_summary.game_id
  • Sort Method: quicksort Memory: 75kB
20. 19.315 19.315 ↓ 10.3 648 1

Index Scan using jackpot_summary_pkey on daily_jackpot_summary (cost=0.29..758.89 rows=63 width=21) (actual time=9.582..19.315 rows=648 loops=1)

  • Index Cond: ((server)::text = ANY ('{DENMARK,DENMARK1,DENMARK2,TAIWAN,TAIWAN1,GIB,GIB1,BAHAMAS}'::text[]))
  • Filter: (date_trunc('month'::text, (summary)::timestamp with time zone) = '2020-03-01 00:00:00+05:30'::timestamp with time zone)
  • Rows Removed by Filter: 18618
21. 7.210 45,128.559 ↓ 2.8 25,532 1

Materialize (cost=2,291,202.16..2,293,015.23 rows=9,115 width=143) (actual time=41,402.595..45,128.559 rows=25,532 loops=1)

22. 205.258 45,121.349 ↓ 2.8 25,172 1

Finalize GroupAggregate (cost=2,291,202.16..2,292,901.30 rows=9,115 width=143) (actual time=41,402.578..45,121.349 rows=25,172 loops=1)

  • Group Key: a_2.revenue_date, a_2.brand_id, a_2.platform_id, brand_info_1.brand, a_2.server, a_2.product, c_1.tabletype
23. 4,104.252 44,916.091 ↓ 11.1 101,243 1

Gather Merge (cost=2,291,202.16..2,292,377.22 rows=9,114 width=143) (actual time=41,402.467..44,916.091 rows=101,243 loops=1)

  • Workers Planned: 7
  • Workers Launched: 7
24. 2,181.758 40,811.839 ↓ 9.7 12,655 8 / 8

Partial GroupAggregate (cost=2,290,202.04..2,290,260.63 rows=1,302 width=143) (actual time=37,775.023..40,811.839 rows=12,655 loops=8)

  • Group Key: a_2.revenue_date, a_2.brand_id, a_2.platform_id, brand_info_1.brand, a_2.server, a_2.product, c_1.tabletype
25. 4,335.702 38,630.081 ↓ 2,386.8 3,107,557 8 / 8

Sort (cost=2,290,202.04..2,290,205.30 rows=1,302 width=86) (actual time=37,774.820..38,630.081 rows=3,107,557 loops=8)

  • Sort Key: a_2.revenue_date, a_2.brand_id, a_2.platform_id, brand_info_1.brand, a_2.server
  • Sort Method: external merge Disk: 356016kB
26. 465.612 34,294.379 ↓ 2,386.8 3,107,557 8 / 8

Nested Loop Left Join (cost=2,289,046.49..2,290,134.69 rows=1,302 width=86) (actual time=4,247.191..34,294.379 rows=3,107,557 loops=8)

27. 1,337.510 5,860.751 ↓ 2,386.8 3,107,557 8 / 8

Merge Join (cost=2,289,046.08..2,289,536.77 rows=1,302 width=72) (actual time=4,247.081..5,860.751 rows=3,107,557 loops=8)

  • Merge Cond: ((a_2.brand_id = c_1.brand_id) AND (a_2.platform_id = c_1.platform_id) AND ((a_2.server)::text = (c_1.server)::text))
28. 367.252 4,221.964 ↓ 14.9 210,288 8 / 8

Sort (cost=2,281,380.55..2,281,415.85 rows=14,121 width=65) (actual time=4,148.604..4,221.964 rows=210,288 loops=8)

  • Sort Key: a_2.brand_id, a_2.platform_id, a_2.server
  • Sort Method: external merge Disk: 20496kB
29. 3,854.712 3,854.712 ↓ 14.9 210,288 8 / 8

Parallel Seq Scan on daily_revenue_summary a_2 (cost=0.00..2,280,407.22 rows=14,121 width=65) (actual time=651.869..3,854.712 rows=210,288 loops=8)

  • Filter: (((product)::text = 'SLOTS'::text) AND (date_trunc('month'::text, (revenue_date)::timestamp with time zone) = '2020-03-01 00:00:00+05:30'::timestamp with time zone))
  • Rows Removed by Filter: 2426537
30. 250.971 301.277 ↓ 92.2 3,128,861 8 / 8

Sort (cost=7,665.49..7,750.32 rows=33,933 width=29) (actual time=98.446..301.277 rows=3,128,861 loops=8)

  • Sort Key: c_1.brand_id, c_1.platform_id, c_1.server
  • Sort Method: quicksort Memory: 4217kB
31. 50.306 50.306 ↓ 1.0 34,311 8 / 8

Seq Scan on revenue_parameters c_1 (cost=0.00..5,111.97 rows=33,933 width=29) (actual time=0.082..50.306 rows=34,311 loops=8)

  • Filter: (((product_type)::text = 'SLOTS'::text) AND ((tabletype)::text = 'Generic'::text))
  • Rows Removed by Filter: 4067
32. 27,968.016 27,968.016 ↑ 1.0 1 24,860,459 / 8

Index Scan using brand_info_idx on brand_info brand_info_1 (cost=0.41..0.45 rows=1 width=33) (actual time=0.009..0.009 rows=1 loops=24,860,459)

  • Index Cond: (((a_2.product)::text = (product)::text) AND ((product)::text = 'SLOTS'::text) AND ((a_2.server)::text = (server)::text) AND (a_2.brand_id = brand_id))
  • Filter: (a_2.platform_id = (platform_id)::numeric)
Planning time : 4.913 ms
Execution time : 95,836.453 ms