Caso de uso 4: IN o EXISTS

Si la consulta tiene NOT IN operadores IN o, se recomienda comprobar el plan de consulta para confirmar que se está utilizando el índice correcto. Si no se utiliza el índice adecuado y el rendimiento de la consulta tarda más de lo esperado, intenta reescribir la consulta utilizando las NOT EXISTS condiciones EXISTS o.

Considera el siguiente ejemplo, que usaNOT 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"

La consulta tarda más de 37 segundos (366 milisegundos) en recuperar 4 millones de registros.

El plan de consulta indica que se realiza un escaneo secuencial en la tabla utilizada en la subconsultavehicleservicehistory. El escaneo secuencial produce una gran cantidad de registros. Para cada uno de esos registros de la subconsulta, la consulta está realizando un escaneo completo de la tabla, lo que provoca un problema de rendimiento.

Para evitar el escaneo secuencial de la subconsulta, reescribe la subconsulta para usar una subconsulta correlacionada con ella. NOT EXISTS La subconsulta correlacionada utilizará un escaneo de índices y un número reducido de escaneos de tablas:

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"

Tras la modificación, la consulta tarda menos de 13 ms en procesar 4 millones de registros

Según el plan de consulta de la consulta modificada, la tabla vehicleservicehistory puede tener un escaneo de índice. El uso de un escaneo de índices reduce el costo y la cantidad de filas afectadas. De esta forma, puede reducir el tiempo de ejecución de una consulta y aumentar su rendimiento.