API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jul 23rd, 2017
59
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 14.65 KB | None | 0 0
raw download clone embed print report
  1. EXPLAIN ANALYZE
  2. SELECT array_agg(DISTINCT t.konto_bezeichnung), array_agg(DISTINCT t.gesellschaft_kuerzel), array_agg(DISTINCT t.gesellschaft_name), array_agg(DISTINCT t.produktgeber), array_agg(DISTINCT t.produkt_name), array_agg(DISTINCT t.produkt_art), array_agg(DISTINCT t.produkt_art_kombiniert), array_agg(DISTINCT t.isin), array_agg(DISTINCT t.wkn) FROM (
  3. -- table statement start
  4. SELECT
  5. kb.konto_bezeichnung AS "konto_bezeichnung",
  6. kb.gesellschaft_kuerzel AS "gesellschaft_kuerzel",
  7. kb.gesellschaft_name AS "gesellschaft_name",
  8. COALESCE(pw.produktgeber, $$Unbekannt$$) AS "produktgeber",
  9. COALESCE(pk.name, pw.name) AS "produkt_name",
  10.  
  11. initcap(COALESCE(
  12. CASE
  13. WHEN $$fonds$$ = pw.gattung THEN $$investmentfonds$$
  14. ELSE pw.gattung
  15. END,
  16. pk.typ))
  17. AS "produkt_art",
  18.  
  19. initcap(COALESCE(CASE
  20. WHEN $$fonds$$ = pw.gattung
  21. THEN $$investmentfonds$$
  22. ELSE pw.gattung
  23. END, pk.typ)) ||
  24. $$<br />$$ ||
  25. CASE
  26. WHEN COALESCE(pk.typ, pw.gattung) = $$fonds$$
  27. THEN COALESCE(fm.fondskategorie, $$Unbekannt$$)
  28. ELSE $$$$
  29. END
  30. AS "produkt_art_kombiniert",
  31. pw.isin::varchar AS "isin",
  32. pw.wkn::varchar AS "wkn"
  33. FROM pms3guv.portfolio_live_cache c
  34. INNER JOIN vw_bestand_wert b ON b.bestand_typ = c.bestand_typ::bestand_typ
  35. AND b.datum BETWEEN c.datum_von AND c.datum_bis
  36. AND b.konto_id = ANY((
  37. SELECT array_agg(konto_id)
  38. FROM pms3guv.portfolio_live_cache_kb kb
  39. WHERE kb.cache_key = c.cache_key)::INTEGER[])
  40. INNER JOIN pms3guv.portfolio_live_cache_kb kb ON kb.cache_key = c.cache_key AND kb.konto_id = b.konto_id
  41. LEFT JOIN ng.produkt_konto pk ON b.produkt_id = pk.produkt_id
  42. LEFT JOIN ng.produkt_wertpapier pw ON pk.produkt_id IS NULL AND b.produkt_id = pw.produkt_id
  43. LEFT JOIN reuters_lipper.fw_main fm ON pw.isin = fm.isin
  44. WHERE
  45. (c.cache_key) = ('c7f5679f7c088ff133a83b6f1039a6a3220207f2')
  46.  
  47. ORDER BY konto_bezeichnung ASC, gesellschaft_kuerzel ASC, gesellschaft_name ASC, produktgeber ASC, produkt_name ASC, produkt_art ASC, produkt_art_kombiniert ASC, isin ASC, wkn ASC
  48. -- table statement end
  49. ) t;
  50.  
  51.  
  52. DB01:
  53. Aggregate (cost=10745.15..10745.16 rows=1 width=1642) (actual time=1005.438..1005.439 rows=1 loops=1)
  54. -> Sort (cost=10744.78..10744.80 rows=10 width=154) (actual time=696.435..698.167 rows=16404 loops=1)
  55. Sort Key: kb.konto_bezeichnung, kb.gesellschaft_kuerzel, kb.gesellschaft_name, (COALESCE(pw.produktgeber, 'Unbekannt'::character varying)), (COALESCE(pk.name, pw.name)), (initcap((COALESCE(CASE WHEN ('fonds'::text = (pw.gattung)::text) THEN 'investmentfonds'::character varying ELSE (pw.gattung)::character varying END, pk.typ))::text)), (((initcap((COALESCE(CASE WHEN ('fonds'::text = (pw.gattung)::text) THEN 'investmentfonds'::character varying ELSE (pw.gattung)::character varying END, pk.typ))::text) || '<br />'::text) || (CASE WHEN ((COALESCE(pk.typ, (pw.gattung)::character varying))::text = 'fonds'::text) THEN COALESCE(fm.fondskategorie, 'Unbekannt'::character varying) ELSE ''::character varying END)::text)), ((pw.isin)::character varying), ((pw.wkn)::character varying)
  56. Sort Method: quicksort Memory: 4366kB
  57. -> Hash Left Join (cost=10353.66..10744.61 rows=10 width=154) (actual time=62.572..217.988 rows=16404 loops=1)
  58. Hash Cond: ((pw.isin)::bpchar = (fm.isin)::bpchar)
  59. -> Nested Loop Left Join (cost=32.51..423.05 rows=10 width=143) (actual time=0.309..105.396 rows=16404 loops=1)
  60. Join Filter: (pk.produkt_id IS NULL)
  61. -> Hash Left Join (cost=32.51..379.99 rows=10 width=62) (actual time=0.296..51.269 rows=16404 loops=1)
  62. Hash Cond: (pp.produkt_id = pk.produkt_id)
  63. -> Nested Loop (cost=31.19..378.62 rows=10 width=38) (actual time=0.278..46.217 rows=16404 loops=1)
  64. -> Nested Loop (cost=31.19..250.73 rows=1 width=54) (actual time=0.250..3.102 rows=529 loops=1)
  65. -> Nested Loop (cost=31.19..247.10 rows=1 width=62) (actual time=0.239..1.430 rows=529 loops=1)
  66. Join Filter: ((c.bestand_typ)::bestand_typ = b.typ)
  67. -> Nested Loop (cost=0.00..11.66 rows=6 width=97) (actual time=0.064..0.130 rows=6 loops=1)
  68. -> Seq Scan on portfolio_live_cache c (cost=0.00..3.84 rows=1 width=59) (actual time=0.014..0.023 rows=1 loops=1)
  69. Filter: ((cache_key)::text = 'c7f5679f7c088ff133a83b6f1039a6a3220207f2'::text)
  70. -> Seq Scan on portfolio_live_cache_kb kb (cost=0.00..7.76 rows=6 width=79) (actual time=0.047..0.102 rows=6 loops=1)
  71. Filter: ((kb.cache_key)::text = 'c7f5679f7c088ff133a83b6f1039a6a3220207f2'::text)
  72. -> Bitmap Heap Scan on bestand b (cost=31.19..33.20 rows=1 width=20) (actual time=0.035..0.077 rows=176 loops=6)
  73. Recheck Cond: ((b.konto_id = ANY ((SubPlan 1))) AND (b.konto_id = kb.konto_id))
  74. -> Bitmap Index Scan on bestand_konto_id (cost=0.00..31.19 rows=1 width=0) (actual time=0.031..0.031 rows=176 loops=6)
  75. Index Cond: ((b.konto_id = ANY ((SubPlan 1))) AND (b.konto_id = kb.konto_id))
  76. SubPlan 1
  77. -> Aggregate (cost=6.00..6.01 rows=1 width=4) (actual time=0.025..0.025 rows=1 loops=6)
  78. -> Bitmap Heap Scan on portfolio_live_cache_kb kb (cost=2.27..5.99 rows=3 width=4) (actual time=0.015..0.016 rows=6 loops=6)
  79. Recheck Cond: ((cache_key)::text = ($0)::text)
  80. -> Bitmap Index Scan on portfolio_live_cache_kb_cache_key_key (cost=0.00..2.27 rows=3 width=0) (actual time=0.013..0.013 rows=6 loops=6)
  81. Index Cond: ((cache_key)::text = ($0)::text)
  82. SubPlan 1
  83. -> Aggregate (cost=6.00..6.01 rows=1 width=4) (actual time=0.025..0.025 rows=1 loops=6)
  84. -> Bitmap Heap Scan on portfolio_live_cache_kb kb (cost=2.27..5.99 rows=3 width=4) (actual time=0.015..0.016 rows=6 loops=6)
  85. Recheck Cond: ((cache_key)::text = ($0)::text)
  86. -> Bitmap Index Scan on portfolio_live_cache_kb_cache_key_key (cost=0.00..2.27 rows=3 width=0) (actual time=0.013..0.013 rows=6 loops=6)
  87. Index Cond: ((cache_key)::text = ($0)::text)
  88. -> Index Scan using uq_konto_acc_kunden_konto_id_acc_kunden_id on konto_acc_kunden kak (cost=0.00..3.61 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=529)
  89. Index Cond: (kak.konto_id = b.konto_id)
  90. Filter: (kak.zugriff_bis IS NULL)
  91. -> Index Scan using produkt_preis_produkt_id_datum on produkt_preis pp (cost=0.00..126.57 rows=59 width=8) (actual time=0.006..0.072 rows=31 loops=529)
  92. Index Cond: ((pp.produkt_id = b.produkt_id) AND (pp.datum >= b.datum_von) AND (pp.datum <= b.datum_bis) AND (pp.datum >= c.datum_von) AND (pp.datum <= c.datum_bis))
  93. -> Hash (cost=1.14..1.14 rows=14 width=24) (actual time=0.010..0.010 rows=14 loops=1)
  94. -> Seq Scan on produkt_konto pk (cost=0.00..1.14 rows=14 width=24) (actual time=0.003..0.006 rows=14 loops=1)
  95. -> Index Scan using produkt_wertpapier_pkey on produkt_wertpapier pw (cost=0.00..4.29 rows=1 width=93) (actual time=0.002..0.003 rows=1 loops=16404)
  96. Index Cond: (pp.produkt_id = pw.produkt_id)
  97. -> Hash (cost=9841.07..9841.07 rows=38407 width=24) (actual time=62.207..62.207 rows=38407 loops=1)
  98. -> Seq Scan on fw_main fm (cost=0.00..9841.07 rows=38407 width=24) (actual time=0.004..50.891 rows=38407 loops=1)
  99. Total runtime: 1005.631 ms
  100.  
  101. dbtest:
  102. Aggregate (cost=6232.54..6232.55 rows=1 width=1642) (actual time=390204.278..390204.278 rows=1 loops=1)
  103. -> Sort (cost=6232.48..6232.48 rows=1 width=154) (actual time=389755.414..389758.128 rows=16404 loops=1)
  104. Sort Key: kb.konto_bezeichnung, kb.gesellschaft_kuerzel, kb.gesellschaft_name, (COALESCE(pw.produktgeber, 'Unbekannt'::character varying)), (COALESCE(pk.name, pw.name)), (initcap((COALESCE(CASE WHEN ('fonds'::text = (pw.gattung)::text) THEN 'investmentfonds'::character varying ELSE (pw.gattung)::character varying END, pk.typ))::text)), (((initcap((COALESCE(CASE WHEN ('fonds'::text = (pw.gattung)::text) THEN 'investmentfonds'::character varying ELSE (pw.gattung)::character varying END, pk.typ))::text) || '<br />'::text) || (CASE WHEN ((COALESCE(pk.typ, (pw.gattung)::character varying))::text = 'fonds'::text) THEN COALESCE(fm.fondskategorie, 'Unbekannt'::character varying) ELSE ''::character varying END)::text)), ((pw.isin)::character varying), ((pw.wkn)::character varying)
  105. Sort Method: quicksort Memory: 4366kB
  106. -> Nested Loop Left Join (cost=47.03..6232.47 rows=1 width=154) (actual time=24.524..388994.274 rows=16404 loops=1)
  107. Join Filter: ((pw.isin)::bpchar = (fm.isin)::bpchar)
  108. -> Nested Loop Left Join (cost=47.03..550.96 rows=1 width=143) (actual time=0.247..876.047 rows=16404 loops=1)
  109. Join Filter: (pk.produkt_id IS NULL)
  110. -> Nested Loop Left Join (cost=47.03..542.67 rows=1 width=62) (actual time=0.237..437.714 rows=16404 loops=1)
  111. Join Filter: (pp.produkt_id = pk.produkt_id)
  112. -> Nested Loop (cost=47.03..541.36 rows=1 width=38) (actual time=0.228..116.829 rows=16404 loops=1)
  113. -> Nested Loop (cost=47.03..329.97 rows=1 width=54) (actual time=0.199..9.231 rows=529 loops=1)
  114. -> Nested Loop (cost=47.03..323.34 rows=1 width=62) (actual time=0.189..3.717 rows=529 loops=1)
  115. Join Filter: ((c.bestand_typ)::bestand_typ = b.typ)
  116. -> Nested Loop (cost=0.00..3.35 rows=6 width=97) (actual time=0.019..0.051 rows=6 loops=1)
  117. -> Seq Scan on portfolio_live_cache c (cost=0.00..1.05 rows=1 width=59) (actual time=0.009..0.012 rows=1 loops=1)
  118. Filter: ((cache_key)::text = 'c7f5679f7c088ff133a83b6f1039a6a3220207f2'::text)
  119. -> Seq Scan on portfolio_live_cache_kb kb (cost=0.00..2.24 rows=6 width=79) (actual time=0.006..0.026 rows=6 loops=1)
  120. Filter: ((kb.cache_key)::text = 'c7f5679f7c088ff133a83b6f1039a6a3220207f2'::text)
  121. -> Bitmap Heap Scan on bestand b (cost=47.03..51.04 rows=1 width=20) (actual time=0.078..0.169 rows=176 loops=6)
  122. Recheck Cond: ((b.konto_id = ANY ((SubPlan 1))) AND (b.konto_id = kb.konto_id))
  123. -> Bitmap Index Scan on bestand_konto_id (cost=0.00..47.03 rows=1 width=0) (actual time=0.069..0.069 rows=176 loops=6)
  124. Index Cond: ((b.konto_id = ANY ((SubPlan 1))) AND (b.konto_id = kb.konto_id))
  125. SubPlan 1
  126. -> Aggregate (cost=2.25..2.26 rows=1 width=4) (actual time=0.034..0.034 rows=1 loops=6)
  127. -> Seq Scan on portfolio_live_cache_kb kb (cost=0.00..2.24 rows=5 width=4) (actual time=0.009..0.013 rows=6 loops=6)
  128. Filter: ((cache_key)::text = ($0)::text)
  129. SubPlan 1
  130. -> Aggregate (cost=2.25..2.26 rows=1 width=4) (actual time=0.034..0.034 rows=1 loops=6)
  131. -> Seq Scan on portfolio_live_cache_kb kb (cost=0.00..2.24 rows=5 width=4) (actual time=0.009..0.013 rows=6 loops=6)
  132. Filter: ((cache_key)::text = ($0)::text)
  133. -> Index Scan using uq_konto_acc_kunden_vorerst_nur_eindeutige_zuordnungen on konto_acc_kunden kak (cost=0.00..6.61 rows=1 width=4) (actual time=0.008..0.009 rows=1 loops=529)
  134. Index Cond: (kak.konto_id = b.konto_id)
  135. Filter: (kak.zugriff_bis IS NULL)
  136. -> Index Scan using produkt_preis_produkt_id_datum on produkt_preis pp (cost=0.00..210.26 rows=50 width=8) (actual time=0.014..0.148 rows=31 loops=529)
  137. Index Cond: ((pp.produkt_id = b.produkt_id) AND (pp.datum >= b.datum_von) AND (pp.datum <= b.datum_bis) AND (pp.datum >= c.datum_von) AND (pp.datum <= c.datum_bis))
  138. -> Seq Scan on produkt_konto pk (cost=0.00..1.14 rows=14 width=24) (actual time=0.004..0.006 rows=14 loops=16404)
  139. -> Index Scan using produkt_wertpapier_pkey on produkt_wertpapier pw (cost=0.00..8.28 rows=1 width=93) (actual time=0.017..0.021 rows=1 loops=16404)
  140. Index Cond: (pp.produkt_id = pw.produkt_id)
  141. -> Seq Scan on fw_main fm (cost=0.00..5201.77 rows=38377 width=24) (actual time=0.002..9.588 rows=38377 loops=16404)
  142. Total runtime: 390206.267 ms
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!