explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZFI0

Settings
# exclusive inclusive rows x rows loops node
1. 976.911 14,646.711 ↓ 1,129.4 135,524 1

Sort (cost=42,368.87..42,369.17 rows=120 width=313) (actual time=14,567.094..14,646.711 rows=135,524 loops=1)

  • Sort Key: (trunc((a.sgst_tax_settle_2017_18 / '100000'::numeric), 2)) DESC
  • Sort Method: external merge Disk: 25672kB
2. 929.015 13,669.800 ↓ 1,129.4 135,524 1

Nested Loop Anti Join (cost=38,092.76..42,364.73 rows=120 width=313) (actual time=3,372.378..13,669.800 rows=135,524 loops=1)

3. 1,016.340 4,588.333 ↓ 1,427.2 388,212 1

Hash Right Join (cost=38,092.20..41,640.07 rows=272 width=226) (actual time=3,372.245..4,588.333 rows=388,212 loops=1)

  • Hash Cond: (log_r3b_top_non_filers.gstin = a.provisional_id)
4. 299.268 356.322 ↓ 1.5 78,296 1

HashAggregate (cost=19,652.66..21,392.87 rows=53,545 width=60) (actual time=155.363..356.322 rows=78,296 loops=1)

  • Group Key: log_r3b_top_non_filers.gstin, log_r3b_top_non_filers.ret_period
5. 38.296 57.054 ↑ 1.0 85,582 1

Bitmap Heap Scan on log_r3b_top_non_filers (cost=1,797.05..18,787.34 rows=86,532 width=28) (actual time=20.346..57.054 rows=85,582 loops=1)

  • Recheck Cond: (ret_period = 112018)
  • Heap Blocks: exact=5117
6. 18.758 18.758 ↑ 1.0 85,582 1

Bitmap Index Scan on idx_log_r3b_retprd_gstn (cost=0.00..1,775.41 rows=86,532 width=0) (actual time=18.758..18.758 rows=85,582 loops=1)

  • Index Cond: (ret_period = 112018)
7. 924.727 3,215.671 ↓ 1,427.2 388,212 1

Hash (cost=18,430.70..18,430.70 rows=272 width=186) (actual time=3,215.671..3,215.671 rows=388,212 loops=1)

  • Buckets: 131072 (originally 1024) Batches: 8 (originally 1) Memory Usage: 19457kB
8. 2,290.944 2,290.944 ↓ 1,427.2 388,212 1

Index Scan using test_gst_1 on test_gst a (cost=0.42..18,430.70 rows=272 width=186) (actual time=0.070..2,290.944 rows=388,212 loops=1)

  • Index Cond: ((top_tax_group >= 130001) AND (top_tax_group <= 800000) AND (test_func_gst(dt_reg) <= 201811) AND (test_func_gst_dtdereg(dt_dereg) >= 201811) AND (CASE WHEN (gst_comp = 'Y'::bpchar) THEN 'Y'::text ELSE 'N'::text END = (...)
  • Filter: (st_cn_jurd = 'S'::bpchar)
  • Rows Removed by Filter: 175159
9. 8,152.452 8,152.452 ↑ 1.0 1 388,212

Index Only Scan using pk_form_gstr3b on form_gstr3b c (cost=0.56..2.62 rows=1 width=16) (actual time=0.021..0.021 rows=1 loops=388,212)

  • Index Cond: ((gstin = a.provisional_id) AND (ret_period = 112018))
  • Heap Fetches: 945