사용 사례 4 — IN 또는 EXISTS

쿼리에 IN 또는 NOT IN 연산자가 있는 경우 쿼리 계획을 확인하여 적절한 색인이 사용되고 있는지 확인하는 것이 좋습니다. 적절한 인덱스가 사용되지 않고 쿼리 성능이 예상보다 오래 걸리는 경우 EXISTS 또는 NOT EXISTS 조건을 사용하여 쿼리를 다시 작성해 보세요.

를 사용하는 NOT IN 다음 예제를 고려해 보십시오.

EXPLAIN ANALYZE SELECT 
  TEST_VEH.TEST_VEH_ID, 
  TEST_VEH.VEHICLE_ID, 
  TEST_VEH.SERVICEPROGRAM_ID, 
  TEST_VEH.STARTDATE, 
  TEST_VEH.ENDDATE, 
  TEST_VEH.OILTYPE_ID 
FROM PERF_USER.TEST_VEH TEST_VEH 
JOIN  PERF_USER.OILTYPE OT ON  OT.OILTYPE_ID =TEST_VEH.OILTYPE_ID  
JOIN  PERF_USER.SERVICEPROGRAM SP ON  SP.SERVICEPROGRAM_ID = TEST_VEH.SERVICEPROGRAM_ID
WHERE SP.PROGNAME  = '18FCE8FDAF365BB' 
  AND OT.OILTYPE_ID  =3
  AND TEST_VEH.ENDDATE  IS NOT NULL 
  AND TEST_VEH.TEST_VEH_ID NOT IN 
                      (SELECT TEST_VEH_ID
                              FROM PERF_USER.VEHICLESERVICEHISTORY 
                              WHERE  TEST_VEH_ID > 1 
                      );
-----------------------------------------------------------------------
"Nested Loop  (cost=1009.16..1188860356305.01 rows=1 width=76) (actual time=37299.891..37347.853 rows=0 loops=1)"
"  ->  Gather  (cost=1009.16..1188860356303.88 rows=1 width=76) (actual time=37299.890..37347.849 rows=0 loops=1)"
"        Workers Planned: 2"
"        Workers Launched: 2"
"        ->  Hash Join  (cost=9.16..1188860355303.78 rows=1 width=76) (actual time=37286.742..37286.751 rows=0 loops=3)"
"              Hash Cond: ((test_veh.serviceprogram_id)::text = (sp.serviceprogram_id)::text)"
"              ->  Parallel Index Scan using test_veh_oiltype_id_ind on test_veh  (cost=0.56..1188860351273.04 rows=1072570 width=76) (actual time=37276.290..37276.292 rows=1 loops=3)"
"                    Index Cond: (oiltype_id = 3)"
"                    Filter: ((enddate IS NOT NULL) AND (NOT (SubPlan 1)))"
"                    Rows Removed by Filter: 0"
"                    SubPlan 1"
"                      ->  Materialize  (cost=0.00..1025071.31 rows=33333332 width=8) (actual time=0.418..23201.432 rows=25001498 loops=4)"
"                            ->  Seq Scan on vehicleservicehistory  (cost=0.00..728195.65 rows=33333332 width=8) (actual time=0.416..13249.975 rows=25001498 loops=4)"
"                                  Filter: (test_veh_id > 1)"
"              ->  Hash  (cost=8.58..8.58 rows=1 width=11) (actual time=9.045..9.046 rows=0 loops=3)"
"                    Buckets: 1024  Batches: 1  Memory Usage: 8kB"
"                    ->  Index Scan using progname_id_ind on serviceprogram sp  (cost=0.56..8.58 rows=1 width=11) (actual time=9.043..9.044 rows=0 loops=3)"
"                          Index Cond: ((progname)::text = '18FCE8FDAF365BB'::text)"
"  ->  Seq Scan on oiltype ot  (cost=0.00..1.12 rows=1 width=8) (never executed)"
"        Filter: (oiltype_id = 3)"
"Planning Time: 37.696 ms"
"Execution Time: 37366.335 ms"

쿼리가 400만 개의 레코드를 검색하는 데 37초 (366밀리초) 이상 걸립니다.

쿼리 계획에는 하위 쿼리에 사용된 테이블에서 시퀀스 스캔이 수행된다고 명시되어 있습니다. vehicleservicehistory 시퀀스 스캔은 많은 수의 레코드를 생성합니다. 하위 쿼리의 각 레코드에 대해 쿼리가 전체 테이블 스캔을 수행하므로 성능 문제가 발생합니다.

하위 쿼리에서 시퀀스 스캔을 방지하려면 상호 관련된 하위 쿼리를 사용하도록 하위 쿼리를 다시 작성하십시오. NOT EXISTS 상관 관계가 있는 하위 쿼리는 인덱스 스캔을 사용하고 테이블 스캔 횟수를 줄였습니다.

EXPLAIN ANALYZE SELECT 
  TEST_VEH.TEST_VEH_ID, 
  TEST_VEH.VEHICLE_ID, 
  TEST_VEH.SERVICEPROGRAM_ID, 
  TEST_VEH.STARTDATE, 
  TEST_VEH.ENDDATE, 
  TEST_VEH.OILTYPE_ID 
FROM PERF_USER.TEST_VEH TEST_VEH 
JOIN  PERF_USER.OILTYPE OT ON  OT.OILTYPE_ID =TEST_VEH.OILTYPE_ID  
JOIN  PERF_USER.SERVICEPROGRAM SP ON  SP.SERVICEPROGRAM_ID = TEST_VEH.SERVICEPROGRAM_ID
WHERE SP.PROGNAME  = '18FCE8FDAF365BB' 
       AND OT.OILTYPE_ID  =3
       AND TEST_VEH.ENDDATE  IS NOT NULL  
       AND  NOT EXISTS 
                    (SELECT TEST_VEH_ID 
                            FROM PERF_USER.VEHICLESERVICEHISTORY 
                            WHERE TEST_VEH.TEST_VEH_ID=VEHICLESERVICEHISTORY.TEST_VEH_ID 
                            AND TEST_VEH_ID > 1 
                    );
---------------------------------------------------------------------
"Nested Loop Anti Join  (cost=1009.03..936146.10 rows=1 width=76) (actual time=12.693..12.810 rows=0 loops=1)"
"  ->  Nested Loop  (cost=1008.59..936141.78 rows=1 width=76) (actual time=12.692..12.809 rows=0 loops=1)"
"        ->  Gather  (cost=1008.59..936140.64 rows=1 width=76) (actual time=12.691..12.807 rows=0 loops=1)"
"              Workers Planned: 2"
"              Workers Launched: 2"
"              ->  Hash Join  (cost=8.59..935140.54 rows=1 width=76) (actual time=0.773..0.774 rows=0 loops=3)"
"                    Hash Cond: ((test_veh.serviceprogram_id)::text = (sp.serviceprogram_id)::text)"
"                    ->  Parallel Seq Scan on test_veh  (cost=0.00..927087.67 rows=2145139 width=76) (actual time=0.672..0.672 rows=1 loops=3)"
"                          Filter: ((enddate IS NOT NULL) AND (oiltype_id = 3))"
"                          Rows Removed by Filter: 7"
"                    ->  Hash  (cost=8.58..8.58 rows=1 width=11) (actual time=0.040..0.040 rows=0 loops=3)"
"                          Buckets: 1024  Batches: 1  Memory Usage: 8kB"
"                          ->  Index Scan using progname_id_ind on serviceprogram sp  (cost=0.56..8.58 rows=1 width=11) (actual time=0.039..0.040 rows=0 loops=3)"
"                                Index Cond: ((progname)::text = '18FCE8FDAF365BB'::text)"
"        ->  Seq Scan on oiltype ot  (cost=0.00..1.12 rows=1 width=8) (never executed)"
"              Filter: (oiltype_id = 3)"
"  ->  Index Only Scan using veh_ser_ind on vehicleservicehistory  (cost=0.44..4.32 rows=1 width=8) (never executed)"
"        Index Cond: ((test_veh_id = test_veh.test_veh_id) AND (test_veh_id > 1))"
"        Heap Fetches: 0"
"Planning Time: 11.115 ms"
"Execution Time: 12.871 ms"

수정 후 쿼리에서 4백만 개의 레코드를 처리하는 데 걸리는 시간은 13ms 미만입니다.

수정된 쿼리의 쿼리 계획에 따르면 테이블에 인덱스 스캔이 있을 vehicleservicehistory 수 있습니다. 인덱스 스캔을 사용하면 비용이 절감되고 영향을 받는 행 수가 줄어듭니다. 이렇게 하면 쿼리 런타임을 줄이고 성능을 높일 수 있습니다.