Re: Help with insert query

On Mon, Apr 1, 2019 at 10:37 AM Glenn Schultz <glenn(at)bondlab(dot)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
>
>