Re: scale up (postgresql vs mssql)

On 4/15/2012 7:43 AM, Eyal Wilde wrote:
> hi,
>
> thanks a lot to all of you for your help.
>
> (i'm sorry i did not know how to reply to a certain message)
>
> i found that the best number of active connections is indeed 8-10. with
> 8-10 active connections postgresql did ~170 "account-id"s. this is still
> only half of what mssql did, but it now makes sence, considering that
> mssql works close to twice faster.
>
> i "played" with work_mem, shared_buffers, temp_buffers. i ran the tests
> with both of the following configurations, but no significant difference
> was found.
>
> thanks again for any more help.
>

We'd need to know if you are CPU bound or IO bound before we can help.
Watch "vmstat 2" while the server is busy (and maybe post a few rows).

> i had a stored procedure in ms-sql server. this stored procedure gets a parameter (account-id), dose about 20 queries, fills some temporary tables, and finally, returns a few result-sets. this stored procedure converted to stored function in postgresql (9.1). the result-sets are being returned using refcursors. this stored function is logically, almost identical to the ms-sql stored procedure.

I think that may be a problem. Treating PG like its mssql wont work
well I'd bet. things that work well in one database may not work well
in another.

Instead of temp tables, have you tried derived tables? Instead of:

insert into temptable select * from stuff;
select * from temptable;

try something like:

select * from (
select * from stuff
) as subq

Another thing you might try to remove temp tables is to use views.

I dont know if it'll be faster, I'm just guessing. Pulling out
individual parts and running "explain analyze" on them will help you
find the slow ones. Finding which is faster (temp tables, derived
tables, or views) might help you deiced what needs to be re-written.

Also, I'm not sure how well PG does "return multiple refcursors". there
may be a lot of round trips from client to server to fetch next. How
hard would it be to re-do your single procedure that returns a bunch of
refcursors into multiple procedures each returning one resultset?

Or maybe it would be something you can speed test to see if it would
even make a difference.

-Andy