explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1Yf8

Settings
# exclusive inclusive rows x rows loops node
1. 0.017 675.229 ↑ 2.0 2 1

Sort (cost=301,915.79..301,915.80 rows=4 width=788) (actual time=675.229..675.229 rows=2 loops=1)

  • Sort Key: m_fincl_year_prd.fy DESC
  • Sort Method: quicksort Memory: 25kB
2.          

CTE collection

3. 2.989 325.692 ↑ 35.6 9 1

GroupAggregate (cost=0.56..150,787.65 rows=320 width=43) (actual time=297.459..325.692 rows=9 loops=1)

  • Group Key: m_fincl_year_prd_1.prd, (gstin_dc_master.dc_id)::integer
4. 25.957 322.703 ↓ 3.8 6,271 1

Nested Loop (cost=0.56..150,761.46 rows=1,647 width=22) (actual time=293.874..322.703 rows=6,271 loops=1)

  • Join Filter: (fact_gstin_period.period_id = m_fincl_year_prd_1.id)
  • Rows Removed by Join Filter: 207497
5. 0.466 0.466 ↓ 1.2 24 1

Index Scan using idx_m_fincl_year_prd_1 on m_fincl_year_prd m_fincl_year_prd_1 (cost=0.14..32.42 rows=20 width=11) (actual time=0.044..0.466 rows=24 loops=1)

  • Filter: (fy = ANY (ARRAY[f_get_current_fy(), (f_get_current_fy() - 101)]))
  • Rows Removed by Filter: 15
6. 15.490 296.280 ↓ 2.8 8,907 24

Materialize (cost=0.42..149,765.53 rows=3,211 width=18) (actual time=0.008..12.345 rows=8,907 loops=24)

7. 3.317 280.790 ↓ 2.8 8,907 1

Nested Loop (cost=0.42..149,749.48 rows=3,211 width=18) (actual time=0.197..280.790 rows=8,907 loops=1)

8. 157.792 157.792 ↓ 4.5 6,299 1

Seq Scan on gstin_dc_master (cost=0.00..25,335.10 rows=1,395 width=19) (actual time=0.098..157.792 rows=6,299 loops=1)

  • Filter: ((dc_id)::integer = 6)
  • Rows Removed by Filter: 272678
9. 119.681 119.681 ↑ 23.0 1 6,299

Index Scan using fact_gstin_period_pkey on fact_gstin_period (cost=0.42..88.96 rows=23 width=31) (actual time=0.016..0.019 rows=1 loops=6,299)

  • Index Cond: (gstin = gstin_dc_master.gstin)
10.          

CTE monthly_ret

11. 2.340 348.738 ↑ 24.6 13 1

GroupAggregate (cost=0.56..151,083.94 rows=320 width=43) (actual time=200.666..348.738 rows=13 loops=1)

  • Group Key: m_fincl_year_prd_2.prd, (gstin_dc_master_1.dc_id)::integer
12. 147.357 346.398 ↓ 27.6 8,845 1

Nested Loop (cost=0.56..151,075.93 rows=321 width=16) (actual time=0.136..346.398 rows=8,845 loops=1)

  • Join Filter: (to_char((fact_gstin_period_1.gstr3b_filing_date)::timestamp with time zone, 'mmyyyy'::text) = m_fincl_year_prd_2.prd)
  • Rows Removed by Join Filter: 204923
13. 0.465 0.465 ↓ 1.2 24 1

Index Scan using idx_m_fincl_year_prd_1 on m_fincl_year_prd m_fincl_year_prd_2 (cost=0.14..32.42 rows=20 width=7) (actual time=0.034..0.465 rows=24 loops=1)

  • Filter: (fy = ANY (ARRAY[f_get_current_fy(), (f_get_current_fy() - 101)]))
  • Rows Removed by Filter: 15
14. 14.850 198.576 ↓ 2.8 8,907 24

Materialize (cost=0.42..149,765.53 rows=3,211 width=12) (actual time=0.003..8.274 rows=8,907 loops=24)

15. 5.417 183.726 ↓ 2.8 8,907 1

Nested Loop (cost=0.42..149,749.48 rows=3,211 width=12) (actual time=0.062..183.726 rows=8,907 loops=1)

16. 109.020 109.020 ↓ 4.5 6,299 1

Seq Scan on gstin_dc_master gstin_dc_master_1 (cost=0.00..25,335.10 rows=1,395 width=19) (actual time=0.030..109.020 rows=6,299 loops=1)

  • Filter: ((dc_id)::integer = 6)
  • Rows Removed by Filter: 272678
17. 69.289 69.289 ↑ 23.0 1 6,299

Index Scan using fact_gstin_period_pkey on fact_gstin_period fact_gstin_period_1 (cost=0.42..88.96 rows=23 width=25) (actual time=0.010..0.011 rows=1 loops=6,299)

  • Index Cond: (gstin = gstin_dc_master_1.gstin)
18. 0.074 675.212 ↑ 2.0 2 1

HashAggregate (cost=43.88..44.16 rows=4 width=788) (actual time=675.207..675.212 rows=2 loops=1)

  • Group Key: m_fincl_year_prd.fy, m_fincl_year_prd.descn
19. 0.037 675.138 ↑ 3.9 13 1

Hash Right Join (cost=29.00..37.51 rows=51 width=80) (actual time=527.036..675.138 rows=13 loops=1)

  • Hash Cond: (monthly_ret.prd = m_fincl_year_prd.prd)
  • Filter: (COALESCE(collection.prd, monthly_ret.prd) IS NOT NULL)
  • Rows Removed by Filter: 11
20. 348.751 348.751 ↑ 24.6 13 1

CTE Scan on monthly_ret (cost=0.00..6.40 rows=320 width=64) (actual time=200.669..348.751 rows=13 loops=1)

21. 0.012 326.350 ↑ 1.3 24 1

Hash (cost=28.60..28.60 rows=32 width=87) (actual time=326.350..326.350 rows=24 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
22. 0.029 326.338 ↑ 1.3 24 1

Hash Right Join (cost=21.34..28.60 rows=32 width=87) (actual time=298.079..326.338 rows=24 loops=1)

  • Hash Cond: (collection.prd = m_fincl_year_prd.prd)
23. 325.705 325.705 ↑ 35.6 9 1

CTE Scan on collection (cost=0.00..6.40 rows=320 width=64) (actual time=297.464..325.705 rows=9 loops=1)

24. 0.017 0.604 ↓ 1.2 24 1

Hash (cost=21.09..21.09 rows=20 width=23) (actual time=0.604..0.604 rows=24 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
25. 0.587 0.587 ↓ 1.2 24 1

Seq Scan on m_fincl_year_prd (cost=0.00..21.09 rows=20 width=23) (actual time=0.126..0.587 rows=24 loops=1)

  • Filter: (fy = ANY (ARRAY[f_get_current_fy(), (f_get_current_fy() - 101)]))
  • Rows Removed by Filter: 15
Planning time : 3.509 ms
Execution time : 676.261 ms