explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bifn

Settings
# exclusive inclusive rows x rows loops node
1. 45.189 29,237.535 ↓ 2.5 24,311 1

Hash Right Join (cost=2,297,004.91..2,297,089.09 rows=9,610 width=339) (actual time=29,190.406..29,237.535 rows=24,311 loops=1)

  • Hash Cond: (((a_1.server)::text = (a.server)::text) AND (b.brand_id = a.brand_id) AND ((b.product_type)::text = (a.product)::text) AND (b.platform_id = a.platform_id) AND ((b.tabletype)::text = (a.tabletype)::text) AND (to_char((a_1.summary)::timestamp with time zone, 'yyyy-mm'::text) = to_char((a.summary)::timestamp with time zone, 'yyyy-mm'::text)))
2. 2.082 1,453.240 ↓ 1,289.0 1,289 1

GroupAggregate (cost=941.15..941.21 rows=1 width=176) (actual time=1,451.083..1,453.240 rows=1,289 loops=1)

  • Group Key: a_1.summary, b.brand_id, b.platform_id, b.product_type, a_1.server, a_1.casino, b.tabletype, b.ggr_group
3. 2.294 1,451.158 ↓ 1,293.0 1,293 1

Sort (cost=941.15..941.16 rows=1 width=71) (actual time=1,451.053..1,451.158 rows=1,293 loops=1)

  • Sort Key: a_1.summary, b.brand_id, b.platform_id, a_1.server, a_1.casino, b.ggr_group
  • Sort Method: quicksort Memory: 230kB
4. 0.939 1,448.864 ↓ 1,293.0 1,293 1

Nested Loop (cost=0.41..941.14 rows=1 width=71) (actual time=0.216..1,448.864 rows=1,293 loops=1)

5. 2.028 2.028 ↓ 204.7 1,433 1

Seq Scan on test_recon a_1 (cost=0.00..56.06 rows=7 width=44) (actual time=0.041..2.028 rows=1,433 loops=1)

  • Filter: (to_char((summary)::timestamp with time zone, 'yyyy-mm'::text) = '2020-03'::text)
6. 1,445.897 1,445.897 ↑ 1.0 1 1,433

Index Scan using ukse18cgea289658bw4gmx3jo3i on revenue_parameters b (cost=0.41..126.43 rows=1 width=49) (actual time=0.985..1.009 rows=1 loops=1,433)

  • Index Cond: (((server)::text = (a_1.server)::text) AND ((brand)::text = (a_1.casino)::text))
  • Filter: (((product_type)::text = 'SLOTS'::text) AND ((tabletype)::text = 'Generic'::text) AND (to_char((date_month)::timestamp with time zone, 'yyyy-mm'::text) = '2020-03'::text))
  • Rows Removed by Filter: 11
7. 36.179 27,739.106 ↓ 2.5 24,311 1

Hash (cost=2,295,823.51..2,295,823.51 rows=9,610 width=147) (actual time=27,739.106..27,739.106 rows=24,311 loops=1)

  • Buckets: 32768 (originally 16384) Batches: 1 (originally 1) Memory Usage: 2696kB
8. 3.413 27,702.927 ↓ 2.5 24,311 1

Subquery Scan on a (cost=2,293,908.21..2,295,823.51 rows=9,610 width=147) (actual time=24,390.387..27,702.927 rows=24,311 loops=1)

9. 221.675 27,699.514 ↓ 2.5 24,311 1

Finalize GroupAggregate (cost=2,293,908.21..2,295,727.41 rows=9,610 width=147) (actual time=24,390.385..27,699.514 rows=24,311 loops=1)

  • Group Key: a_2.revenue_date, a_2.brand_id, a_2.platform_id, brand_info.brand, a_2.server, a_2.product, c.tabletype, c.ggr_group
10. 728.194 27,477.839 ↓ 10.6 101,581 1

Gather Merge (cost=2,293,908.21..2,295,150.77 rows=9,611 width=147) (actual time=24,390.232..27,477.839 rows=101,581 loops=1)

  • Workers Planned: 7
  • Workers Launched: 7
11. 2,140.019 26,749.645 ↓ 9.2 12,698 8 / 8

Partial GroupAggregate (cost=2,292,908.09..2,292,973.30 rows=1,373 width=147) (actual time=23,822.969..26,749.645 rows=12,698 loops=8)

  • Group Key: a_2.revenue_date, a_2.brand_id, a_2.platform_id, brand_info.brand, a_2.server, a_2.product, c.tabletype, c.ggr_group
12. 4,336.241 24,609.626 ↓ 2,049.9 2,814,510 8 / 8

Sort (cost=2,292,908.09..2,292,911.52 rows=1,373 width=90) (actual time=23,822.644..24,609.626 rows=2,814,510 loops=8)

  • Sort Key: a_2.revenue_date, a_2.brand_id, a_2.platform_id, brand_info.brand, a_2.server, c.ggr_group
  • Sort Method: external merge Disk: 291904kB
13. 1,391.186 20,273.385 ↓ 2,049.9 2,814,510 8 / 8

Nested Loop Left Join (cost=2,291,878.16..2,292,836.53 rows=1,373 width=90) (actual time=3,357.721..20,273.385 rows=2,814,510 loops=8)

14. 1,218.706 4,809.648 ↓ 2,049.9 2,814,510 8 / 8

Merge Join (cost=2,291,877.87..2,292,379.08 rows=1,373 width=77) (actual time=3,357.622..4,809.648 rows=2,814,510 loops=8)

  • Merge Cond: ((a_2.brand_id = c.brand_id) AND (a_2.platform_id = c.platform_id) AND ((a_2.server)::text = (c.server)::text))
15. 307.974 3,365.630 ↓ 12.8 189,771 8 / 8

Sort (cost=2,284,212.34..2,284,249.40 rows=14,822 width=65) (actual time=3,301.510..3,365.630 rows=189,771 loops=8)

  • Sort Key: a_2.brand_id, a_2.platform_id, a_2.server
  • Sort Method: external merge Disk: 15512kB
16. 3,057.656 3,057.656 ↓ 12.8 189,771 8 / 8

Parallel Seq Scan on daily_revenue_summary a_2 (cost=0.00..2,283,185.51 rows=14,822 width=65) (actual time=76.831..3,057.656 rows=189,771 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: 2423608
17. 198.421 225.312 ↓ 83.6 2,835,544 8 / 8

Sort (cost=7,665.49..7,750.32 rows=33,933 width=34) (actual time=56.088..225.312 rows=2,835,544 loops=8)

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

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

  • Filter: (((product_type)::text = 'SLOTS'::text) AND ((tabletype)::text = 'Generic'::text))
  • Rows Removed by Filter: 4067
19. 14,072.551 14,072.551 ↑ 1.0 1 22,516,081 / 8

Index Scan using brand_info_idx on brand_info (cost=0.29..0.32 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=22,516,081)

  • 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 : 8.780 ms
Execution time : 29,289.311 ms