Leistungsoptimierung

[flight]

Abfrage, die in Stapeljobs > 100 Minuten benötigt, um abgeschlossen zu werden. Wie kann ich die Zeit verkürzen?

–Explain

SELECT DISTINCT
INV_NO_001 “Invoice Number”, Chr(9),
INVOICE.INV_ID_002 “Invoice ID”, Chr(9),
CASE
WHEN INV_TYPE =‘B’ THEN ‘Billed Invoice’
WHEN INV_TYPE =‘M’ THEN ‘Miscellaneous’
END
AS “Document Type”,Chr(9),
CASE WHEN INVOICE.INV_PAID_FLAG = ‘P’ THEN INVOICE.INV_CLOSE_DATE ELSE NULL END AS “Invoice Close Date”, Chr(9),
trim(inv_curr3) “Billed Currency”, Chr(9),
ROUND((INV_AMT / 100), 2) “Invoice Amount”, Chr(9),
ROUND(((INV_AMT - INV_AMT_PAID) / 100),2) “Outstanding Amount”, Chr(9),
ROUND((INV_AMT *EXCH_RATE2 / 100), 2) “Invoice Amount (USD)”, Chr(9),
ROUND(((INV_AMT - INV_AMT_PAID) *EXCH_RATE2 / 100),2) “Outstanding Amount (USD)”, Chr(9),
PERS_ID “Touch By OHR ID”, Chr(9),
PERS_NAME_ “Touch By Name”, Chr(9),
FROM (
SELECT DISTINCT PERS_SSO_ID ,PERS_NAME, INV_ID_002 INVOICE_ID,
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TRIM(Substring(Scr_Line_Desc, 1, 1000)),Chr(10),’ ‘),Chr(13),’ ‘),
Chr(9),’ ‘),Chr(126),’-‘),Chr(44),’ ‘),’"‘,’') Sp_Comments,
IC_NO,IC_NAME

FROM
test.INVESTMENT_CODE INNER JOIN test.INVOICE ON IC_NO_702 = INV_IC_NO_720
INNER JOIN test.INVOICE_SCRATCH_PAD ON (SCR_CUST_NO_722 = INV_CUST_NO_720 AND SCR_FINDER_NO_722 = INV_FINDER_NO_720)
LEFT JOIN test.PERSONNEL ON SCR_USER_ID_722 = PERS_CODE_796 INNER JOIN test.CUSTOMER ON INV_CUST_NO_720 = CUST_NO_717
AND SCR_LINE_DESC_722 IS NOT NULL
and IC_H2_702=‘HCG’ AND IC_H3_702=‘LSC’
AND DATE_TRUNC(‘DAY’,SCR_DATE_722::DATE) >= date_trunc(‘week’, current_timestamp::timestamp(0))
AND DATE_TRUNC(‘DAY’,SCR_DATE_722) <= (current_date + 7 - (extract(dow from current_timestamp::timestamp(0)) || ’ days’)::interval)
AND DATE_TRUNC(‘DAY’,INVOICE_SCRATCH_PAD.TIMESTAMP::DATE) >= date_trunc(‘week’, current_timestamp::timestamp(0))
AND DATE_TRUNC(‘DAY’,INVOICE_SCRATCH_PAD.TIMESTAMP::DATE) <=
(current_date + 7 - (extract(dow from current_date) || ’ days’)::interval)

)A LEFT OUTER JOIN

(
SELECT DISTINCT INV_ID_002 INVOICE_ID, SCR_DATE_722 SP_DATE, SCR_CONTACT_TYPE_722 CONTACT_TYPE,
SCR_OPEN_ITEM_STAT1_722 SP_CODE,
INVOICE_SCRATCH_PAD.TIMESTAMP,
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TRIM(OTHER_TABLE_NAME_794), CHR(10), ’ '), CHR(13), ’ '), CHR(9), ’ '),
CHR(126), ‘-’), CHR(44), ’ '), ‘“”’, ‘’) SP_DESCRIPTION
FROM
test.INVESTMENT_CODE INNER JOIN test.INVOICE ON IC_NO_702 = INV_IC_NO_720

INNER JOIN test.INVOICE_SCRATCH_PAD ON (SCR_CUST_NO_722 = INV_CUST_NO_720
AND SCR_FINDER_NO_722 = INV_FINDER_NO_720)
INNER JOIN test.OTHER_TABLE ON ( OTHER_TABLE_KEY_794 = SCR_OPEN_ITEM_STAT1_722
AND OTHER_TABLE_TYPE_794 = ‘SP’ AND OTHER_TABLE_LANG_794 = ‘E’
AND SCR_LINE_DESC_722 IS NULL
and IC_H2_702=‘HCG’ AND IC_H3_702=‘LSC’
AND DATE_TRUNC(‘DAY’,INVOICE_SCRATCH_PAD.TIMESTAMP::DATE) >= date_trunc(‘week’, current_timestamp::timestamp(0))
AND DATE_TRUNC(‘DAY’,INVOICE_SCRATCH_PAD.TIMESTAMP) <= (current_date + 7 -
(extract(dow from current_timestamp::timestamp(0)) || ’ days’)::interval))
) B ON A.INVOICE_ID = B.INVOICE_ID
AND DATE_TRUNC(‘minute’,A.TIMESTAMP::DATE) = DATE_TRUNC(‘minute’,B.TIMESTAMP::DATE)
AND COALESCE(A.CONTACT_TYPE,‘na’) = COALESCE(B.CONTACT_TYPE, ‘na’)
INNER JOIN test.INVOICE ON A.INVOICE_ID = INVOICE.INV_ID_002
LEFT OUTER JOIN test.EXCHANGE_RATE2 ON test.INVOICE.INV_CURR3_720 = EXCH_CURR3_FROM_792
AND EXCH_RATE_TYPE_792=‘CM’ AND EXCH_CURR3_TO_792=‘USD’
ORDER BY INVOICE.INV_ID_002 ,A.SP_DATE ASC;

– Unique (cost=788268.15…788268.29 rows=2 width=1303)

----- Explain Details:

“Unique (cost=788268.15…788268.29 rows=2 width=1303)”
" → Sort (cost=788268.15…788268.16 rows=2 width=1303)"
" Sort Key: invoice.inv_id_720, a.sp_date, invoice.inv_no_720, a.business_alias, invoice.inv_cust_no_720, invoice.inv_cust_short_name_720, invoice.inv_date_720, invoice.inv_due_date_720, invoice.inv_system_dt_720, a.cust_cc_no_717, a.ic_no_702, a.ic_name_702, (CASE WHEN (invoice.inv_type_720 = ‘B’::bpchar) THEN ‘Billed Invoice’::text WHEN (invoice.inv_type_720 = ‘C’::bpchar) THEN ‘Billed Credit Note’::text WHEN (invoice.inv_type_720 = ‘N’::bpchar) THEN ‘Memo Invoice’::text WHEN (invoice.inv_type_720 = ‘U’::bpchar) THEN ‘Unapplied Cash’::text WHEN (invoice.inv_type_720 = ‘V’::bpchar) THEN ‘Variance (Cash Appln)’::text WHEN (invoice.inv_type_720 = ‘W’::bpchar) THEN ‘Variance (Zero Appln)’::text WHEN (invoice.inv_type_720 = ‘X’::bpchar) THEN ‘Reverse Variance (Cash)’::text WHEN (invoice.inv_type_720 = ‘Y’::bpchar) THEN ‘REVERSE VARIANCE(ZERO)’::text WHEN (invoice.inv_type_720 = ‘E’::bpchar) THEN ‘Charge Entry’::text WHEN (invoice.inv_type_720 = ‘I’::bpchar) THEN ‘Interest Invoice’::text WHEN (invoice.inv_type_720 = ‘M’::bpchar) THEN ‘Miscellaneous’::text ELSE NULL::text END), (CASE WHEN (invoice.inv_paid_flag_720 = ‘P’::bpchar) THEN ‘Closed’::text ELSE ‘Open’::text END), (CASE WHEN (invoice.inv_paid_flag_720 = ‘P’::bpchar) THEN invoice.inv_close_date_720 ELSE NULL::timestamp without time zone END), (TRIM(BOTH FROM invoice.inv_curr3_720)), (round((invoice.inv_amt_720 / ‘100’::numeric), 2)), (round(((invoice.inv_amt_720 - invoice.inv_amt_paid_720) / ‘100’::numeric), 2)), (round(((invoice.inv_amt_720 * exchange_rate2.exch_rate2_792) / ‘100’::numeric), 2)), (round((((invoice.inv_amt_720 - invoice.inv_amt_paid_720) * exchange_rate2.exch_rate2_792) / ‘100’::numeric), 2)), a.pers_sso_id_796, a.pers_name_796, a.contact_type, a.contact_result, b.sp_description, a.sp_comments"
" → Nested Loop Left Join (cost=788250.57…788268.14 rows=2 width=1303)"
" → Nested Loop (cost=788250.28…788267.10 rows=2 width=267)"
" → Merge Left Join (cost=788249.84…788250.18 rows=2 width=182)"
" Merge Cond: ((a.invoice_id = b.invoice_id) AND ((date_trunc(‘minute’::text, ((a.““timestamp””)::date)::timestamp with time zone)) = (date_trunc(‘minute’::text, ((b.““timestamp””)::date)::timestamp with time zone))) AND (((COALESCE(a.contact_type, ‘na’::character varying))::text) = ((COALESCE(b.contact_type, ‘na’::character varying))::text)))"
" → Sort (cost=394264.20…394264.21 rows=2 width=158)"
" Sort Key: a.invoice_id, (date_trunc(‘minute’::text, ((a.““timestamp””)::date)::timestamp with time zone)), ((COALESCE(a.contact_type, ‘na’::character varying))::text)"
" → Subquery Scan on a (cost=394264.09…394264.19 rows=2 width=158)"
" → Unique (cost=394264.09…394264.17 rows=2 width=200)"
" → Sort (cost=394264.09…394264.09 rows=2 width=200)"
" Sort Key: personnel.pers_sso_id_796,

[akretschmer]

das ist schwer lesbar so als Fließtext. Bitte verwende dafür code-Abschnitte, und zeige bitte explain ANALYSE - Ausgabe, die sagt mehr aus.

[pogomips]

@flight

Wie kann ich die Zeit verkürzen?

Dass es schwer lesbar ist, wurde schon gesagt. Explain könnte viel Klarheit bringen.
Was fast alle Deine Where Bedingungen zeigen:
Datumsumformung (Funktionen) über Originalfelder
Das führt ohne weitere Gegenmaßnahmen mit Sicherheit dazu, dass ggF. vorhandene Indizes nicht genutzt werden (können).

Was Du tun kannst:
Etwas “Algebra”, Termumstellung sodass Originaldatumsfelder direkt abgefragt werden (ohne Funktionen) und der funktionale Teil des Kriteriums auf der anderen Seite des Vergleichsausdrucks steht.

[pogomips]

Zunächst mal die Anmerkung, dass mit diesen Codeklumpen echt leicht Augenkrebs entsteht. Das geht bei mir auch aufs Hirn. Keine Ahnung wie es bei anderen wirkt.
Man muss sich damit heute nicht mehr quälen, die meisten Tools bieten automatische Formatierung an.

Ich habe ein Stück aus Deinem Code genommen, um die Auswirkungen der Umformung zu zeigen. Basis ist eine Tabelle mit ca 600T Datensätzen, die hier gerade rumfliegt. Bei Dir ist es wahrscheinlich viel mehr. Ändert nichts am Prinzip.
Die Statements sind chronologisch und daher etwas redundant, das bietet m.E. ein besseres Verständnis des Ablaufs. Dabei erzeuge ich zwischendurch einen Index auf der Spalte, die in der Where Condition verwendet wird. Das Ganze ist exemplarisch für die Umstellung Deines gesamten Statements anhand eines WHERE Kriteriums.
Um das gleich zu sagen, die folgende Umstellung kann man immer verwenden, aber sie greift in dieser Form nur, wenn die Spalte einen Index hat. Sie ist also immer “unschädlich”, bei späterer Erzeugung eines Index ist dann nichts weiter an Umstellung notwendig. Man hat es einfach gleich richtig gemacht.
Die Schlussfolgerung daraus sollte nicht sein, für alle diese Fälle einen Index auf diverse Spalten zu legen.

Es macht dagegen in diesem Fall Sinn, die Spalten, die sowieso bereits indiziert sind, in den Where Kriterien mit einer entsprechenden Umformung zu beglücken.
Dabei geht es am Ende auch um die Mengengerüste in den verschiedenen Tabellen, primär die Join Kriterien (dann erst die Where Kriterien). (Siehe unten)

explain analyze
select *,  -- unwichtig
       -- schau Dir die Vergleichskriterien an
       ts1, -- hier nochmal explizit der "Originalwert"
       date_trunc('DAY', ts1 ::DATE)                                              as date_trunc_d1_day,
       date_trunc('week', now() ::timestamp(0))                                   as date_trunc_week_now,
      (current_date + 7 - (extract(dow from current_date) || ' days') ::interval) as current_plus_dow,
       current_date + 7                                                           as current_date_plus7,
      (extract(dow from current_date) || ' days') ::interval                      as extract_dow_interval
  from flight 
 where -- ursprüngliche Abfrage mit einer typischen Herangehensweise aus dem Business Case "alle Werte von diesem Tag (egal welche Uhrzeit)"
       date_trunc('DAY', ts1 ::DATE) >=  date_trunc('week', now() ::timestamp(0))
   AND date_trunc('DAY', ts1::DATE)  <= (current_date + 7 - (extract(dow from current_date) || ' days') ::interval)

-- Gather  (cost=1000.00..22436.22 rows=3050 width=70) (actual time=5.353..1184.074 rows=624 loops=1)
--   Workers Planned: 2
--   Workers Launched: 2
--   ->  Parallel Seq Scan on flight  (cost=0.00..21131.22 rows=1271 width=70) (actual time=2.891..1142.075 rows=208 loops=3)
--         Filter: ((date_trunc('DAY'::text, ((ts1)::date)::timestamp with time zone) >= date_trunc('week'::text, (now())::timestamp(0) without time zone)) AND (date_trunc('DAY'::text, ((ts1)::date)::timestamp with time zone) <= ((CURRENT_DATE + 7) - (((date_part('dow'::text, (CURRENT_DATE)::timestamp without time zone))::text || ' days'::text))::interval)))
--         Rows Removed by Filter: 203125
-- Planning Time: 1.113 ms
-- Execution Time: 1184.209 ms

1.2 Sekunden …

jetzt mit Index

create index ts1idx on flight (ts1);

explain analyze
select *,  -- unwichtig
       -- schau Dir die Vergleichskriterien an
       ts1, -- hier nochmal explizit der "Originalwert"
       date_trunc('DAY', ts1 ::DATE)                                              as date_trunc_d1_day,
       date_trunc('week', now() ::timestamp(0))                                   as date_trunc_week_now,
      (current_date + 7 - (extract(dow from current_date) || ' days') ::interval) as current_plus_dow,
       current_date + 7                                                           as current_date_plus7,
      (extract(dow from current_date) || ' days') ::interval                      as extract_dow_interval
  from flight 
 where -- ursprüngliche Abfrage mit einer typischen Herangehensweise aus dem Business Case "alle Werte von diesem Tag (egal welche Uhrzeit)"
       date_trunc('DAY', ts1 ::DATE) >=  date_trunc('week', now() ::timestamp(0))
   AND date_trunc('DAY', ts1::DATE)  <= (current_date + 7 - (extract(dow from current_date) || ' days') ::interval)

-- Gather  (cost=1000.00..22436.22 rows=3050 width=70) (actual time=4.954..1205.916 rows=624 loops=1)
--   Workers Planned: 2
--   Workers Launched: 2
--   ->  Parallel Seq Scan on flight  (cost=0.00..21131.22 rows=1271 width=70) (actual time=2.228..1164.395 rows=208 loops=3)
--         Filter: ((date_trunc('DAY'::text, ((ts1)::date)::timestamp with time zone) >= date_trunc('week'::text, (now())::timestamp(0) without time zone)) AND (date_trunc('DAY'::text, ((ts1)::date)::timestamp with time zone) <= ((CURRENT_DATE + 7) - (((date_part('dow'::text, (CURRENT_DATE)::timestamp without time zone))::text || ' days'::text))::interval)))
--         Rows Removed by Filter: 203125
-- Planning Time: 1.222 ms
-- Execution Time: 1206.037 ms

1.2 Sekunden …
kein Unterschied

Index wieder raus:

drop index ts1idx;[/code]
und die Umformung im Where Kriterium einsetzen


[code]explain analyze
select *,  -- unwichtig
       -- schau Dir die Vergleichskriterien an
       ts1, -- hier nochmal explizit der "Originalwert"
       date_trunc('DAY', ts1 ::DATE)                                              as date_trunc_d1_day,
       date_trunc('week', now() ::timestamp(0))                                   as date_trunc_week_now,
      (current_date + 7 - (extract(dow from current_date) || ' days') ::interval) as current_plus_dow,
       current_date + 7                                                           as current_date_plus7,
      (extract(dow from current_date) || ' days') ::interval                      as extract_dow_interval
  from flight
 where -- Umformung auf Nutzung des unverfälschten Originalwert der Datumsspalte
       -- die Nutzung von between mit den Trunc Dates macht gleich anschaulich, dass die Kürzung des original Timestamp auf ein "glattes" Datum gar nicht notwendig ist.
       -- allerdings muss dann in der Umformung der Range von between einen Tag nach hinten ausgedehnt werden!
       ts1 between (date_trunc('week', now() ::timestamp(0)))
               and (current_date + 8 - (extract(dow from current_date) || ' days') ::interval)

-- Gather  (cost=1000.00..18301.15 rows=553 width=70) (actual time=4.429..785.317 rows=624 loops=1)
--   Workers Planned: 2
--   Workers Launched: 2
--   ->  Parallel Seq Scan on flight  (cost=0.00..17245.85 rows=230 width=70) (actual time=3.836..741.054 rows=208 loops=3)
--         Filter: ((ts1 >= date_trunc('week'::text, (now())::timestamp(0) without time zone)) AND (ts1 <= ((CURRENT_DATE + 8) - (((date_part('dow'::text, (CURRENT_DATE)::timestamp without time zone))::text || ' days'::text))::interval)))
--         Rows Removed by Filter: 203125
-- Planning Time: 0.690 ms
-- Execution Time: 785.439 ms

0,8 Sekunden
besser, aber gleiches Verfahren laut Explain und im Prinzip gleich schlecht

Index wieder rein:

create index ts1idx on flight (ts1);

explain analyze
select *,  -- unwichtig
       -- schau Dir die Vergleichskriterien an
       ts1, -- hier nochmal explizit der "Originalwert"
       date_trunc('DAY', ts1 ::DATE)                                              as date_trunc_d1_day,
       date_trunc('week', now() ::timestamp(0))                                   as date_trunc_week_now,
      (current_date + 7 - (extract(dow from current_date) || ' days') ::interval) as current_plus_dow,
       current_date + 7                                                           as current_date_plus7,
      (extract(dow from current_date) || ' days') ::interval                      as extract_dow_interval
  from flight
 where -- Umformung auf Nutzung des unverfälschten Originalwert der Datumsspalte
       -- die Nutzung von between mit den Trunc Dates macht gleich anschaulich, dass die Kürzung des original Timestamp auf ein "glattes" Datum gar nicht notwendig ist.
       -- allerdings muss dann in der Umformung der Range von between einen Tag nach hinten ausgedehnt werden!
       ts1 between (date_trunc('week', now() ::timestamp(0)))
               and (current_date + 8 - (extract(dow from current_date) || ' days') ::interval)

-- Bitmap Heap Scan on flight  (cost=15.19..1799.40 rows=656 width=70) (actual time=0.294..7.566 rows=624 loops=1)
--   Recheck Cond: ((ts1 >= date_trunc('week'::text, (now())::timestamp(0) without time zone)) AND (ts1 <= ((CURRENT_DATE + 8) - (((date_part('dow'::text, (CURRENT_DATE)::timestamp without time zone))::text || ' days'::text))::interval)))
--   Heap Blocks: exact=581
--   ->  Bitmap Index Scan on ts1idx  (cost=0.00..15.02 rows=656 width=0) (actual time=0.173..0.174 rows=624 loops=1)
--         Index Cond: ((ts1 >= date_trunc('week'::text, (now())::timestamp(0) without time zone)) AND (ts1 <= ((CURRENT_DATE + 8) - (((date_part('dow'::text, (CURRENT_DATE)::timestamp without time zone))::text || ' days'::text))::interval)))
-- Planning Time: 0.963 ms
-- Execution Time: 7.684 ms

1,2 Sekunden
gegen
0,007 Sekunden

170 mal schneller!

Jetzt zeigt das explain analyze die Nutzung des Index:
Bitmap Heap Scan on flight … → …
…Bitmap Index Scan on ts1idx

Zu den Mengengerüsten und anderen Effekten bei großen Datenmengen:
Ein Selectstatement schafft es im Idealfall, gigantische Datenmengen durch sinnvolle JOIN Kriterien bzw. JOIN Reihenfolge auf sehr kleine Datenmengen zu reduzieren.
Wenn das gelingt, können die restlichen Kriterien, also die Where Condition(s) mit Filtern und Logik Prüfungen sehr komplex sein, ohne dass es nennenswert Zeit kostet.

In Deinem Statement fallen jedenfalls relativ üppige JOINS auf, die vermutlich keine reinen JOINS sind, sondern auch Filter, die ins WHERE gehören. Das kann ok sein, kann aber auch den Queryoptimizer in die Irre führen (und den Entwickler).
Ein JOIN sollte primär eine gültige/korrekte Vereinigung von Datenmengen sein. Das WHERE filtert auf die benötigten/gewünschten Daten. Dieses Vorgehen hilft auch dabei, ähnliche Abfragen, die der gleichen Logik folgen, aber andere Filterkriterien benötigen, sauber und übersichtlich zu variieren, ggF. auch durch Nutzung eines gemeinsamen Views als Basis (View als reiner Join ohne Filter oder wenig Filter als kleinster gemeinsamer Nenner für viele Abfragevarianten der gleichen Kategorie)

[flight]

Thanks a lot. Let me try & get back with an update.

[flight]

@castorp

Sorry. I haven’t solved this yet. I need help.

[pogomips]

@flight

Sorry. I haven’t solved this yet. I need help.

What Do You expect after such a post?
So let me ask again:
Did you do what You have been told?
Clean the code?
Linebreaks are fixed?
Commented code removed?

Finally, how could anybody help without knowing the current problem?