On 4/1/19 10:37 AM, Glenn Schultz wrote:
> All,
>
> The query below is designed to insert into a table. This works when I
> have a single loan which I insert. However, if remove the part of the
> where clause of a single loan the insert does not work. The table
> fnmloan is a large table with 500mm + rows and the query runs for about
> 4 hours. Any idea of how to get this to work? I am a little stumped
> since the query works with one loan.
If you pull the SELECT portion of the query out and run it without
restricting the loanseqnum and use EXPLAIN ANALYZE how many rows do you
get and what does the EXPLAIN show?
>
> Glenn
>
> SET max_parallel_workers_per_gather = 8;
> SET random_page_cost = 1;
>
> truncate fnmloan_balance;
> insert into fnmloan_balance (
> fctrdt, loanseqnum, secmnem, beginbal, scheduled,endbal,smm
> )
>
> select
> fnmloan.fctrdt
> ,fnmloan.loanseqnum
> ,fnmloan.secmnem
> --,fnmloan.orignoterate
> --,fnmloan.loanage
> --,fnmloan.origloanamt
> ,fnmloan.currrpb as beginbal
> ,round(nullif(schedprin(cast(fnmloan.orignoterate as numeric),
> cast(fnmloan.remterm - 1 as numeric),
> cast(fnmloan.currrpb as numeric)),4)) as scheduled
> ,coalesce(endbal.currrpb,0) as endbal
> ,abs(round(
> cast((fnmloan.currrpb - coalesce(endbal.currrpb,0) -
> round(nullif(schedprin(cast(fnmloan.orignoterate as numeric),
> cast(fnmloan.remterm - 1 as numeric),
> cast(fnmloan.currrpb as numeric)),4)) )/(fnmloan.currrpb -
> round(nullif(schedprin(cast(fnmloan.orignoterate as numeric),
> cast(fnmloan.remterm - 1 as numeric),
> cast(fnmloan.currrpb as numeric)),4)) ) as numeric)
> ,4)) as SMM
>
> from
> (
> select * from fnmloan
> where
> fctrdt < '03-01-2019'
> and
> loanseqnum = '5991017042'
> ) as fnmloan
>
>
> left outer join
> (select
> fctrdt - interval '1 month' as fctrdt
> ,loanseqnum
> ,orignoterate
> ,loanage
> ,origloanamt
> ,currrpb
> from fnmloan
> ) as endbal
>
> on fnmloan.loanseqnum = endbal.loanseqnum
> and fnmloan.fctrdt = endbal.fctrdt
>
--
Adrian Klaver
adrian.klaver@aklaver.com