On Mon, Apr 1, 2019 at 10:37 AM Glenn Schultz <gl...@bondlab.io> 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. > > Inserting one row is fast, inserting 500 million rows is going to take quite a bit longer. I suggest your break your query up into batches, and insert, say, 1 million rows at a time. Also it might be a good idea to drop your indexes on the target table and re-create them after you do the bulk insert, and also do an 'ANALYZE' on the target table after you have inserted all the records. -Michel > 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 > >
