High memory usage / performance issue ( temp tables ? )

Hi

I feel that there is some issue with temp tables and memory usage. Have seen
a couple of posts online regarding this, but most issues have since been
resolved or have been proved as problem unrelated to Postgres.
I'd appreciate if someone can assist me in this.

My situation: 

Version "PostgreSQL 9.2.6 on x86_64-unknown-linux-gnu, compiled by gcc
(Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit" ) running on linux.

I have a plpgsql function which takes an XML string payload as input and
does some processing using that payload data. This function makes use of
multiple other postgres functions (sql, plpgsql, plpython3u).
Main purpose of the process is to populate multiple tables, basically doing
inserts on financial transactional tables which in turn triggers to other
tables triggering to other tables again, etc. , etc. 
The process follows a method where the XML payload data is inserted into
multiple TEMP tables ( can be up to 10 tables for each function call ) .
These are created using ON COMMIT DROP .

My problem:

We have a process using the above function to process batches of XML payload
files. 
After running a batch of 50000 xml files , I definitely see a deterioration
in performance. At first glance, I wrote this down to some sort of memory
problem.

$top
 Cpu(s): 25.1%us,  0.1%sy,  0.0%ni, 74.8%id,  0.0%wa,  0.0%hi,  0.0%si, 
0.0%st Mem:  14371048k total, 14195464k used,   175584k free,   424788k buffers Swap:  6288380k total,    11972k used,
6276408kfree, 12114744k cached
 
   PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND 20596 postgres  20   0 1688m 1.3g 1.2g S    0
9.1  0:24.38 postgres 29164 postgres  20   0 1721m 966m 933m R  100  6.9   4:30.18 postgres 28165 postgres  20   0
1782m630m 568m S    0  4.5   0:23.19 postgres 28155 postgres  20   0 1780m 460m 370m S    0  3.3   0:43.76 postgres
 

(I have to admit, I'm not a linux expert -- I have some guys at the office
who I can ask for help tomorrow).
Now accoring to this post (
http://www.postgresql.org/message-id/165E6919-697C-4C50-9EEE-38728AC6D982@tcdi.com
) , this can be a display issue in top , rather than a real memory problem.


I get the idea that the method of creating temp tables is probably causing
the problem here . At the very least , it could be cause of performance
issues.
Has anyone else been in this same situation with regards to temp tables in
Postgres 

I'd appreciate if there are any comments / advice / reprimands.


Regards 

gmb



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/High-memory-usage-performance-issue-temp-tables-tp5815108.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.