Thread: BUG #16950: Query Planer make wrong plan with CTE and foreign table
BUG #16950: Query Planer make wrong plan with CTE and foreign table
From
PG Bug reporting form
Date:
The following bug has been logged on the website:
Bug reference: 16950
Logged by: Thomas Steffen
Email address: linreg@gmx.net
PostgreSQL version: 13.2
Operating system: OpenSuse 15.2
Description:
- query with many CTE and use foreign table (tds_fdw) produce different
output. row count = 1 versus row count = 15
- only difference is to "materialized" one CTE block (==> epreise AS
MATERIALIZED )
- FDW give correct output! No Errors and such things!
Error:
==> "epreise Ep" give back 1 Row and the other Tables PPT, einricht,
dbf_import_HARZ4 .... give back 15 Rows
==> So the JOIN has to produce 15 Rows (correct Solution)
==> But without "MATERIALIZED" it give back only 1 Row
Query:
EXPLAIN ANALYZE VERBOSE
WITH KUNDEN AS (
SELECT K.kidnr, E.gruppe AS klasse, E.eidnr AS scnr, E.ende AS edate,
E.datum AS adate, conv_varchar2bit(data_dbf->'LIMIT') AS limit,
conv_varchar2bit(data_dbf->'LIMITW') AS limitw
FROM dbf_import_KUNDEN K INNER JOIN SQL01.kunden_einrichtung E ON
K.kidnr=E.kidnr
where K.kidnr = 70394
)
,dat AS (
SELECT *
FROM dbf_import_plankw
WHERE jakw between 2109 and 2112
AND data_dbf->'TYP'='1' AND pidnr in (select kidnr from KUNDEN)
)
, dat2 AS (
SELECT pidnr
,jahr
,kw
, (each(data_dbf - ARRAY['ANAM'::text, 'ANUM'::text, 'ANVON'::text,
'TYP'::text, 'ANUM'::text, 'BEAM'::text, 'BEUM'::text, 'BEVON'::text,
'PIDNR'::text] )).key AS menu
, (each(data_dbf - ARRAY['ANAM'::text, 'ANUM'::text, 'ANVON'::text,
'TYP'::text, 'ANUM'::text, 'BEAM'::text, 'BEUM'::text, 'BEVON'::text,
'PIDNR'::text] )).value AS bstatus
FROM dat
)
, PPT AS (
SELECT jahr, kw, pidnr, menu, substring(menu,1,1) as
zeichen,substring(menu,3,1)::int as menunr, substring(menu,2,1)::int AS WT
, to_date(JAHR::TEXT || ' ' || kw::TEXT,'IY IW') + (substring(menu,2,1) || '
Day')::INTERVAL - '1 DAY'::INTERVAL AS Tag
, fn_castasnumeric(bstatus)::int as bstatus
FROM dat2 WHERE (bstatus = '2' OR bstatus='3' OR bstatus='4')
)
, einricht AS (
SELECT idnr, data_dbf->'FHTYP' AS fhtyp, data_dbf->'MDRUCK' AS mdruck,
data_dbf->'LIMIT' AS limit, data_dbf->'P1' AS p1, data_dbf->'P2' AS p2,
data_dbf->'BL' AS BL FROM dbf_import_einricht -- LIMIT 1
)
, epreise AS MATERIALIZED (
SELECT * FROM sql01.epreise WHERE scnr = 68
)
SELECT k.scnr
, k.scnr as EinrNr
,case when h.idnr IS NOT NULL AND nh.idnr IS NULL THEN 1 ELSE 0 END AS
IstH4
,CASE WHEN F.ID IS NOT NULL AND E.fhtyp='0' THEN 1 ELSE 0 END
, 0 AS IstFrei
,pp.pidnr,pp.jahr,pp.kw,pp.menu,pp.zeichen, pp.menunr
, case when pp.bstatus=3 then 2 else pp.bstatus end as bstatus
,pp.WT,pp.Tag
,E.mdruck
FROM PPT AS PP
INNER JOIN KUNDEN K ON k.kidnr=PP.pidnr AND PP.Tag between K.adate and
K.edate
INNER JOIN einricht E ON E.idnr=K.scnr
JOIN epreise Ep on k.scnr = ep.scnr::INT and pp.Tag between Ep.von::DATE and
coalesce(ep.bis,'9998-12-30'::date)
LEFT JOIN dbf_import_HARZ4 H on H.idnr=PP.pidnr AND pp.Tag between
(h.data_dbf->'VON')::DATE AND (h.data_dbf->'BIS')::DATE
LEFT JOIN dbf_import_H4AUS NH on Nh.idnr=H.idnr AND pp.Tag between
(nh.data_dbf->'VON')::DATE AND (nh.data_dbf->'BIS')::DATE
LEFT JOIN SQL01.pro_feiertage F ON F.Bundesland=e.bl AND F.Kategorie<>'BT'
AND DATUM=pp.TAG
ORDER BY k.scnr, k.klasse, k.kidnr
Materialized Query Plan:
Sort (cost=245061.58..245061.59 rows=1 width=172) (actual
time=218.379..218.389 rows=15 loops=1) Output: k.scnr, k.scnr, (CASE WHEN
((h.idnr IS NOT NULL) AND (nh.idnr IS NULL)) THEN 1 ELSE 0 END), (CASE WHEN
((f.id IS NOT NULL) AND ((dbf_import_einricht.data_dbf -> 'FHTYP'::text) =
'0'::text)) THEN 1 ELSE 0 END), 0, pp.pidnr, pp.jahr, pp.kw, pp.menu,
pp.zeichen, pp.menunr, (CASE WHEN (pp.bstatus = 3) THEN 2 ELSE pp.bstatus
END), pp.wt, pp.tag, ((dbf_import_einricht.data_dbf -> 'MDRUCK'::text)),
k.klasse, k.kidnr
Sort Key: k.scnr, k.klasse, pp.pidnr
Sort Method: quicksort Memory: 27kB
CTE kunden
-> Nested Loop (cost=200.29..608.56 rows=5 width=54) (actual
time=46.653..46.670 rows=5 loops=1)
Output: k_1.kidnr, e.gruppe, e.eidnr, e.ende, e.datum,
(((COALESCE(NULLIF((k_1.data_dbf -> 'LIMIT'::text), ''::text),
'0'::text))::boolean)::integer)::bit(1), (((COALESCE(NULLIF((k_1.data_dbf ->
'LIMITW'::text), ''::text), '0'::text))::boolean)::integer)::bit(1)
-> Index Scan using idx_kidnr_dbf_import_kunden on
public.dbf_import_kunden k_1 (cost=0.29..8.31 rows=1 width=644) (actual
time=46.269..46.272 rows=1 loops=1)
Output: k_1.kidnr, k_1.data_dbf
Index Cond: (k_1.kidnr = 70394)
-> Foreign Scan on sql01.kunden_einrichtung e
(cost=200.00..600.05 rows=5 width=100) (actual time=0.365..0.375 rows=5
loops=1)
Output: e.id, e.kidnr, e.eidnr, e.gruppe, e.datum, e.ende,
e.erstelltvon, e.erstelltam, e.bearbeitetvon, e.bearbeitetam
CTE ppt
-> Subquery Scan on dat2 (cost=766.98..38282.09 rows=15 width=96)
(actual time=106.098..214.624 rows=15 loops=1)
Output: dat2.jahr, dat2.kw, dat2.pidnr, dat2.menu,
"substring"(dat2.menu, 1, 1), ("substring"(dat2.menu, 3, 1))::integer,
("substring"(dat2.menu, 2, 1))::integer, ((to_date((((dat2.jahr)::text || '
'::text) || (dat2.kw)::text), 'IY IW'::text) + (("substring"(dat2.menu, 2,
1) || ' Day'::text))::interval) - '1 day'::interval),
((COALESCE(NULLIF(btrim(replace(replace(dat2.bstatus, ','::text, '.'::text),
':'::text, '.'::text)), ''::text), '0'::text))::numeric)::integer
Filter: ((dat2.bstatus = '2'::text) OR (dat2.bstatus = '3'::text)
OR (dat2.bstatus = '4'::text))
Rows Removed by Filter: 615
-> Result (cost=766.98..38263.58 rows=1000 width=76) (actual
time=105.968..214.424 rows=630 loops=1)
Output: dbf_import_plankw.pidnr, dbf_import_plankw.jahr,
dbf_import_plankw.kw, ((each((dbf_import_plankw.data_dbf -
'{ANAM,ANUM,ANVON,TYP,ANUM,BEAM,BEUM,BEVON,PIDNR}'::text[])))).key,
((each((dbf_import_plankw.data_dbf -
'{ANAM,ANUM,ANVON,TYP,ANUM,BEAM,BEUM,BEVON,PIDNR}'::text[])))).value
-> ProjectSet (cost=766.98..38243.58 rows=1000 width=44)
(actual time=105.965..214.354 rows=630 loops=1)
Output: each((dbf_import_plankw.data_dbf -
'{ANAM,ANUM,ANVON,TYP,ANUM,BEAM,BEUM,BEVON,PIDNR}'::text[])),
dbf_import_plankw.pidnr, dbf_import_plankw.jahr, dbf_import_plankw.kw
-> Hash Semi Join (cost=766.98..38238.57 rows=1
width=30) (actual time=105.935..214.192 rows=3 loops=1)
Output: dbf_import_plankw.data_dbf,
dbf_import_plankw.pidnr, dbf_import_plankw.jahr, dbf_import_plankw.kw
Hash Cond: (dbf_import_plankw.pidnr =
kunden.kidnr)
-> Bitmap Heap Scan on public.dbf_import_plankw
(cost=766.82..38237.66 rows=285 width=30) (actual time=1.171..164.157
rows=39102 loops=1)
Output: dbf_import_plankw.data_id,
dbf_import_plankw.dbf_file_id, dbf_import_plankw.parent_file_id,
dbf_import_plankw.hash, dbf_import_plankw.chksum, dbf_import_plankw.pidnr,
dbf_import_plankw.data_dbf, dbf_import_plankw.jahr, dbf_import_plankw.kw,
dbf_import_plankw.jakw, dbf_import_plankw."LOCK_TS",
dbf_import_plankw."LOCK_VON", dbf_import_plankw.typ
Recheck Cond: ((dbf_import_plankw.jakw >=
2109) AND (dbf_import_plankw.jakw <= 2112))
Filter: ((dbf_import_plankw.data_dbf ->
'TYP'::text) = '1'::text)
Rows Removed by Filter: 2865
Heap Blocks: exact=888
-> Bitmap Index Scan on
dbf_import_plankw_jakw (cost=0.00..766.75 rows=57032 width=0) (actual
time=0.971..0.971 rows=42946 loops=1)
Index Cond: ((dbf_import_plankw.jakw
>= 2109) AND (dbf_import_plankw.jakw <= 2112))
-> Hash (cost=0.10..0.10 rows=5 width=4)
(actual time=46.678..46.679 rows=5 loops=1)
Output: kunden.kidnr
Buckets: 1024 Batches: 1 Memory Usage:
9kB
-> CTE Scan on kunden (cost=0.00..0.10
rows=5 width=4) (actual time=46.656..46.674 rows=5 loops=1)
Output: kunden.kidnr
CTE epreise
-> Foreign Scan on sql01.epreise (cost=200.00..700.06 rows=6
width=100) (actual time=0.601..0.638 rows=6 loops=1)
Output: epreise.id, epreise.scnr, epreise.anam, epreise.anum,
epreise.anvon, epreise.beam, epreise.beum, epreise.bevon, epreise.von,
epreise.bis, epreise.p1, epreise.p2, epreise.p3, epreise.p4, epreise.p5,
epreise.p6, epreise.p7, epreise.p8, epreise.p9, epreise.p0,
epreise.bemerkung, epreise.freigabe, epreise.upd_am
-> Nested Loop Left Join (cost=201.51..205470.86 rows=1 width=172)
(actual time=218.217..218.340 rows=15 loops=1)
Output: k.scnr, k.scnr, CASE WHEN ((h.idnr IS NOT NULL) AND (nh.idnr
IS NULL)) THEN 1 ELSE 0 END, CASE WHEN ((f.id IS NOT NULL) AND
((dbf_import_einricht.data_dbf -> 'FHTYP'::text) = '0'::text)) THEN 1 ELSE 0
END, 0, pp.pidnr, pp.jahr, pp.kw, pp.menu, pp.zeichen, pp.menunr, CASE WHEN
(pp.bstatus = 3) THEN 2 ELSE pp.bstatus END, pp.wt, pp.tag,
(dbf_import_einricht.data_dbf -> 'MDRUCK'::text), k.klasse, k.kidnr
Join Filter: (((f.datum)::timestamp without time zone = pp.tag) AND
((f.bundesland)::text = (dbf_import_einricht.data_dbf -> 'BL'::text)))
Rows Removed by Join Filter: 2053
-> Nested Loop Left Join (cost=1.51..14.40 rows=1 width=797)
(actual time=215.403..215.520 rows=15 loops=1)
Output: pp.pidnr, pp.jahr, pp.kw, pp.menu, pp.zeichen,
pp.menunr, pp.bstatus, pp.wt, pp.tag, k.scnr, k.klasse, k.kidnr,
dbf_import_einricht.data_dbf, h.idnr, nh.idnr
Join Filter: ((pp.tag >= ((nh.data_dbf -> 'VON'::text))::date)
AND (pp.tag <= ((nh.data_dbf -> 'BIS'::text))::date))
Rows Removed by Join Filter: 30
-> Nested Loop Left Join (cost=1.23..13.89 rows=1 width=793)
(actual time=215.389..215.465 rows=15 loops=1)
Output: pp.pidnr, pp.jahr, pp.kw, pp.menu, pp.zeichen,
pp.menunr, pp.bstatus, pp.wt, pp.tag, k.scnr, k.klasse, k.kidnr,
dbf_import_einricht.data_dbf, h.idnr
-> Nested Loop (cost=0.95..6.36 rows=1 width=789)
(actual time=215.366..215.411 rows=15 loops=1)
Output: pp.pidnr, pp.jahr, pp.kw, pp.menu,
pp.zeichen, pp.menunr, pp.bstatus, pp.wt, pp.tag, k.scnr, k.klasse, k.kidnr,
dbf_import_einricht.data_dbf
Join Filter: (k.scnr = dbf_import_einricht.idnr)
-> Merge Join (cost=0.80..0.85 rows=1 width=128)
(actual time=215.356..215.380 rows=15 loops=1)
Output: pp.pidnr, pp.jahr, pp.kw, pp.menu,
pp.zeichen, pp.menunr, pp.bstatus, pp.wt, pp.tag, k.scnr, k.klasse, k.kidnr,
ep.scnr
Merge Cond: (k.scnr = ep.scnr)
Join Filter: ((pp.tag >= ep.von) AND (pp.tag
<= COALESCE(ep.bis, '9998-12-30'::date)))
Rows Removed by Join Filter: 75
-> Sort (cost=0.60..0.61 rows=1 width=124)
(actual time=214.691..214.694 rows=15 loops=1)
Output: pp.pidnr, pp.jahr, pp.kw,
pp.menu, pp.zeichen, pp.menunr, pp.bstatus, pp.wt, pp.tag, k.scnr, k.klasse,
k.kidnr
Sort Key: k.scnr
Sort Method: quicksort Memory: 27kB
-> Hash Join (cost=0.16..0.59 rows=1
width=124) (actual time=106.122..214.672 rows=15 loops=1)
Output: pp.pidnr, pp.jahr,
pp.kw, pp.menu, pp.zeichen, pp.menunr, pp.bstatus, pp.wt, pp.tag, k.scnr,
k.klasse, k.kidnr
Hash Cond: (pp.pidnr =
k.kidnr)
Join Filter: ((pp.tag >=
k.adate) AND (pp.tag <= k.edate))
Rows Removed by Join Filter:
60
-> CTE Scan on ppt pp
(cost=0.00..0.30 rows=15 width=96) (actual time=106.102..214.635 rows=15
loops=1)
Output: pp.jahr, pp.kw,
pp.pidnr, pp.menu, pp.zeichen, pp.menunr, pp.wt, pp.tag, pp.bstatus
-> Hash (cost=0.10..0.10
rows=5 width=36) (actual time=0.007..0.008 rows=5 loops=1)
Output: k.scnr, k.klasse,
k.kidnr, k.adate, k.edate
Buckets: 1024 Batches: 1
Memory Usage: 9kB
-> CTE Scan on kunden k
(cost=0.00..0.10 rows=5 width=36) (actual time=0.003..0.004 rows=5
loops=1)
Output: k.scnr,
k.klasse, k.kidnr, k.adate, k.edate
-> Sort (cost=0.20..0.21 rows=6 width=12)
(actual time=0.653..0.658 rows=76 loops=1)
Output: ep.scnr, ep.von, ep.bis
Sort Key: ep.scnr
Sort Method: quicksort Memory: 25kB
-> CTE Scan on epreise ep
(cost=0.00..0.12 rows=6 width=12) (actual time=0.606..0.645 rows=6
loops=1)
Output: ep.scnr, ep.von,
ep.bis
-> Index Scan using idx_unique_einricht on
public.dbf_import_einricht (cost=0.15..5.50 rows=1 width=669) (actual
time=0.001..0.001 rows=1 loops=15)
Output: dbf_import_einricht.data_dbf,
dbf_import_einricht.idnr
Index Cond: (dbf_import_einricht.idnr =
ep.scnr)
-> Index Scan using idx_unique_harz4_idnr on
public.dbf_import_harz4 h (cost=0.28..7.52 rows=1 width=231) (actual
time=0.003..0.003 rows=1 loops=15)
Output: h.data_id, h.dbf_file_id,
h.parent_file_id, h.hash, h.chksum, h.idnr, h.data_dbf
Index Cond: (h.idnr = pp.pidnr)
Filter: ((pp.tag >= ((h.data_dbf ->
'VON'::text))::date) AND (pp.tag <= ((h.data_dbf -> 'BIS'::text))::date))
-> Index Scan using idx_unique_h4aus_idnr_von on
public.dbf_import_h4aus nh (cost=0.28..0.44 rows=2 width=176) (actual
time=0.001..0.002 rows=2 loops=15)
Output: nh.data_id, nh.dbf_file_id, nh.parent_file_id,
nh.hash, nh.chksum, nh.idnr, nh.von, nh.bis, nh.data_dbf
Index Cond: (nh.idnr = h.idnr)
-> Foreign Scan on sql01.pro_feiertage f (cost=200.00..205420.53
rows=2053 width=100) (actual time=0.018..0.178 rows=137 loops=15)
Output: f.id, f.datum, f.wt, f.kk, f.lge, f.bundesland,
f.kategorie, f.bezeichnung, f."user", f.hist_ts
Planning Time: 19.317 ms
JIT:
Functions: 78
Options: Inlining false, Optimization false, Expressions true, Deforming
true
Timing: Generation 6.921 ms, Inlining 0.000 ms, Optimization 2.484 ms,
Emission 42.939 ms, Total 52.344 ms
Execution Time: 264.024 ms
Without Materialized Query Plan:
Sort (cost=245063.98..245063.99 rows=1 width=172) (actual
time=205.930..205.939 rows=1 loops=1) Output: k.scnr, k.scnr, (CASE WHEN
((h.idnr IS NOT NULL) AND (nh.idnr IS NULL)) THEN 1 ELSE 0 END), (CASE WHEN
((f.id IS NOT NULL) AND ((dbf_import_einricht.data_dbf -> 'FHTYP'::text) =
'0'::text)) THEN 1 ELSE 0 END), 0, pp.pidnr, pp.jahr, pp.kw, pp.menu,
pp.zeichen, pp.menunr, (CASE WHEN (pp.bstatus = 3) THEN 2 ELSE pp.bstatus
END), pp.wt, pp.tag, ((dbf_import_einricht.data_dbf -> 'MDRUCK'::text)),
k.klasse, k.kidnr
Sort Key: k.klasse, pp.pidnr
Sort Method: quicksort Memory: 25kB
CTE kunden
-> Nested Loop (cost=200.29..608.56 rows=5 width=54) (actual
time=40.081..40.099 rows=5 loops=1)
Output: k_1.kidnr, e.gruppe, e.eidnr, e.ende, e.datum,
(((COALESCE(NULLIF((k_1.data_dbf -> 'LIMIT'::text), ''::text),
'0'::text))::boolean)::integer)::bit(1), (((COALESCE(NULLIF((k_1.data_dbf ->
'LIMITW'::text), ''::text), '0'::text))::boolean)::integer)::bit(1)
-> Index Scan using idx_kidnr_dbf_import_kunden on
public.dbf_import_kunden k_1 (cost=0.29..8.31 rows=1 width=644) (actual
time=39.714..39.718 rows=1 loops=1)
Output: k_1.kidnr, k_1.data_dbf
Index Cond: (k_1.kidnr = 70394)
-> Foreign Scan on sql01.kunden_einrichtung e
(cost=200.00..600.05 rows=5 width=100) (actual time=0.347..0.359 rows=5
loops=1)
Output: e.id, e.kidnr, e.eidnr, e.gruppe, e.datum, e.ende,
e.erstelltvon, e.erstelltam, e.bearbeitetvon, e.bearbeitetam
CTE ppt
-> Subquery Scan on dat2 (cost=766.98..38282.09 rows=15 width=96)
(actual time=97.395..201.822 rows=15 loops=1)
Output: dat2.jahr, dat2.kw, dat2.pidnr, dat2.menu,
"substring"(dat2.menu, 1, 1), ("substring"(dat2.menu, 3, 1))::integer,
("substring"(dat2.menu, 2, 1))::integer, ((to_date((((dat2.jahr)::text || '
'::text) || (dat2.kw)::text), 'IY IW'::text) + (("substring"(dat2.menu, 2,
1) || ' Day'::text))::interval) - '1 day'::interval),
((COALESCE(NULLIF(btrim(replace(replace(dat2.bstatus, ','::text, '.'::text),
':'::text, '.'::text)), ''::text), '0'::text))::numeric)::integer
Filter: ((dat2.bstatus = '2'::text) OR (dat2.bstatus = '3'::text)
OR (dat2.bstatus = '4'::text))
Rows Removed by Filter: 615
-> Result (cost=766.98..38263.58 rows=1000 width=76) (actual
time=97.267..201.619 rows=630 loops=1)
Output: dbf_import_plankw.pidnr, dbf_import_plankw.jahr,
dbf_import_plankw.kw, ((each((dbf_import_plankw.data_dbf -
'{ANAM,ANUM,ANVON,TYP,ANUM,BEAM,BEUM,BEVON,PIDNR}'::text[])))).key,
((each((dbf_import_plankw.data_dbf -
'{ANAM,ANUM,ANVON,TYP,ANUM,BEAM,BEUM,BEVON,PIDNR}'::text[])))).value
-> ProjectSet (cost=766.98..38243.58 rows=1000 width=44)
(actual time=97.265..201.551 rows=630 loops=1)
Output: each((dbf_import_plankw.data_dbf -
'{ANAM,ANUM,ANVON,TYP,ANUM,BEAM,BEUM,BEVON,PIDNR}'::text[])),
dbf_import_plankw.pidnr, dbf_import_plankw.jahr, dbf_import_plankw.kw
-> Hash Semi Join (cost=766.98..38238.57 rows=1
width=30) (actual time=97.234..201.383 rows=3 loops=1)
Output: dbf_import_plankw.data_dbf,
dbf_import_plankw.pidnr, dbf_import_plankw.jahr, dbf_import_plankw.kw
Hash Cond: (dbf_import_plankw.pidnr =
kunden.kidnr)
-> Bitmap Heap Scan on public.dbf_import_plankw
(cost=766.82..38237.66 rows=285 width=30) (actual time=1.099..157.844
rows=39102 loops=1)
Output: dbf_import_plankw.data_id,
dbf_import_plankw.dbf_file_id, dbf_import_plankw.parent_file_id,
dbf_import_plankw.hash, dbf_import_plankw.chksum, dbf_import_plankw.pidnr,
dbf_import_plankw.data_dbf, dbf_import_plankw.jahr, dbf_import_plankw.kw,
dbf_import_plankw.jakw, dbf_import_plankw."LOCK_TS",
dbf_import_plankw."LOCK_VON", dbf_import_plankw.typ
Recheck Cond: ((dbf_import_plankw.jakw >=
2109) AND (dbf_import_plankw.jakw <= 2112))
Filter: ((dbf_import_plankw.data_dbf ->
'TYP'::text) = '1'::text)
Rows Removed by Filter: 2865
Heap Blocks: exact=888
-> Bitmap Index Scan on
dbf_import_plankw_jakw (cost=0.00..766.75 rows=57032 width=0) (actual
time=0.911..0.911 rows=42946 loops=1)
Index Cond: ((dbf_import_plankw.jakw
>= 2109) AND (dbf_import_plankw.jakw <= 2112))
-> Hash (cost=0.10..0.10 rows=5 width=4)
(actual time=40.106..40.107 rows=5 loops=1)
Output: kunden.kidnr
Buckets: 1024 Batches: 1 Memory Usage:
9kB
-> CTE Scan on kunden (cost=0.00..0.10
rows=5 width=4) (actual time=40.084..40.102 rows=5 loops=1)
Output: kunden.kidnr
-> Nested Loop Left Join (cost=400.84..206173.32 rows=1 width=172)
(actual time=101.331..205.887 rows=1 loops=1)
Output: k.scnr, k.scnr, CASE WHEN ((h.idnr IS NOT NULL) AND (nh.idnr
IS NULL)) THEN 1 ELSE 0 END, CASE WHEN ((f.id IS NOT NULL) AND
((dbf_import_einricht.data_dbf -> 'FHTYP'::text) = '0'::text)) THEN 1 ELSE 0
END, 0, pp.pidnr, pp.jahr, pp.kw, pp.menu, pp.zeichen, pp.menunr, CASE WHEN
(pp.bstatus = 3) THEN 2 ELSE pp.bstatus END, pp.wt, pp.tag,
(dbf_import_einricht.data_dbf -> 'MDRUCK'::text), k.klasse, k.kidnr
Join Filter: (((f.datum)::timestamp without time zone = pp.tag) AND
((f.bundesland)::text = (dbf_import_einricht.data_dbf -> 'BL'::text)))
Rows Removed by Join Filter: 2053
-> Nested Loop Left Join (cost=200.84..716.85 rows=1 width=797)
(actual time=97.963..202.519 rows=1 loops=1)
Output: pp.pidnr, pp.jahr, pp.kw, pp.menu, pp.zeichen,
pp.menunr, pp.bstatus, pp.wt, pp.tag, k.scnr, k.klasse, k.kidnr,
dbf_import_einricht.data_dbf, h.idnr, nh.idnr
Join Filter: ((pp.tag >= ((nh.data_dbf -> 'VON'::text))::date)
AND (pp.tag <= ((nh.data_dbf -> 'BIS'::text))::date))
Rows Removed by Join Filter: 2
-> Nested Loop (cost=200.56..716.35 rows=1 width=793)
(actual time=97.943..202.497 rows=1 loops=1)
Output: pp.pidnr, pp.jahr, pp.kw, pp.menu, pp.zeichen,
pp.menunr, pp.bstatus, pp.wt, pp.tag, k.scnr, k.klasse, k.kidnr,
dbf_import_einricht.data_dbf, h.idnr
Join Filter: ((pp.tag >= epreise.von) AND (pp.tag <=
COALESCE(epreise.bis, '9998-12-30'::date)))
Rows Removed by Join Filter: 5
-> Nested Loop Left Join (cost=0.55..16.20 rows=1
width=793) (actual time=97.442..201.977 rows=15 loops=1)
Output: pp.pidnr, pp.jahr, pp.kw, pp.menu,
pp.zeichen, pp.menunr, pp.bstatus, pp.wt, pp.tag, k.scnr, k.klasse, k.kidnr,
dbf_import_einricht.data_dbf, h.idnr
-> Nested Loop (cost=0.27..8.67 rows=1
width=789) (actual time=97.428..201.913 rows=15 loops=1)
Output: pp.pidnr, pp.jahr, pp.kw, pp.menu,
pp.zeichen, pp.menunr, pp.bstatus, pp.wt, pp.tag, k.scnr, k.klasse, k.kidnr,
dbf_import_einricht.data_dbf
-> Hash Join (cost=0.12..0.50 rows=1
width=124) (actual time=97.418..201.872 rows=15 loops=1)
Output: pp.pidnr, pp.jahr, pp.kw,
pp.menu, pp.zeichen, pp.menunr, pp.bstatus, pp.wt, pp.tag, k.scnr, k.klasse,
k.kidnr
Hash Cond: (pp.pidnr = k.kidnr)
Join Filter: ((pp.tag >= k.adate) AND
(pp.tag <= k.edate))
Rows Removed by Join Filter: 45
-> CTE Scan on ppt pp
(cost=0.00..0.30 rows=15 width=96) (actual time=97.397..201.832 rows=15
loops=1)
Output: pp.jahr, pp.kw,
pp.pidnr, pp.menu, pp.zeichen, pp.menunr, pp.wt, pp.tag, pp.bstatus
-> Hash (cost=0.11..0.11 rows=1
width=36) (actual time=0.008..0.009 rows=4 loops=1)
Output: k.scnr, k.klasse,
k.kidnr, k.adate, k.edate
Buckets: 1024 Batches: 1
Memory Usage: 9kB
-> CTE Scan on kunden k
(cost=0.00..0.11 rows=1 width=36) (actual time=0.004..0.006 rows=4
loops=1)
Output: k.scnr, k.klasse,
k.kidnr, k.adate, k.edate
Filter: (k.scnr = 68)
Rows Removed by Filter:
1
-> Index Scan using idx_unique_einricht on
public.dbf_import_einricht (cost=0.15..8.17 rows=1 width=669) (actual
time=0.002..0.002 rows=1 loops=15)
Output: dbf_import_einricht.data_dbf,
dbf_import_einricht.idnr
Index Cond: (dbf_import_einricht.idnr
= 68)
-> Index Scan using idx_unique_harz4_idnr on
public.dbf_import_harz4 h (cost=0.28..7.52 rows=1 width=231) (actual
time=0.003..0.003 rows=1 loops=15)
Output: h.data_id, h.dbf_file_id,
h.parent_file_id, h.hash, h.chksum, h.idnr, h.data_dbf
Index Cond: (h.idnr = pp.pidnr)
Filter: ((pp.tag >= ((h.data_dbf ->
'VON'::text))::date) AND (pp.tag <= ((h.data_dbf -> 'BIS'::text))::date))
-> Foreign Scan on sql01.epreise (cost=200.00..700.06
rows=6 width=100) (actual time=0.033..0.034 rows=0 loops=15)
Output: epreise.id, epreise.scnr, epreise.anam,
epreise.anum, epreise.anvon, epreise.beam, epreise.beum, epreise.bevon,
epreise.von, epreise.bis, epreise.p1, epreise.p2, epreise.p3, epreise.p4,
epreise.p5, epreise.p6, epreise.p7, epreise.p8, epreise.p9, epreise.p0,
epreise.bemerkung, epreise.freigabe, epreise.upd_am
-> Index Scan using idx_unique_h4aus_idnr_von on
public.dbf_import_h4aus nh (cost=0.28..0.44 rows=2 width=176) (actual
time=0.009..0.011 rows=2 loops=1)
Output: nh.data_id, nh.dbf_file_id, nh.parent_file_id,
nh.hash, nh.chksum, nh.idnr, nh.von, nh.bis, nh.data_dbf
Index Cond: (nh.idnr = h.idnr)
-> Foreign Scan on sql01.pro_feiertage f (cost=200.00..205420.53
rows=2053 width=100) (actual time=0.454..3.194 rows=2053 loops=1)
Output: f.id, f.datum, f.wt, f.kk, f.lge, f.bundesland,
f.kategorie, f.bezeichnung, f."user", f.hist_ts
Planning Time: 18.940 ms
JIT:
Functions: 68
Options: Inlining false, Optimization false, Expressions true, Deforming
true
Timing: Generation 6.030 ms, Inlining 0.000 ms, Optimization 2.210 ms,
Emission 36.777 ms, Total 45.017 ms
Execution Time: 249.659 ms