explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MBwX : prod

Settings
# exclusive inclusive rows x rows loops node
1. 0.059 751.125 ↓ 8.0 8 1

Group (cost=189.43..189.45 rows=1 width=16) (actual time=751.046..751.125 rows=8 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=91477
2. 0.197 751.066 ↓ 460.0 460 1

Sort (cost=189.43..189.43 rows=1 width=16) (actual time=751.043..751.066 rows=460 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: 46kB
  • Buffers: shared hit=91477
3. 4.977 750.869 ↓ 460.0 460 1

Nested Loop (cost=2.40..189.42 rows=1 width=16) (actual time=123.654..750.869 rows=460 loops=1)

  • Join Filter: (((p.prod_no)::text = (g.prod_no)::text) AND CASE WHEN ((g.is_pak_fly)::text = 'Y'::text) THEN (q.org_qty >= 0) ELSE (q.org_qty > 0) END)
  • Buffers: shared hit=91477
4. 3.885 725.742 ↓ 10,075.0 10,075 1

Nested Loop (cost=1.96..186.58 rows=1 width=64) (actual time=0.252..725.742 rows=10,075 loops=1)

  • Join Filter: ((p.prod_no)::text = (pgp.prod_no)::text)
  • Buffers: shared hit=59282
5. 0.384 2.502 ↓ 2,015.0 2,015 1

Nested Loop (cost=1.40..71.04 rows=1 width=41) (actual time=0.191..2.502 rows=2,015 loops=1)

  • Buffers: shared hit=1149
6. 0.012 0.168 ↓ 13.0 13 1

Nested Loop (cost=0.84..64.01 rows=1 width=55) (actual time=0.062..0.168 rows=13 loops=1)

  • Buffers: shared hit=51
7. 0.036 0.036 ↓ 3.0 3 1

Index Scan using idx_tpm_linkno on tblprod p (cost=0.42..20.50 rows=1 width=14) (actual time=0.025..0.036 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
8. 0.120 0.120 ↓ 2.0 4 3

Index Scan using idx_tpdc_pn on tblprod_detail pd (cost=0.42..43.49 rows=2 width=41) (actual time=0.023..0.040 rows=4 loops=3)

  • Index Cond: ((prod_no)::text = (p.prod_no)::text)
  • Filter: (((part_tp)::text = 'GFC'::text) AND (is_use = 'Y'::bpchar))
  • Rows Removed by Filter: 28
  • Buffers: shared hit=45
9. 1.950 1.950 ↓ 155.0 155 13

Index Scan using tblpart_qty_unique on tblpart_qty q (cost=0.56..7.02 rows=1 width=40) (actual time=0.018..0.150 rows=155 loops=13)

  • 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: 0
  • Buffers: shared hit=1098
10. 719.355 719.355 ↓ 5.0 5 2,015

Index Scan using idx_pgp_ppcttb on tblprod_grp_price pgp (cost=0.56..115.53 rows=1 width=23) (actual time=0.088..0.357 rows=5 loops=2,015)

  • 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))
  • Buffers: shared hit=58133
11. 20.150 20.150 ↓ 0.0 0 10,075

Index Scan using idx_tg_pb on tblgrup g (cost=0.43..2.80 rows=1 width=25) (actual time=0.002..0.002 rows=0 loops=10,075)

  • Index Cond: (((prod_no)::text = (pgp.prod_no)::text) AND ((beg_dt)::text = (pgp.beg_dt)::text))
  • Filter: (((is_hid_grup)::text = 'N'::text) AND ((grup_tp)::text = ANY ('{2,3,4,6,7}'::text[])) AND ((beg_dt)::text >= to_char(LOCALTIMESTAMP, 'YYYYMMDD'::text)))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=32195