Re: scale up (postgresql vs mssql)

after reducing bo (blocks out) to ~10% by using a ramdisk (improving overall performance by ~15-20%), i now managed to reduced it to ~3% by .... removing the "analyze temp-table" statements. 

it also :

reduced b (Process which are waiting for I/O) to zero

reduced wa (percentage of time spent by cpu for waiting to IO) to zero

and reduced id (cpu idle time percent) to be 4 times less.

unfortunately, this time there was no significant performance gain. ):

i afraid now there are certain statements that do not use an optimal query-plan. these statements looks like:

insert into temp-table1 (value) select table1.f1 from table1 join temp-table2 on table1.recid=temp-table2.recid where table1.f2 in (x,y,z);

temp-table2 never contains more then 10 records.

there is an index on table1: recid,f2

previous tests showed that the query-optimizer normally chose to do hash-join (i.e: ignoring the index), but once we did "analyze temp-table2;", the index was used. i read somewhere that the optimizer's assumption is that every temp-table contains 1k of records. i believe that is the reason for the bad plan. we tried to do "set enable_hashjoin=false;", but it did not seem to be working inside a function (although it did work independently). what can we do about that?

another thing i found is that a sequence on a temp-table is being stored on the current tablespace, and not on the temp_tablespace. would you consider this as a bug?

anyway, i found a way to not using any sequences on the temp-tables. but this did not change the bo (blocks-out) figures.