Re: scale up (postgresql vs mssql)

guess what:

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.

r b swpd free buff cache si so bi bo in cs us sy id wa st
8 0 0 6144048 237472 485640 0 0 0 40 4380 3237 79 5 16 0 0
8 0 0 6138216 238032 485736 0 0 0 40 4741 3506 93 7 0 0 0
8 0 0 6125256 238276 486484 0 0 0 2709 4801 3447 92 7 1 0 0
7 0 0 6119400 238376 485792 0 0 0 32 4854 4311 93 6 1 0 0
5 0 0 6105624 238476 486172 0 0 0 364 4783 3430 92 7 1 0 0
5 0 0 6092956 238536 485384 0 0 0 416 4954 3652 91 8 2 0 0

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.

merlin,
about the Hint Bits. i read this article:
http://wiki.postgresql.org/wiki/Hint_Bits
as far as i understand, this is not the case here, because i ran the test
many times, and there were no DML operations at all in between. so i
believe that the hint-bits are already cleared in most of the tuples.

Thanks again for any more help.