Re: scale up (postgresql vs mssql)

On Thu, May 3, 2012 at 12:07 PM, Eyal Wilde <eyal(at)impactsoft(dot)co(dot)il> wrote:
> 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?

let's see the query plan...when you turned it off, did it go faster?
put your suspicious plans here: http://explain.depesz.com/

> 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.

yeah. well, your query was an insert? that would naturally result in
blocks out.

merlin