Die vorliegende Übersetzung wurde maschinell erstellt. Im Falle eines Konflikts oder eines Widerspruchs zwischen dieser übersetzten Fassung und der englischen Fassung (einschließlich infolge von Verzögerungen bei der Übersetzung) ist die englische Fassung maßgeblich.
Anwendungsfall 4 — IN oder EXISTS
Wenn die Abfrage NOT IN Operatoren IN oder hat, empfehlen wir, den Abfrageplan zu überprüfen, um sicherzustellen, dass der richtige Index verwendet wird. Wenn nicht der richtige Index verwendet wird und die Abfrageleistung länger als erwartet in Anspruch nimmt, versuchen Sie, die Abfrage mit den NOT EXISTS Bedingungen EXISTS oder neu zu schreiben.
Betrachten Sie das folgende Beispiel, das verwendetNOT 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"
Die Abfrage benötigt mehr als 37 Sekunden (366 Millisekunden), um 4 Millionen Datensätze abzurufen.
Der Abfrageplan besagt, dass ein Sequenzscan für die in der Unterabfrage verwendete Tabelle durchgeführt wird. vehicleservicehistory Der Sequenzscan erzeugt eine große Anzahl von Datensätzen. Für jeden dieser Datensätze in der Unterabfrage führt die Abfrage einen vollständigen Tabellenscan durch, was das Leistungsproblem verursacht.
Um den Sequenzscan der Unterabfrage zu vermeiden, schreiben Sie die Unterabfrage neu, sodass sie eine korrelierte Unterabfrage mit verwendet. NOT EXISTS Die korrelierte Unterabfrage verwendet einen Indexscan und eine reduzierte Anzahl von Tabellenscans:
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"
Nach der Änderung benötigt die Abfrage weniger als 13 ms, um 4 Millionen Datensätze zu verarbeiten
Gemäß dem Abfrageplan der geänderten Abfrage vehicleservicehistory kann in der Tabelle ein Indexscan durchgeführt werden. Die Verwendung eines Indexscans reduziert die Kosten und die Anzahl der betroffenen Zeilen. Auf diese Weise können Sie die Laufzeit einer Abfrage reduzieren und ihre Leistung erhöhen.
