Query Plan 案例入門

Plan-Before-Tuning.md

                                                                     QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=107446.22..266934.22 rows=1 width=60) (actual time=561.213..14329.521 rows=15 loops=1)
   ->  Nested Loop Left Join  (cost=107445.79..139804.60 rows=1 width=24) (actual time=558.920..7446.517 rows=15 loops=1)
         ->  Index Scan using dtapb001_pk on dtapb001 a  (cost=0.56..19.74 rows=1 width=18) (actual time=0.209..1.322 rows=15 loops=1)
               Index Cond: ((emp_id = 'JXXXXXXXXX'::bpchar) AND (vrfy_wkym <= '202009'::numeric) AND (vrfy_wkym >= '201908'::numeric))
               Filter: (substr((emp_org_id)::text, 2, 1) = '3'::text)
               Rows Removed by Filter: 30
         ->  Bitmap Heap Scan on pg060 s  (cost=107445.23..139784.85 rows=1 width=24) (actual time=496.333..496.333 rows=0 loops=15)
               Recheck Cond: (a.vrfy_wkym = vrfy_wkym)
               Filter: ((a.div_no = (div_no)::bpchar) AND (a.emp_org_id = (emp_org_id)::bpchar))
               Rows Removed by Filter: 38118
               Heap Blocks: exact=9023
               ->  Bitmap Index Scan on pg060_idx1  (cost=0.00..107445.23 rows=34550 width=0) (actual time=489.915..489.915 rows=38118 loops=15)
                     Index Cond: (vrfy_wkym = a.vrfy_wkym)
   ->  Index Scan using pg059_pk on pg059 b  (cost=0.43..127129.61 rows=1 width=38) (actual time=31.014..458.859 rows=1 loops=15)
         Index Cond: (vrfy_wkym = a.vrfy_wkym)
         Filter: ((a.div_no = (div_no)::bpchar) AND (a.emp_org_id = (unit_org_id)::bpchar))
         Rows Removed by Filter: 33968
 Planning Time: 1.914 ms
 Execution Time: 14329.638 ms