Thread: Performance MSSql vs PostgreSql

See http://channel9.msdn.com/ShowPost.aspx?PostID=179064

This is one of the few performance comparisons between PostgreSQL and
MS SQL I have seen, and at first sight it seems too unfavorable to
PgSQL...  Maybe there is a bottleneck in the ODBC driver, Foxpro,
VMWare or Windows, really I don't know, I'm not an expert, but I would
not lightly believe in such results, especially for such queries that
are so simple. However, a casual reader or someone wanting to choose
between PostgreSQL and MS SQL could base his/her decision on that
results.

You may want to give the author (and readers) some comments to explain
the results or to improve the benchmarks.

Regards.

Hello all I'm the guy who posted the quick and dirty performance numbers on Channel 9.  i was informed of this thread by an associate. 

I did this little test to start getting my feet wet with PostgreSQL and see what kind of performance difference with my data.

The dataset has heavy usage for the small company i work for  The name of table is QClog (Quality Control Log)  The table only experiences Inserts and  Searches on WorkOrder, dateentered, SalesOrders, 
and for analysis on failures and reasons why a failure occurred.  Users are never allowed to delete entries they must add another record offsetting the bad entry and reason why?  

Table StructureInspecnum char(10)Workorder char(10)doctype  int(4)stockcode char(55)qty  numeric(9,4)salesorder char(6)saleslinenum int(4)qtyinspec int(4)shipdate datetimedateinspec datetimeinspector char(25)certrequired int(4)accrejected int(4)comment textwarehouse 

As you can see its not a big table..

The test i did was very basic and made me do more research on performance of PostgreSql.  I latter read  "PostgreSql Second Edition" by (Korry Douglas/Susan Douglas). This book better explains the performance tweaking but heres how i understand it

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

On the other hand MSSql default data caching is copy the entire table into memory if there is enough memory or until something else pushes it out. MsSql never needed to use the Disk sense the table was copied into memory

This was noticeable when looking at each loops time MSSQL was allot slower during the first loop but MS picked up allot of time during the following loops because it cached the entire table.  


It appears the only way for PostgreSql to use cache more effectively is to reorder the entire physical table into chronological order with respect to InspecNum???? 


If any of you have comments/questions/suggestions  i'm all ears..





On Tue, 2006-05-09 at 17:16 -0400, Ricardo Stuven wrote:
> Please, see http://channel9.msdn.com/ShowPost.aspx?PostID=179064
> 
> This is one of the few performance comparisons between PostgreSQL and
> MS SQL I have seen, and at first sight it seems too unfavorable to
> PgSQL...  Maybe there is a bottleneck in the ODBC driver, Foxpro,
> VMWare or Windows, really I don't know, I'm not an expert, but I would
> not lightly believe in such results, especially for such queries that
> are so simple. However, a casual reader or someone wanting to choose
> between PostgreSQL and MS SQL could base his/her decision on that
> results.

All the queries tested are "Select *" issued with a single Exec, no
Fetch-es at all.

It's all down to how many rows are cached by the basic Exec call, in
anticipation of later Fetch-es.

You can always find a trick that one or other does better. 

--  Simon Riggs EnterpriseDB          http://www.enterprisedb.com

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

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

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! 

ZZZZ,

> Default for both MSSQL and PostgreSql  no performance turning was done
> to the configurations

Try setting work_mem to 8096, or 16192.

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

Aha, so this could be an ODBC driver speed difference as well.  I
wouldn't be surprised.

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

Certainly.

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

Yes, so that's part of things.  I think the bigger part is ODBC and
FoxPro overhead.  I'd be interested to see the time just on the database
server.

--Josh

ZZZ,

> The reason i'm doing this is because the ERP package we are looking at
> buying (waiting for Approval) uses PostgreSql as the backend
> and need to learn allot more about PostgreSQL

OpenMFG?

--
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco