explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LGpA : uat

Settings
# exclusive inclusive rows x rows loops node
1. 0.024 10,418.037 ↓ 2.0 2 1

Group (cost=185.57..185.60 rows=1 width=16) (actual time=10,418.007..10,418.037 rows=2 loops=1)

  • Group Key: (date_part('year'::text, (to_date((g.beg_dt)::text, 'yyyyMMdd'::text))::timestamp without time zone)), (date_part('month'::text, (to_date((g.beg_dt)::text, 'yyyyMMdd'::text))::timestamp without time zone))
  • Buffers: shared hit=797164
2. 0.186 10,418.013 ↓ 185.0 185 1

Sort (cost=185.57..185.58 rows=1 width=16) (actual time=10,418.004..10,418.013 rows=185 loops=1)

  • Sort Key: (date_part('year'::text, (to_date((g.beg_dt)::text, 'yyyyMMdd'::text))::timestamp without time zone)), (date_part('month'::text, (to_date((g.beg_dt)::text, 'yyyyMMdd'::text))::timestamp without time zone))
  • Sort Method: quicksort Memory: 33kB
  • Buffers: shared hit=797164
3. 18.443 10,417.827 ↓ 185.0 185 1

Nested Loop (cost=2.40..185.56 rows=1 width=16) (actual time=155.248..10,417.827 rows=185 loops=1)

  • Join Filter: (((pgp.prod_no)::text = (p.prod_no)::text) AND ((pgp.beg_dt)::text = (g.beg_dt)::text) AND CASE WHEN ((g.is_pak_fly)::text = 'Y'::text) THEN (q.org_qty >= 0) ELSE (q.org_qty > 0) END)
  • Rows Removed by Join Filter: 122625
  • Buffers: shared hit=797164
4. 0.270 2.809 ↓ 175.0 175 1

Nested Loop (cost=1.28..59.71 rows=1 width=80) (actual time=0.107..2.809 rows=175 loops=1)

  • Join Filter: ((p.prod_no)::text = (pd.prod_no)::text)
  • Buffers: shared hit=716
5. 0.105 0.541 ↓ 37.0 37 1

Nested Loop (cost=0.86..46.62 rows=1 width=39) (actual time=0.067..0.541 rows=37 loops=1)

  • Buffers: shared hit=78
6. 0.037 0.037 ↓ 3.0 3 1

Index Scan using idx_tpm_linkno on tblprod p (cost=0.42..16.47 rows=1 width=14) (actual time=0.025..0.037 rows=3 loops=1)

  • Index Cond: ((link_no)::text = 'GFG0000000094'::text)
  • Filter: (((sale_status)::text = ANY ('{Y,Q}'::text[])) AND ((prod_type)::text = 'GFG'::text))
  • Buffers: shared hit=6
7. 0.399 0.399 ↓ 6.0 12 3

Index Scan using idx_tg_ptb on tblgrup g (cost=0.43..30.13 rows=2 width=25) (actual time=0.025..0.133 rows=12 loops=3)

  • Index Cond: (((prod_no)::text = (p.prod_no)::text) AND ((grup_tp)::text = ANY ('{2,3,4,6,7}'::text[])) AND ((beg_dt)::text >= to_char(LOCALTIMESTAMP, 'YYYYMMDD'::text)))
  • Filter: ((is_hid_grup)::text = 'N'::text)
  • Buffers: shared hit=72
8. 1.998 1.998 ↓ 2.5 5 37

Index Scan using idx_tpdc_pn on tblprod_detail pd (cost=0.42..13.06 rows=2 width=41) (actual time=0.030..0.054 rows=5 loops=37)

  • Index Cond: ((prod_no)::text = (g.prod_no)::text)
  • Filter: (((part_tp)::text = 'GFC'::text) AND (is_use = 'Y'::bpchar))
  • Rows Removed by Filter: 27
  • Buffers: shared hit=638
9. 32.795 10,396.575 ↓ 702.0 702 175

Nested Loop (cost=1.12..125.82 rows=1 width=63) (actual time=0.041..59.409 rows=702 loops=175)

  • Buffers: shared hit=796448
10. 43.050 43.050 ↓ 179.0 179 175

Index Scan using tblpart_qty_unique on tblpart_qty q (cost=0.56..7.00 rows=1 width=40) (actual time=0.018..0.246 rows=179 loops=175)

  • Index Cond: (((part_no)::text = (pd.part_no)::text) AND ((bat_no)::text = (pd.bat_no)::text))
  • Filter: ((close_st)::text = 'N'::text)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=19570
11. 10,320.730 10,320.730 ↓ 4.0 4 31,370

Index Scan using idx_pgp_ppcttb on tblprod_grp_price pgp (cost=0.56..118.81 rows=1 width=23) (actual time=0.131..0.329 rows=4 loops=31,370)

  • Index Cond: (((prod_no)::text = (pd.prod_no)::text) AND ((port_tp)::text = 'B2C'::text) AND ((beg_dt)::text = (q.beg_dt)::text))
  • Filter: ((price > '0'::numeric) AND ((price_tp)::text = '1'::text))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=776878