Search Postgresql Archives

Re: Help with insert query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Mon, Apr 1, 2019 at 10:37 AM Glenn Schultz <glenn@xxxxxxxxxx> 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  


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux