explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Zvsc

Settings
# exclusive inclusive rows x rows loops node
1. 0.015 702.559 ↑ 2.0 2 1

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

  • Output: m_fincl_year_prd.descn, (sum(COALESCE(monthly_ret.colxn_monthly, '0'::numeric)) FILTER (WHERE (m_fincl_year_prd.month = 4))), (sum(COALESCE(monthly_ret.colxn_monthly, '0'::numeric)) FILTER (WHERE (m_fincl_year_prd.month = 5))), (sum(COALESCE(mont (...)
  • Sort Key: m_fincl_year_prd.fy DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=50845 read=38314
2.          

CTE collection

3. 3.087 343.036 ↑ 35.6 9 1

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

  • Output: m_fincl_year_prd_1.prd, sum(((COALESCE(fact_gstin_period.gstr3b_collection, '0'::numeric) + COALESCE(fact_gstin_period.gstr3b_interest_sgst, '0'::numeric)) + COALESCE(fact_gstin_period.gstr3b_latefee_sgst, '0'::numeric))), ((gstin_dc_mast (...)
  • Group Key: m_fincl_year_prd_1.prd, (gstin_dc_master.dc_id)::integer
  • Buffers: shared hit=25406 read=19173
4. 28.093 339.949 ↓ 3.8 6,271 1

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

  • Output: m_fincl_year_prd_1.prd, (gstin_dc_master.dc_id)::integer, fact_gstin_period.gstr3b_collection, fact_gstin_period.gstr3b_interest_sgst, fact_gstin_period.gstr3b_latefee_sgst
  • Join Filter: (fact_gstin_period.period_id = m_fincl_year_prd_1.id)
  • Rows Removed by Join Filter: 207497
  • Buffers: shared hit=25406 read=19173
5. 0.456 0.456 ↓ 1.2 24 1

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

  • Output: m_fincl_year_prd_1.prd, m_fincl_year_prd_1.id
  • Filter: (m_fincl_year_prd_1.fy = ANY (ARRAY[f_get_current_fy(), (f_get_current_fy() - 101)]))
  • Rows Removed by Filter: 15
  • Buffers: shared hit=2
6. 16.475 311.400 ↓ 2.8 8,907 24

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

  • Output: fact_gstin_period.gstr3b_collection, fact_gstin_period.gstr3b_interest_sgst, fact_gstin_period.gstr3b_latefee_sgst, fact_gstin_period.period_id, gstin_dc_master.dc_id
  • Buffers: shared hit=25404 read=19173
7. 6.279 294.925 ↓ 2.8 8,907 1

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

  • Output: fact_gstin_period.gstr3b_collection, fact_gstin_period.gstr3b_interest_sgst, fact_gstin_period.gstr3b_latefee_sgst, fact_gstin_period.period_id, gstin_dc_master.dc_id
  • Buffers: shared hit=25404 read=19173
8. 168.965 168.965 ↓ 4.5 6,299 1

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

  • Output: gstin_dc_master.dc_id, gstin_dc_master.ou_id, gstin_dc_master.gstin, gstin_dc_master.legal_name, gstin_dc_master.trade_name, gstin_dc_master.migrated, gstin_dc_master.composition, gstin_dc_master.jurisdiction, gsti (...)
  • Filter: ((gstin_dc_master.dc_id)::integer = 6)
  • Rows Removed by Filter: 272678
  • Buffers: shared hit=1280 read=19173
9. 119.681 119.681 ↑ 23.0 1 6,299

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

  • Output: fact_gstin_period.gstr3b_collection, fact_gstin_period.gstr3b_interest_sgst, fact_gstin_period.gstr3b_latefee_sgst, fact_gstin_period.period_id, fact_gstin_period.gstin
  • Index Cond: (fact_gstin_period.gstin = gstin_dc_master.gstin)
  • Buffers: shared hit=24124
10.          

CTE monthly_ret

11. 2.517 358.714 ↑ 24.6 13 1

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

  • Output: m_fincl_year_prd_2.prd, sum(COALESCE(fact_gstin_period_1.gstr3b_collection, '0'::numeric)), ((gstin_dc_master_1.dc_id)::integer)
  • Group Key: m_fincl_year_prd_2.prd, (gstin_dc_master_1.dc_id)::integer
  • Buffers: shared hit=25438 read=19141
12. 150.914 356.197 ↓ 27.6 8,845 1

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

  • Output: m_fincl_year_prd_2.prd, (gstin_dc_master_1.dc_id)::integer, fact_gstin_period_1.gstr3b_collection
  • 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
  • Buffers: shared hit=25438 read=19141
13. 0.467 0.467 ↓ 1.2 24 1

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

  • Output: m_fincl_year_prd_2.prd
  • Filter: (m_fincl_year_prd_2.fy = ANY (ARRAY[f_get_current_fy(), (f_get_current_fy() - 101)]))
  • Rows Removed by Filter: 15
  • Buffers: shared hit=2
14. 17.593 204.816 ↓ 2.8 8,907 24

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

  • Output: fact_gstin_period_1.gstr3b_collection, fact_gstin_period_1.gstr3b_filing_date, gstin_dc_master_1.dc_id
  • Buffers: shared hit=25436 read=19141
15. 6.226 187.223 ↓ 2.8 8,907 1

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

  • Output: fact_gstin_period_1.gstr3b_collection, fact_gstin_period_1.gstr3b_filing_date, gstin_dc_master_1.dc_id
  • Buffers: shared hit=25436 read=19141
16. 111.708 111.708 ↓ 4.5 6,299 1

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

  • Output: gstin_dc_master_1.dc_id, gstin_dc_master_1.ou_id, gstin_dc_master_1.gstin, gstin_dc_master_1.legal_name, gstin_dc_master_1.trade_name, gstin_dc_master_1.migrated, gstin_dc_master_1.composition, gstin_dc_master_1.ju (...)
  • Filter: ((gstin_dc_master_1.dc_id)::integer = 6)
  • Rows Removed by Filter: 272678
  • Buffers: shared hit=1312 read=19141
17. 69.289 69.289 ↑ 23.0 1 6,299

Index Scan using fact_gstin_period_pkey on gst_reports.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)

  • Output: fact_gstin_period_1.gstr3b_collection, fact_gstin_period_1.gstr3b_filing_date, fact_gstin_period_1.gstin
  • Index Cond: (fact_gstin_period_1.gstin = gstin_dc_master_1.gstin)
  • Buffers: shared hit=24124
18. 0.071 702.544 ↑ 2.0 2 1

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

  • Output: m_fincl_year_prd.descn, sum(COALESCE(monthly_ret.colxn_monthly, '0'::numeric)) FILTER (WHERE (m_fincl_year_prd.month = 4)), sum(COALESCE(monthly_ret.colxn_monthly, '0'::numeric)) FILTER (WHERE (m_fincl_year_prd.month = 5)), sum(COALESCE(mon (...)
  • Group Key: m_fincl_year_prd.fy, m_fincl_year_prd.descn
  • Buffers: shared hit=50845 read=38314
19. 0.046 702.473 ↑ 3.9 13 1

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

  • Output: m_fincl_year_prd.fy, m_fincl_year_prd.descn, monthly_ret.colxn_monthly, m_fincl_year_prd.month, collection.colxn
  • 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
  • Buffers: shared hit=50845 read=38314
20. 358.728 358.728 ↑ 24.6 13 1

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

  • Output: monthly_ret.prd, monthly_ret.colxn_monthly
  • Buffers: shared hit=25438 read=19141
21. 0.012 343.699 ↑ 1.3 24 1

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

  • Output: m_fincl_year_prd.descn, m_fincl_year_prd.month, m_fincl_year_prd.fy, m_fincl_year_prd.prd, collection.colxn, collection.prd
  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=25407 read=19173
22. 0.030 343.687 ↑ 1.3 24 1

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

  • Output: m_fincl_year_prd.descn, m_fincl_year_prd.month, m_fincl_year_prd.fy, m_fincl_year_prd.prd, collection.colxn, collection.prd
  • Inner Unique: true
  • Hash Cond: (collection.prd = m_fincl_year_prd.prd)
  • Buffers: shared hit=25407 read=19173
23. 343.047 343.047 ↑ 35.6 9 1

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

  • Output: collection.prd, collection.colxn
  • Buffers: shared hit=25406 read=19173
24. 0.018 0.610 ↓ 1.2 24 1

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

  • Output: m_fincl_year_prd.descn, m_fincl_year_prd.month, m_fincl_year_prd.fy, m_fincl_year_prd.prd
  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=1
25. 0.592 0.592 ↓ 1.2 24 1

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

  • Output: m_fincl_year_prd.descn, m_fincl_year_prd.month, m_fincl_year_prd.fy, m_fincl_year_prd.prd
  • Filter: (m_fincl_year_prd.fy = ANY (ARRAY[f_get_current_fy(), (f_get_current_fy() - 101)]))
  • Rows Removed by Filter: 15
  • Buffers: shared hit=1
Planning time : 3.256 ms
Execution time : 703.321 ms