Re: Performance MSSql vs PostgreSql

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

Actually, it's sorted on whatever CLUSTERED INDEX you have on the table.
If you don't have a clustered index, it will go in in insert order, just
like pg.
(if a table has a clustered index, the complete data is stored in this
index for ordered retreival, and otheri ndexes just point to this one.
There is no heap. If there is no clustered index, the data is stored in
a heap. This makes it very fast to access data in the order of the
clustered index)


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

Not only that, but for a query like that MSSQL will start returing the
data right away, whereas postgresql needs to load it all up in server
ram, then sort it, before it acn send even the first row. Even if you
have an index, I beleive it's all read up before you can access it - but
you can skip the sort step.


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

VMware workstation does have some noticable overhead - ESX is much
better on that. But vmware workstation acn give great speeds on INSERTs
and UPDATEs, because it will AFAIK *always* turn fsync into a no-op. The
data is flushde out of vmware, but ends up in the cache of the host
operatnig system. (This is not true of the vmware sever products, but it
is in workstation from my experience)

//Magnus