Re: Performance MSSql vs PostgreSql

Josh Berkus wrote:
> ZZ,
>
>
>> One of the big problems with the performance numbers I came up with is
>> the way the data was inserted. When creating the table and adding the
>> records I only did "Select * from qclog" from the MSSql where the data
>> is not organized in chronologically with respect to InspecNum. So
>> PostgreSql has to jump around allot to put the data in order. This made
>> the page caching worthless. Second given the size of the dataset being
>> requested the buffer is always over written with each new query.
>> PostgreSql had to use the disk
>>
>
> First off, this is PostgreSQL on windows, right?
>
Yes its Windows XP Pro sp2
> Second, what's your work_mem setting?
>
Default for both MSSQL and PostgreSql no performance turning was done
to the configurations
> Third: data in MSSQL is spooled, and therefore by default in the order it
> was inserted into the table. So if the timestamp is related to how the
> data was inserted in the table, it may actually be ordered in MSSQL and
> not in PostgreSQL.
MSSql does not spool data out in the order it appears in the table for
several reasons.
>
> Also, by default the time returned by MSSQL is the time it *began*
> returning rows, whereas PostgreSQL gives you the time it *finished*. How
> are you measuring that time?
>
I'm not using the server time but the clients time from the start of the
query and time to return the resulting dataset to Foxpro. The code is
on Channel 9, Its just a simple loop to see have fast it runs.
> That being said, it's certainly possible that PostgreSQL sorting is slower
> on Windows than SQL Server is. We are optimized for Linux and FreeBSD,
> and your test is pretty much a raw sort speed test.
>
I was not looking at the server response time but what the client/user
will experience. To me how the fast the server does something kinda
meaningless if User can't see the added speed because one of the other
layers is slowing things down.

Now i did compare it to the production server. The time to get the
records into Foxpro was 1.2 seconds.
> Finally, given your overall times I see that stuff is *very* slow on
> VMware. I'd expect that query to return in milleseconds on both
> databases!
>
I stated the test setup on channel 9 this is all running on my HP zd7000
laptop 3.2 gigahertz p4, 54000 rpm hard drive.
My development environment runs in VMware 5.0 it slows things down a
bit but not allot.