Re: Performance MSSql vs PostgreSql

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?

Second, what's your work_mem setting?

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.

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?

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.

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!

--
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco