Caso de uso 4 — IN ou EXISTS

Se a consulta tiver NOT IN operadores IN ou, recomendamos verificar o plano de consulta para confirmar se o índice adequado está sendo usado. Se o índice adequado não estiver sendo usado e o desempenho da consulta estiver demorando mais do que o esperado, tente reescrever a consulta usando as NOT EXISTS condições EXISTS ou.

Considere o exemplo a seguir, 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"

A consulta está levando mais de 37 segundos e 366 milissegundos para recuperar 4 milhões de registros.

O plano de consulta indica que uma varredura de sequência é executada na tabela usada na subconsultavehicleservicehistory. A varredura da sequência está produzindo um grande número de registros. Para cada um desses registros na subconsulta, a consulta está executando uma varredura completa da tabela, o que está causando o problema de desempenho.

Para evitar a varredura de sequência na subconsulta, reescreva a subconsulta para usar uma subconsulta correlacionada com. NOT EXISTS A subconsulta correlacionada usará uma varredura de índice e um número reduzido de varreduras de tabela:

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"

Após a modificação, a consulta está levando menos de 13 ms para processar 4 milhões de registros

De acordo com o plano de consulta da consulta modificada, a tabela vehicleservicehistory pode ter uma varredura de índice. Usar uma varredura de índice reduz o custo e o número de linhas afetadas. Dessa forma, você pode reduzir o tempo de execução de uma consulta e aumentar seu desempenho.