SELECT *, kerekit_penznem (netto_ertek*(afa_szazalek/100), penznem) AS afa_ertek, kerekit_penznem (netto_ertek*(1+afa_szazalek/100), penznem) AS brutto_ertek FROM ( SELECT szamla, kerekit_penznem( elsodl_netto_ertek*COALESCE(deka,1)/COALESCE(dekb,1), penznem) AS netto_ertek, konyvelesi_tetelcsoport, afa, afa_szazalek, penznem FROM (SELECT szt.szamla, kerekit_penznem(sum(szt.netto_egysegar * szt.mennyiseg), penznem) AS elsodl_netto_ertek, konyvelesi_tetelcsoport(szt.szamla, szt.tetelszam) AS konyvelesi_tetelcsoport, szt.afa, afa.ertek AS afa_szazalek, szamla.penznem AS sz_penznem, szamla.teljesites AS sz_teljesites, arf_a.deviza_kozeparfolyam as deka, arf_b.deviza_kozeparfolyam as dekb, foo_valuta AS penznem FROM szamla_tetele szt LEFT JOIN szamla ON (szamla = szamla.az) LEFT JOIN afa ON (afa.az = szt.afa) LEFT JOIN arfolyam arf_a ON (arf_a.ervenyes = (SELECT ervenyes FROM arfolyam WHERE ervenyes<=szamla.teljesites AND valuta = szamla.penznem ORDER BY 1 DESC LIMIT 1) AND szamla.penznem=arf_a.valuta) JOIN (SELECT az AS foo_valuta FROM valuta) AS valuta ON (true) LEFT JOIN arfolyam arf_b ON (arf_b.valuta=foo_valuta AND arf_b.ervenyes = (SELECT ervenyes FROM arfolyam WHERE ervenyes<=szamla.teljesites AND valuta = foo_valuta ORDER BY 1 DESC LIMIT 1) ) WHERE (NOT szt.archiv) AND (foo_valuta = 4 or arf_b.valuta notnull ) GROUP BY szt.szamla, konyvelesi_tetelcsoport, szt.afa, sz_penznem, sz_teljesites, afa.ertek, arf_a.deviza_kozeparfolyam, arf_b.deviza_kozeparfolyam, foo_valuta, penznem ) foo ) bar WHERE szamla = 2380; ------------------------------------------------------------------------- ---------------------------- 7.2.1 PLAN --------------------------------- ------------------------------------------------------------------------- NOTICE: QUERY PLAN: Subquery Scan foo (cost=488.97..490.94 rows=8 width=104) (actual time=94.77..109.10 rows=12 loops=1) -> Aggregate (cost=488.97..490.94 rows=8 width=104) (actual time=89.29..92.05 rows=12 loops=1) -> Group (cost=488.97..490.74 rows=79 width=104) (actual time=88.13..88.59 rows=12 loops=1) -> Sort (cost=488.97..488.97 rows=79 width=104) (actual time=88.09..88.13 rows=12 loops=1) -> Nested Loop (cost=1.05..486.50 rows=79 width=104) (actual time=28.23..86.20 rows=12 loops=1) -> Nested Loop (cost=1.05..150.19 rows=79 width=84) (actual time=12.68..25.41 rows=12 loops=1) -> Nested Loop (cost=1.05..135.52 rows=13 width=80) (actual time=12.60..24.80 rows=2 loops=1) -> Hash Join (cost=1.05..79.59 rows=13 width=60) (actual time=0.55..0.80 rows=2 loops=1) -> Nested Loop (cost=0.00..78.31 rows=13 width=46) (actual time=0.23..0.42 rows=2 loops=1) -> Index Scan using szml_ttl_szml on szamla_tetele szt (cost=0.00..3.51 rows=13 width=34) (actual time=0.11..0.16 rows=2 loops=1) -> Index Scan using szamla_az_key on szamla (cost=0.00..5.70 rows=1 width=12) (actual time=0.07..0.09 rows=1 loops=2) -> Hash (cost=1.04..1.04 rows=4 width=14) (actual time=0.14..0.14 rows=0 loops=1) -> Seq Scan on afa (cost=0.00..1.04 rows=4 width=14) (actual time=0.08..0.11 rows=4 loops=1) -> Index Scan using arfolyam_ervenyes on arfolyam arf_a (cost=0.00..3.63 rows=3 width=20) (actual time=0.01..0.01 rows=0 loops=2) SubPlan -> Limit (cost=0.00..0.17 rows=1 width=4) (actual time=11.92..11.92 rows=0 loops=2) -> Index Scan Backward using arfolyam_ervenyes on arfolyam (cost=0.00..13.30 rows=79 width=4) (actual time=11.90..11.90 rows=0 loops=2) -> Limit (cost=0.00..0.17 rows=1 width=4) -> Index Scan Backward using arfolyam_ervenyes on arfolyam (cost=0.00..13.30 rows=79 width=4) -> Seq Scan on valuta (cost=0.00..1.06 rows=6 width=4) (actual time=0.02..0.11 rows=6 loops=2) -> Index Scan using arfolyam_ervenyes on arfolyam arf_b (cost=0.00..3.63 rows=3 width=20) (actual time=0.04..0.10 rows=3 loops=12) SubPlan -> Limit (cost=0.00..0.17 rows=1 width=4) (actual time=4.35..4.39 rows=1 loops=12) -> Index Scan Backward using arfolyam_ervenyes on arfolyam (cost=0.00..13.30 rows=79 width=4) (actual time=4.33..4.37 rows=2 loops=12) -> Limit (cost=0.00..0.17 rows=1 width=4) -> Index Scan Backward using arfolyam_ervenyes on arfolyam (cost=0.00..13.30 rows=79 width=4) Total runtime: 111.48 msec ------------------------------------------------------------------------- ---------------------------- 7.3.2 PLAN --------------------------------- ------------------------------------------------------------------------- QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Subquery Scan foo (cost=14542.01..15448.46 rows=3022 width=123) (actual time=2264.36..2282.17 rows=12 loops=1) -> Aggregate (cost=14542.01..15448.46 rows=3022 width=123) (actual time=2257.70..2261.08 rows=12 loops=1) -> Group (cost=14542.01..15372.92 rows=30215 width=123) (actual time=2256.31..2256.84 rows=12 loops=1) -> Sort (cost=14542.01..14617.55 rows=30215 width=123) (actual time=2256.27..2256.31 rows=12 loops=1) Sort Key: szt.szamla, konyvelesi_tetelcsoport(szt.szamla, szt.tetelszam), szt.afa, szamla.penznem, szamla.teljesites, afa.ertek, arf_a.deviza_kozeparfolyam, arf_b.deviza_kozeparfolyam, public.valuta.az -> Merge Join (cost=4755.50..11038.79 rows=30215 width=123) (actual time=80.88..2254.96 rows=12 loops=1) Merge Cond: ("outer".az = "inner".valuta) Join Filter: ("inner".ervenyes = (subplan)) Filter: (("outer".az = 4) OR ("inner".valuta IS NOT NULL)) -> Sort (cost=4676.19..4751.73 rows=30215 width=103) (actual time=56.41..56.44 rows=12 loops=1) Sort Key: public.valuta.az -> Nested Loop (cost=433.35..1375.92 rows=30215 width=103) (actual time=55.78..56.18 rows=12 loops=1) -> Merge Join (cost=433.35..469.47 rows=30 width=99) (actual time=55.68..55.72 rows=2 loops=1) Merge Cond: ("outer".penznem = "inner".valuta) Join Filter: ("inner".ervenyes = (subplan)) -> Sort (cost=354.05..354.12 rows=30 width=79) (actual time=32.63..32.64 rows=2 loops=1) Sort Key: szamla.penznem -> Hash Join (cost=121.67..353.30 rows=30 width=79) (actual time=32.37..32.49 rows=2 loops=1) Hash Cond: ("outer".afa = "inner".az) -> Merge Join (cost=120.62..352.09 rows=30 width=58) (actual time=32.07..32.16 rows=2 loops=1) Merge Cond: ("outer".az = "inner".szamla) -> Sort (cost=120.62..123.85 rows=1291 width=12) (actual time=25.62..27.62 rows=1285 loops=1) Sort Key: szamla.az -> Seq Scan on szamla (cost=0.00..53.91 rows=1291 width=12) (actual time=0.04..16.46 rows=1314 loops=1) -> Index Scan using szamla_tetele_pkey on szamla_tetele szt (cost=0.00..218.88 rows=30 width=46) (actual time=0.13..0.18 rows=2 loops=1) Index Cond: (szamla = 2380) Filter: (NOT archiv) -> Hash (cost=1.04..1.04 rows=4 width=21) (actual time=0.10..0.10 rows=0 loops=1) -> Seq Scan on afa (cost=0.00..1.04 rows=4 width=21) (actual time=0.04..0.07 rows=4 loops=1) -> Sort (cost=79.30..82.19 rows=1155 width=20) (actual time=22.93..22.93 rows=1 loops=1) Sort Key: arf_a.valuta -> Seq Scan on arfolyam arf_a (cost=0.00..20.55 rows=1155 width=20) (actual time=0.03..9.66 rows=1155 loops=1) SubPlan -> Limit (cost=0.00..0.16 rows=1 width=4) (never executed) -> Index Scan Backward using arfolyam_ervenyes on arfolyam (cost=0.00..12.17 rows=77 width=4) (never executed) Index Cond: (ervenyes <= $0) Filter: (valuta = $1) -> Seq Scan on valuta (cost=0.00..20.00 rows=1000 width=4) (actual time=0.02..0.06 rows=6 loops=2) -> Sort (cost=79.30..82.19 rows=1155 width=20) (actual time=21.98..27.54 rows=2309 loops=1) Sort Key: arf_b.valuta -> Seq Scan on arfolyam arf_b (cost=0.00..20.55 rows=1155 width=20) (actual time=0.03..9.89 rows=1155 loops=1) SubPlan -> Limit (cost=0.00..0.16 rows=1 width=4) (actual time=0.88..0.91 rows=1 loops=2310) -> Index Scan Backward using arfolyam_ervenyes on arfolyam (cost=0.00..12.17 rows=77 width=4) (actual time=0.87..0.90 rows=2 loops=2310) Index Cond: (ervenyes <= $0) Filter: (valuta = $2) Total runtime: 2287.30 msec (47 rows)