Re: Performance MSSql vs PostgreSql

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 Structure
Inspecnum 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 datetime
dateinspec datetime
inspector char(25)
certrequired int(4)
accrejected int(4)
comment text
warehouse

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