explain.depesz.com

PostgreSQL's explain analyze made readable

Result: P6NT : ㅁㅁ

Settings
# exclusive inclusive rows x rows loops node
1. 1.769 17,251.306 ↓ 30.0 30 1

Subquery Scan on t (cost=29,600.33..29,667.74 rows=1 width=222) (actual time=9,187.186..17,251.306 rows=30 loops=1)

  • Filter: ((t.row_num >= 1) AND (t.row_num <= 30))
  • Rows Removed by Filter: 6669
  • Buffers: shared hit=235018 read=13703
2. 340.173 17,249.537 ↓ 6,699.0 6,699 1

WindowAgg (cost=29,600.33..29,667.73 rows=1 width=222) (actual time=9,187.185..17,249.537 rows=6,699 loops=1)

  • Buffers: shared hit=235018 read=13703
3. 21.327 9,141.814 ↓ 6,699.0 6,699 1

Sort (cost=29,600.33..29,600.34 rows=1 width=79) (actual time=9,140.348..9,141.814 rows=6,699 loops=1)

  • Sort Key: dvs.std_st_dt DESC, dvs.dailyvoca_std_id DESC
  • Sort Method: quicksort Memory: 1135kB
  • Buffers: shared hit=131731 read=7682
4. 12.049 9,120.487 ↓ 6,699.0 6,699 1

Nested Loop Left Join (cost=135.66..29,600.32 rows=1 width=79) (actual time=33.383..9,120.487 rows=6,699 loops=1)

  • Buffers: shared hit=131725 read=7682
5. 4.167 9,034.749 ↓ 6,699.0 6,699 1

Nested Loop Left Join (cost=135.10..29,599.69 rows=1 width=74) (actual time=33.353..9,034.749 rows=6,699 loops=1)

  • Buffers: shared hit=102621 read=7682
6. 2.859 3,537.402 ↓ 6,699.0 6,699 1

Nested Loop Anti Join (cost=134.55..29,598.89 rows=1 width=69) (actual time=27.089..3,537.402 rows=6,699 loops=1)

  • Join Filter: (rd.ord_seq = od.ord_seq)
  • Buffers: shared hit=78059 read=3126
7. 6.481 3,467.553 ↓ 6,699.0 6,699 1

Nested Loop (cost=133.70..29,597.63 rows=1 width=69) (actual time=27.060..3,467.553 rows=6,699 loops=1)

  • Join Filter: (dvs.ord_seq = od.ord_seq)
  • Buffers: shared hit=57923 read=3126
8. 6.923 384.228 ↓ 6,718.0 6,718 1

Nested Loop (cost=133.14..29,596.64 rows=1 width=84) (actual time=22.980..384.228 rows=6,718 loops=1)

  • Buffers: shared hit=27141 read=251
9. 249.084 269.641 ↑ 1.0 6,729 1

Bitmap Heap Scan on ldv_std dvs (cost=132.58..569.93 rows=6,748 width=69) (actual time=22.222..269.641 rows=6,729 loops=1)

  • Recheck Cond: ((voca_listen_div_cd)::text = '0002'::text)
  • Heap Blocks: exact=218
  • Buffers: shared hit=1 read=238
10. 20.557 20.557 ↑ 1.0 6,730 1

Bitmap Index Scan on ix_lecture_ldv_std_01 (cost=0.00..130.90 rows=6,748 width=0) (actual time=20.557..20.557 rows=6,730 loops=1)

  • Index Cond: ((voca_listen_div_cd)::text = '0002'::text)
  • Buffers: shared hit=1 read=20
11. 107.664 107.664 ↑ 1.0 1 6,729

Index Only Scan using ix_payment_pay_ord_09 on pay_ord o (cost=0.56..4.30 rows=1 width=23) (actual time=0.016..0.016 rows=1 loops=6,729)

  • Index Cond: ((ord_cancl_yn = 'N'::text) AND (ord_no = (dvs.ord_no)::text) AND (mem_no = (dvs.mem_no)::text) AND (ord_stat_cd = '0002'::text))
  • Heap Fetches: 0
  • Buffers: shared hit=27140 read=13
12. 3,076.844 3,076.844 ↑ 1.0 1 6,718

Index Scan using ix_payment_pay_ord_dtl_08 on pay_ord_dtl od (cost=0.56..0.97 rows=1 width=19) (actual time=0.458..0.458 rows=1 loops=6,718)

  • Index Cond: (((ord_no)::text = (o.ord_no)::text) AND ((ord_cancl_yn)::text = 'N'::text) AND ((goods_div_cd)::text = '0010'::text))
  • Filter: ((goods_dtl_div_cd)::text = '0022'::text)
  • Buffers: shared hit=30782 read=2875
13. 6.699 66.990 ↓ 0.0 0 6,699

Nested Loop (cost=0.84..1.26 rows=1 width=19) (actual time=0.010..0.010 rows=0 loops=6,699)

  • Buffers: shared hit=20136
14. 60.291 60.291 ↓ 0.0 0 6,699

Index Scan using ix_payment_pay_refund_03 on pay_refund r (cost=0.42..0.48 rows=1 width=23) (actual time=0.009..0.009 rows=0 loops=6,699)

  • Index Cond: ((ord_no)::text = (o.ord_no)::text)
  • Filter: ((refund_div_cd)::text = '0002'::text)
  • Buffers: shared hit=20136
15. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_payment_pay_refund_dtl on pay_refund_dtl rd (cost=0.42..0.75 rows=2 width=12) (never executed)

  • Index Cond: ((refund_no)::text = (r.refund_no)::text)
16. 5,493.180 5,493.180 ↓ 0.0 0 6,699

Index Scan using pk_payment_pay_pay on pay_pay p (cost=0.56..0.77 rows=2 width=20) (actual time=0.819..0.820 rows=0 loops=6,699)

  • Index Cond: ((ord_no)::text = (o.ord_no)::text)
  • Filter: ((pay_means_cd)::text = ANY ('{0001,0002,0003,0012,0014,0016,0017,0009}'::text[]))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=24562 read=4556
17. 73.689 73.689 ↓ 0.0 0 6,699

Index Scan using pk_payment_pay_pay on pay_pay ps (cost=0.56..0.72 rows=2 width=20) (actual time=0.011..0.011 rows=0 loops=6,699)

  • Index Cond: (((o.ord_no)::text = (ord_no)::text) AND (pay_seq = 1))
  • Buffers: shared hit=29104
18.          

SubPlan (for WindowAgg)

19. 20.097 937.860 ↑ 1.0 1 6,699

Aggregate (cost=24.40..24.41 rows=1 width=32) (actual time=0.140..0.140 rows=1 loops=6,699)

  • Buffers: shared hit=23184 read=753
20. 917.763 917.763 ↑ 2.5 4 6,699

Index Scan using pk_lecture_ldv_scr on ldv_scr dvp (cost=0.42..24.34 rows=10 width=7) (actual time=0.124..0.137 rows=4 loops=6,699)

  • Index Cond: (dailyvoca_std_id = dvs.dailyvoca_std_id)
  • Buffers: shared hit=23184 read=753
21. 13.398 33.495 ↑ 1.0 1 6,699

Aggregate (cost=24.38..24.39 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=6,699)

  • Buffers: shared hit=23944
22. 20.097 20.097 ↑ 4.0 1 6,699

Index Scan using pk_lecture_ldv_scr on ldv_scr dvp_1 (cost=0.42..24.37 rows=4 width=8) (actual time=0.002..0.003 rows=1 loops=6,699)

  • Index Cond: (dailyvoca_std_id = dvs.dailyvoca_std_id)
  • Filter: ((acum_yn)::text = 'N'::text)
  • Rows Removed by Filter: 2
  • Buffers: shared hit=23937
23. 2.770 6,796.195 ↑ 1.0 1 1,385

Limit (cost=1.99..18.16 rows=1 width=32) (actual time=4.906..4.907 rows=1 loops=1,385)

  • Buffers: shared hit=20004 read=5268
24. 2.770 6,793.425 ↑ 1.0 1 1,385

Nested Loop (cost=1.99..18.16 rows=1 width=32) (actual time=4.905..4.905 rows=1 loops=1,385)

  • Buffers: shared hit=20004 read=5268
25. 2.770 5,893.175 ↑ 1.0 1 1,385

Nested Loop (cost=1.55..17.65 rows=1 width=7) (actual time=4.255..4.255 rows=1 loops=1,385)

  • Buffers: shared hit=15107 read=4623
26. 5.540 4,882.125 ↑ 1.0 1 1,385

Nested Loop (cost=0.99..17.03 rows=1 width=20) (actual time=3.525..3.525 rows=1 loops=1,385)

  • Buffers: shared hit=8759 read=3732
27. 2,552.555 2,552.555 ↑ 1.0 1 1,385

Index Scan using ix_payment_pay_discpn_use_01 on pay_discpn_use s_lcu (cost=0.43..8.45 rows=1 width=4) (actual time=1.843..1.843 rows=1 loops=1,385)

  • Index Cond: (((ord_no)::text = (o.ord_no)::text) AND (ord_seq = od.ord_seq))
  • Buffers: shared hit=3674 read=1886
28. 2,324.030 2,324.030 ↑ 1.0 1 1,385

Index Scan using pk_payment_pay_discpn_reg_mem on pay_discpn_reg_mem s_lcl (cost=0.56..8.58 rows=1 width=24) (actual time=1.678..1.678 rows=1 loops=1,385)

  • Index Cond: (cpn_reg_no = s_lcu.cpn_reg_no)
  • Buffers: shared hit=5085 read=1846
29. 1,008.280 1,008.280 ↑ 1.0 1 1,385

Index Scan using pk_payment_pay_discpn_certkey on pay_discpn_certkey s_lca (cost=0.56..0.62 rows=1 width=27) (actual time=0.728..0.728 rows=1 loops=1,385)

  • Index Cond: ((certkey)::text = (s_lcl.certkey)::text)
  • Buffers: shared hit=6348 read=891
30. 897.480 897.480 ↑ 1.0 1 1,385

Index Scan using pk_payment_pay_discpn on pay_discpn s_lc (cost=0.43..0.51 rows=1 width=51) (actual time=0.648..0.648 rows=1 loops=1,385)

  • Index Cond: ((cpn_id)::text = (s_lca.cpn_id)::text)
  • Buffers: shared hit=4897 read=645